{"id":204,"date":"2007-12-01T13:48:23","date_gmt":"2007-12-01T12:48:23","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=204"},"modified":"2007-12-01T13:48:23","modified_gmt":"2007-12-01T12:48:23","slug":"oracle-11g-virtual-columns-dates","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=204","title":{"rendered":"Oracle 11G &#8211; Virtual Columns (Dates)"},"content":{"rendered":"<p>A couple of days ago there was a <a title=\"datums\" href=\"http:\/\/utplsql.oracledeveloper.nl\/newforum\/showmessage.cfm?mesid=1107&amp;last=0\" target=\"_blank\">question<\/a> (in Dutch) on the <a title=\"utPLSQL\" href=\"http:\/\/utplsql.oracledeveloper.nl\" target=\"_blank\">utPLSQL<\/a> forum about filling up a table with a lot of dates and some info about those dates, like the weeknumber, the dayname etc. I created code using&nbsp;persistent&nbsp;columns at first, so the code would run on a pre-11G installation, but I was thinking about how this could be done using the new Virtual Column option in 11G.<\/p>\n<p><!--more--><\/p>\n<p>First I wrote a package for the fields that cannot easily be derived from the data field itself, for instance if the current year is a leap year or not. This package holds the following functions:<\/p>\n<ul>\n<li>isleapyear  <\/li>\n<li>isweekend  <\/li>\n<li>dayname  <\/li>\n<li>monthname<\/li>\n<\/ul>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:75f13494-aa76-4d7f-ba16-3d06092e4195\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">CREATE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">OR<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">REPLACE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">PACKAGE<\/span><span style=\"color: #000000; \"> dates_vp\n<\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #008000; \">--<\/span><span style=\"color: #008000; \"> Author  : PBAREL<\/span><span style=\"color: #008000; \">\n<\/span><span style=\"color: #000000; \">  <\/span><span style=\"color: #008000; \">--<\/span><span style=\"color: #008000; \"> Purpose : Functions for virtual columns<\/span><span style=\"color: #008000; \">\n<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #008000; \">--<\/span><span style=\"color: #008000; \"> Public function and procedure declarations<\/span><span style=\"color: #008000; \">\n<\/span><span style=\"color: #000000; \">  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> isleapyear(\n    year_in                        <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> isweekend(\n    daynumber_in                   <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> dayname(\n    daynumber_in                   <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> monthname(\n    monthnumber_in                 <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">;\n<\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> dates_vp;\n<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<p>I know that the day name and the month name can probably be derived using an overload of the to_char function, but this way I could rather easily make it work.<\/p>\n<p>The implementation is as follows:<\/p>\n<\/p>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:b88c00a1-f44f-41a0-9703-bfb791c36250\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">CREATE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">OR<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">REPLACE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">PACKAGE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">BODY<\/span><span style=\"color: #000000; \"> dates_vp\n<\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #008000; \">--<\/span><span style=\"color: #008000; \"> Private constant declarations<\/span><span style=\"color: #008000; \">\n<\/span><span style=\"color: #000000; \">  c_unknown                       <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Unknown<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_yes                           <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">3<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Yes<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_no                            <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">3<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">No<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_sunday                        <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Sunday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_monday                        <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Monday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_tuesday                       <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Tuesday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_wednesday                     <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Wednesday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_thursday                      <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Thursday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_friday                        <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Friday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_saturday                      <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">Saturday<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_january                       <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">January<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_february                      <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">February<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_march                         <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">March<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_april                         <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">April<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_may                           <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">May<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_june                          <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">June<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_july                          <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">July<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_august                        <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">August<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_september                     <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">September<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_october                       <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">October<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_november                      <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">November<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n  c_december                      <\/span><span style=\"color: #0000FF; \">CONSTANT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> ) :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">December<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #008000; \">--<\/span><span style=\"color: #008000; \"> Function and procedure implementations<\/span><span style=\"color: #008000; \">\n<\/span><span style=\"color: #000000; \">  <\/span><span style=\"color: #0000FF; \">PROCEDURE<\/span><span style=\"color: #000000; \"> initialization\n  <\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">NULL<\/span><span style=\"color: #000000; \">;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> initialization;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> isleapyear(\n    year_in                        <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n    l_dummy                                  <\/span><span style=\"color: #0000FF; \">DATE<\/span><span style=\"color: #000000; \">;\n  <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n    l_dummy   :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_DATE( <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">2902<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \"> || TO_CHAR( year_in ), <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">DDMMYYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \"> );\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> c_yes;\n  <\/span><span style=\"color: #0000FF; \">EXCEPTION<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">OTHERS<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \">\n      <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> c_no;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> isweekend(\n    daynumber_in                   <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n    l_returnvalue                            <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">3<\/span><span style=\"color: #000000; \"> );\n  <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \">    ( daynumber_in <\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">1<\/span><span style=\"color: #000000; \"> )\n       <\/span><span style=\"color: #0000FF; \">OR<\/span><span style=\"color: #000000; \"> ( daynumber_in <\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">7<\/span><span style=\"color: #000000; \"> ) <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \">\n      l_returnvalue   :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_yes;\n    <\/span><span style=\"color: #0000FF; \">ELSE<\/span><span style=\"color: #000000; \">\n      l_returnvalue   :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_no;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \">;\n\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> l_returnvalue;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> dayname(\n    daynumber_in                   <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n    l_returnvalue                            <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">20<\/span><span style=\"color: #000000; \"> );\n  <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">CASE<\/span><span style=\"color: #000000; \"> daynumber_in\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">1<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_sunday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_monday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">3<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_tuesday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">4<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_wednesday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">5<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_thursday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">6<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_friday;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">7<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_saturday;\n      <\/span><span style=\"color: #0000FF; \">ELSE<\/span><span style=\"color: #000000; \">\n        l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_unknown;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">CASE<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> l_returnvalue;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n\n  <\/span><span style=\"color: #0000FF; \">FUNCTION<\/span><span style=\"color: #000000; \"> monthname(\n    monthnumber_in                 <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \">      <\/span><span style=\"color: #0000FF; \">NUMBER<\/span><span style=\"color: #000000; \"> )\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">DETERMINISTIC<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">IS<\/span><span style=\"color: #000000; \">\n    l_returnvalue                            <\/span><span style=\"color: #0000FF; \">VARCHAR2<\/span><span style=\"color: #000000; \">( <\/span><span style=\"color: #800080; \">20<\/span><span style=\"color: #000000; \"> );\n  <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">CASE<\/span><span style=\"color: #000000; \"> monthnumber_in\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">1<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_january;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">2<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_february;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">3<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_march;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">4<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_april;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">5<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_may;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">6<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_june;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">7<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_july;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">8<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_august;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">9<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_september;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">10<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_october;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">11<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_november;\n      <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">12<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_december;\n      <\/span><span style=\"color: #0000FF; \">ELSE<\/span><span style=\"color: #000000; \">\n        l_returnvalue :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> c_unknown;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">CASE<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">RETURN<\/span><span style=\"color: #000000; \"> l_returnvalue;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n<\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n  initialization;\n<\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> dates_vp;\n<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<\/p>\n<p>Note the function to decide whether we are in a leap year or not. It tries to convert the 29th of February to&nbsp;a real date. If it fails, we are not in a leap year, if it succeeds, then the current year is a leap year. Using the exception being raised by Oracle allows me to use Oracle to decide instead of writing the entire algorithm myself.<\/p>\n<p>Then it was time to create the table, a sequence and create the code to fill up the 2 column table.<\/p>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:e0277cf1-fcff-4feb-b00e-c6949060f58c\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">CREATE<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc(\n  thekey <\/span><span style=\"color: #000000; font-weight: bold; \">NUMBER<\/span><span style=\"color: #000000; \">\n, thedate DATE\n);\n<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<p><\/p>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:7be6e61b-39d0-4692-85ae-c20f3f7d158e\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">CREATE<\/span><span style=\"color: #000000; \"> SEQUENCE dates_vc_seq START <\/span><span style=\"color: #0000FF; \">WITH<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; font-weight: bold; \">1<\/span><span style=\"color: #000000; \"> NOCACHE;\n<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:dee6f49d-67a9-4c9e-967a-bf1d688155eb\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">DECLARE<\/span><span style=\"color: #000000; \">\n  l_startdate <\/span><span style=\"color: #0000FF; \">DATE<\/span><span style=\"color: #000000; \">;\n  l_enddate <\/span><span style=\"color: #0000FF; \">DATE<\/span><span style=\"color: #000000; \">;\n  l_currentdate <\/span><span style=\"color: #0000FF; \">DATE<\/span><span style=\"color: #000000; \">;\n  l_startyear <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">;\n  l_startmonth <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">;\n  l_startday <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">;\n  l_endyear <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">;\n  l_endmonth <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">;\n  l_endday <\/span><span style=\"color: #0000FF; \">PLS_INTEGER<\/span><span style=\"color: #000000; \">; \n<\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n  l_startdate :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_DATE(<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">01011960<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">DDMMYYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n  l_enddate :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_DATE(<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">31122020,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">DDMMYYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">);<\/span><span style=\"color: #800000; \">\n<\/span><span style=\"color: #000000; \">  l_startyear :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_startdate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">YYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n  \n  l_endyear :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_enddate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">YYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n  \n  <\/span><span style=\"color: #0000FF; \">FOR<\/span><span style=\"color: #000000; \"> yearindex <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \"> l_startyear .. l_endyear <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">\n    <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \"> yearindex <\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> l_startyear <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \">\n      l_startmonth :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_startdate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">MM<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n      l_startday :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_startdate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">DD<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n    <\/span><span style=\"color: #0000FF; \">ELSE<\/span><span style=\"color: #000000; \">\n      l_startmonth :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">1<\/span><span style=\"color: #000000; \">;\n      l_startday :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">1<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \"> yearindex <\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> l_endyear <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \">\n      l_endmonth :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_enddate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">MM<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n      l_endday :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_CHAR(l_enddate,<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">DD<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n    <\/span><span style=\"color: #0000FF; \">ELSE<\/span><span style=\"color: #000000; \">\n      l_endmonth :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">12<\/span><span style=\"color: #000000; \">;\n      l_endday   :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800080; \">31<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">IF<\/span><span style=\"color: #000000; \">;\n    \n    <\/span><span style=\"color: #0000FF; \">FOR<\/span><span style=\"color: #000000; \"> monthindex <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \"> l_startmonth .. l_endmonth <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">\n      <\/span><span style=\"color: #0000FF; \">FOR<\/span><span style=\"color: #000000; \"> dayindex <\/span><span style=\"color: #0000FF; \">IN<\/span><span style=\"color: #000000; \"> l_startday .. l_endday <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">\n        <\/span><span style=\"color: #0000FF; \">BEGIN<\/span><span style=\"color: #000000; \">\n          l_currentdate :<\/span><span style=\"color: #000000; \">=<\/span><span style=\"color: #000000; \"> TO_DATE(to_char(dayindex)||<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">\/<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">\n                                 ||to_char(monthindex)||<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">\/<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">\n                                 ||to_char(yearindex)\n                                  , <\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #800000; \">DD\/MM\/YYYY<\/span><span style=\"color: #800000; \">'<\/span><span style=\"color: #000000; \">);\n          <\/span><span style=\"color: #0000FF; \">INSERT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">INTO<\/span><span style=\"color: #000000; \"> dates_vc (thekey, thedate) <\/span><span style=\"color: #0000FF; \">VALUES<\/span><span style=\"color: #000000; \"> \n                               (dates_vc_seq.nextval, l_currentdate);\n        <\/span><span style=\"color: #0000FF; \">EXCEPTION<\/span><span style=\"color: #000000; \">\n          <\/span><span style=\"color: #0000FF; \">WHEN<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">OTHERS<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">THEN<\/span><span style=\"color: #000000; \"> \n            <\/span><span style=\"color: #0000FF; \">NULL<\/span><span style=\"color: #000000; \">;\n        <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n      <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">;\n    <\/span><span style=\"color: #0000FF; \">COMMIT<\/span><span style=\"color: #000000; \">;\n  <\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">LOOP<\/span><span style=\"color: #000000; \">;\n<\/span><span style=\"color: #0000FF; \">END<\/span><span style=\"color: #000000; \">;\n<\/span><span style=\"color: #000000; \">\/<\/span><span style=\"color: #000000; \">\n<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<p>I know, I shouldn&#8217;t use the WHEN OTHERS THEN NULL; exception, but in this case, if conversion of the date fails, then that&#8217;s ok, just continue with the next possibility.<br \/>Now that the table with the persistent columns is in place, it&#8217;s time to add the Virtual Columns to the table so I can select everything I want.<\/p>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:169681bc-61e8-4b97-b6be-4f3e4c2bfac5\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (thedaynr <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">DDD<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (theday <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> ( dates_vp.dayname(to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">D<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">)))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (theweek <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">IW<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (themonthnr <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">MM<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (themonth <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (dates_vp.monthname(to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">MM<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">)))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (thequarter <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (<\/span><span style=\"color: #FF00FF; \">FLOOR<\/span><span style=\"color: #000000; \">(( to_number(to_char(thedate, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">MM<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">)) <\/span><span style=\"color: #808080; \">+<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; font-weight: bold; \">2<\/span><span style=\"color: #000000; \"> ) <\/span><span style=\"color: #808080; \">\/<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #800000; font-weight: bold; \">3<\/span><span style=\"color: #000000; \"> )));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (leapyear <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (dates_vp.isleapyear(to_number(TO_char(thedate,<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">YYYY<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">)))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (weekend <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (dates_vp.isweekend(TO_NUMBER(to_char(thedate,<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">DDD<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">)))));\n<\/span><span style=\"color: #0000FF; \">ALTER<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #0000FF; \">TABLE<\/span><span style=\"color: #000000; \"> dates_vc \n  <\/span><span style=\"color: #0000FF; \">ADD<\/span><span style=\"color: #000000; \"> (theyearmonth <\/span><span style=\"color: #0000FF; \">AS<\/span><span style=\"color: #000000; \"> (to_number(to_char(thedate,<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">YYYYMM<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">))));<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<\/p>\n<p>Now that everything is in place, let&#8217;s select a row from the table. Let&#8217;s take a completely random date (December 29th 1972, my birthdate):<\/p>\n<div class=\"wlWriterSmartContent\" id=\"57F11A72-B0E5-49c7-9094-E3A15BD5B5E7:7130fe03-97b3-4161-a40b-ff54d3a1bacf\" contenteditable=\"false\" style=\"padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px\">\n<pre style=\"background-color:White;;overflow: auto;\"><div><!--\n\nCode highlighting produced by Actipro CodeHighlighter (freeware)\nhttp:\/\/www.CodeHighlighter.com\/\n\n--><span style=\"color: #0000FF; \">SELECT<\/span><span style=\"color: #000000; \"> <\/span><span style=\"color: #808080; \">*<\/span><span style=\"color: #000000; \">\n  <\/span><span style=\"color: #0000FF; \">FROM<\/span><span style=\"color: #000000; \"> dates_vc\n <\/span><span style=\"color: #0000FF; \">WHERE<\/span><span style=\"color: #000000; \"> dates_vc.thedate <\/span><span style=\"color: #808080; \">=<\/span><span style=\"color: #000000; \"> TO_DATE( <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">12291972<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \">, <\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #FF0000; \">MMDDYYYY<\/span><span style=\"color: #FF0000; \">'<\/span><span style=\"color: #000000; \"> )<\/span><\/div><\/pre>\n<p><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http:\/\/dunnhq.com --><\/div>\n<p>The results are as follows:<\/p>\n<table cellspacing=\"0\" cellpadding=\"0\" width=\"270\" border=\"1\" unselectable=\"on\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"156\">THEKEY<\/td>\n<td valign=\"top\" width=\"91\">4747<\/td>\n<td valign=\"top\" width=\"21\">Persistent<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"167\">THEDATE<\/td>\n<td valign=\"top\" width=\"94\">12\/29\/1972<\/td>\n<td valign=\"top\" width=\"23\">Persistent<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"168\">THEDAYNR<\/td>\n<td valign=\"top\" width=\"96\">364<\/td>\n<td valign=\"top\" width=\"24\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"167\">THEDAY<\/td>\n<td valign=\"top\" width=\"97\">Friday<\/td>\n<td valign=\"top\" width=\"25\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"166\">THEWEEK<\/td>\n<td valign=\"top\" width=\"97\">52<\/td>\n<td valign=\"top\" width=\"26\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"165\">THEMONTHNR<\/td>\n<td valign=\"top\" width=\"97\">12<\/td>\n<td valign=\"top\" width=\"27\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"164\">THEMONTH<\/td>\n<td valign=\"top\" width=\"97\">December<\/td>\n<td valign=\"top\" width=\"28\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"164\">THEQUARTER<\/td>\n<td valign=\"top\" width=\"97\">4<\/td>\n<td valign=\"top\" width=\"28\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"164\">LEAPYEAR<\/td>\n<td valign=\"top\" width=\"97\">Yes<\/td>\n<td valign=\"top\" width=\"28\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"164\">WEEKEND<\/td>\n<td valign=\"top\" width=\"97\">No<\/td>\n<td valign=\"top\" width=\"28\">Virtual<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"164\">THEYEARMONTH<\/td>\n<td valign=\"top\" width=\"97\">197212<\/td>\n<td valign=\"top\" width=\"28\">Virtual<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>There you have it. Just 2 persistent columns, a couple of functions and a lot of virtual columns. It takes up the minimum of space in the tables, the rest of the columns is derived from the persistent date column. And I think this is fast enough, even though the virtual columns are calculated at runtime. But, since the functions are deterministic, I think Oracle has the results cached some way or the other. I read once that data that can easily be derived from other data should not be stored in the table, except maybe for speed reasons, but using virtual columns you can virtually store the columns in the database, so it seems to other people as if you store all the values, where you actually (or have Oracle) calculate the derived values.<\/p>\n<p>I know it&#8217;s a rather useless issue, but it is fun to play with this new functionality.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A couple of days ago there was a question (in Dutch) on the utPLSQL forum about filling up a table with a lot of dates and some info about those dates, like the weeknumber, the dayname etc. I created code using&nbsp;persistent&nbsp;columns at first, so the code would run on a pre-11G installation, but I was [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-204","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=204"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/204\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}