Is a year a leap year ?

This post seems timely given that yesterday was Feb 29.

In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic.

This is perhaps one of the very very few exceptions Smile


SQL> set timing off
SQL> create or replace
  2  function is_leap_year1(y number) return boolean is
  3    x date;
  4  begin
  5    x := to_date('2902'||y,'ddmmyyyy');
  6    return true;
  7  exception
  8    when others then return false;
  9  end;
 10  /

Function created.

SQL>
SQL> create or replace
  2  function is_leap_year2(y number) return boolean is
  3  begin
  4    return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
  5  end;
  6  /

Function created.

SQL>
SQL> set timing on
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year1(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.88
SQL>
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year2(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

Although I must admit, I’m struggling to think of a use case where you would need to check a year for being a leap year hundreds of thousands of times Smile

Loading LOB from a file

I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:

First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size



SQL> !echo "This is line 1" > /tmp/lobfile
SQL> !echo "This is line 2" >> /tmp/lobfile
SQL> !echo "This is line 3" >> /tmp/lobfile
SQL> !echo "This is line 4" >> /tmp/lobfile
SQL> !echo "This is line 5" >> /tmp/lobfile
SQL> !wc /tmp/lobfile
       5      20      75 /tmp/lobfile

Then create a standard routine to load it into the database


SQL> create or replace directory TMP as '/tmp';

Directory created.

SQL> drop table lob_tab;

Table dropped.

SQL> create table lob_tab ( the_lob clob );

Table created.

SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5  begin
  6    l_bfile := bfilename( 'TMP', 'lobfile' );
  7
  8    insert into lob_tab (the_lob)
  9    values ( empty_clob() )
 10    returning the_lob into v_lob;
 11
 12    amt := dbms_lob.getlength( l_bfile );
 13    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 14    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
 15
 16    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 17    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 18    dbms_lob.fileclose( l_bfile );
 19  end;
 20  /
File length is: 75
Loaded length is: 37   <==== !!!!

PL/SQL procedure successfully completed.

So what has happened to the second half of the LOB ?!

It turns out to be related to the character set. If you’re using UTF8 (as this database is), then loadfromfile must assume the possibility of multibyte characters (since the bfile could be binary). Thus you get two bytes per character and hence “garbage” in the lob.

The workaround is to use sqlldr, or load the lob without using loadfromfile, eg you could load it piecewise:


SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    buf       raw(32767);
  5    vc        varchar2(32767);
  6    amt       number;
  7    v_offset  number := 1;
  8  begin
  9    l_bfile := bfilename( 'TMP', 'lobfile' );
 10
 11    insert into lob_tab (the_lob)
 12    values ( empty_clob() )
 13    returning the_lob into v_lob;
 14
 15    amt := dbms_lob.getlength( l_bfile );
 16    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 17    dbms_lob.read(l_bfile,amt,v_offset,buf);
 18    vc := utl_raw.cast_to_varchar2(buf);
 19    dbms_lob.writeappend(v_lob,amt,vc);
 20
 21    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 22    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 23    dbms_lob.fileclose( l_bfile );
 24  end;
 25  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

Or you could use LOADCLOBFROMFILE with the extra parameters


SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5
  6    d_offset number := 1;
  7    s_offset number := 1;
  8    csid     number := 0;
  9    lang     number := 0;
 10    warning  number;
 11  begin
 12    l_bfile := bfilename( 'TMP', 'lobfile' );
 13
 14    insert into lob_tab (the_lob)
 15    values ( empty_clob() )
 16    returning the_lob into v_lob;
 17
 18    amt := dbms_lob.getlength( l_bfile );
 19    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 20    dbms_lob.LOADCLOBFROMFILE( v_lob, l_bfile ,amt, d_offset,s_offset,csid, lang,warning );
 21
 22    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 23    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 24    dbms_lob.fileclose( l_bfile );
 25  end;
 26  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

It might look like a bug, but it’s not really – if you look carefully, you’ll see that this behaviour is documented in the DBMS_LOB manual.

Amped on Amper

This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle


SQL> create table CARTOONS ( name varchar2(30));

Table created.

SQL> insert into CARTOONS values ('Tom & Jerry');
Enter value for jerry:


And the question comes in: “How can I insert an ampersand into my table?”

And the correct response is – the database treats ampersand exactly the same as any other character.  Which of course, just confuses the heck out of people, because they are staring at their screen, being asked for a value for “Jerry” and it’s obvious that ampersands are not the same as any character.

So what’s going on here ?  There is an excellent section in the Concepts Guide which talks about the way we actually communicate to the database, and that is via a client program.

The client program passes our requests to the database, and awaits it’s response.  The client program is not the database, it is a means of communicating to the database.  And whatever client program you are using, it may have features designed to assist you (which in fact may just be confusing you).

Whether it be SQL Plus, or SQL Developer, or TOAD, or (insert any tool here), there’s a good chance it has a facility to allow the end user to provide input during the execution of a SQL statement.  (More accurately, its asking you for input before it sends the statement for execution).  And for many tools, the ampersand is the special character that indicates “pause for input”.

So in the SQL Plus example above, it’s just a case of telling the client tool that the ampersand is not to be used for this purpose:


SQL> set define off
SQL> insert into CARTOONS values ('Tom & Jerry');

1 row created.

SQL> set define &

Just remember. This has nothing to do with the database.

How can I see my invisible columns

A cool new feature in 12c is the ability to make a column invisible.  The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.


SQL> create table T ( c1 int, c2 int );

Table created.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2                                     NUMBER(38)

SQL> alter table T modify c2 invisible;

Table altered.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)


So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.

It’s easy, we have a new setting – COLINVISIBLE



SQL> set colinvisible on
SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         NUMBER(38)


Of course, if you want to play a practical joke on your work colleagues, you could do this:


SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- -------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         DATE
 C2 (INVISIBLE)                         NUMBER(38)

Smile

Equi-sized partitions on random data

Had an interesting AskTom question today about dynamically keeping partition sizes in check. The poster had large table, already partitioned, but since there was no logical column to do a range partition on, it was partitioned on a surrogate primary key value. Due to the volatility of the data, (with a BLOB column on the table) evenly sized primary key ranges led to dramatically different partition sizes, causing manageability concerns. Partitions could be split and/or merged, but obviously if those partitions contain data, that is both a very expensive operation, and could impact availability of the data depending on the type of maintenance operation performed.

So the challenge became – can we proactively have (roughly) equi-sized partitions, when we don’t know how and when the data is going to come in. Obviously, its virtually impossible to know exactly every time an extent is allocated to a segment, and we don’t want to run things to tightly that we end up not being able to do inserts. It is like the old stock market phrase: “Past behaviour is not a reliable indicator for future behaviour“, in that all it takes is a few very very large blobs to easily get you to a large partition size in just a few rows, whereas tomorrow, it might take millions of rows (each with tiny blobs) to get you to 10g.

So we are probably going to have some sort of compromise.

That compromise might be relying on the “fast split” optimization where you pick a point where no data will exist on the “right” hand side of the split point.

So my logic for such a schema is this:

  • walk along the partitions in order
  • if “this” partition in empty, check the previous one for its size
  • if its greater than our allowable threshold then
    • split it into 2 partitions at a logical point so that one has all the data, one empty one (ptmp)
    • split the “maxvalue” highest partition (pmax) into two (pn and pmax), to establish a new sensible boundary point for the new partition
    • merge ptmp and pn

So with some numbers, our partition strategy might go along the following lines.  We start with the following setup, we where have our partition definitions, and the current high water mark for the primary key column that is being used for partitioning


       values less than          current hwm
p1     1000                      1000
p2     2000                      1500
pmax   maxvalue                  -

and we think p2 is now “too large”. We need to split p2 so that it cannot grow any further

Step1 – split p2


       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
ptmp   2000                      -
pmax   maxvalue                  -

So we’ve now capped p2 at 1600.  We would pick something like  1600 because when we commenced the split, the primary key value was up to 1500, so we want some leeway whilst inserts still carry on.

Step2 – split pmax


       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
ptmp   2000                      -
p3     3000                      -
pmax   maxvalue                  -

You may wonder why we split PMAX since it appears unrelated.  We’ve done this, because ‘ptmp’ is no good to us because its capped at 2000, and of course, it could have started from anywhere between 1500 and 1999, depending on where the primary values were up to when we did this check.  We want it capped at a sensible logical value, so we’ll be using 3000, which is currently assigned to P3.

Step3 – merge p3 and ptmp


       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
p3     3000                      -
pmax   maxvalue                  -

Both P3 and PTMP are empty, so the merge of these two will be just a dictionary operation.

And we’re done. All of these operations, because they would be working on empty partitions should be virtually instantaneous.   A complete implementation would include some further checks to ensure this is the case during the processing stage, but for the sake of a simple demo we’ll be optimistic.

So here’s the demo, where 30meg is the size limit for a segment.  We’ll create a table partitioned on our surrogate key, and pre-load it with some data.


SQL> create table T ( x int, y char(200))
  2  partition by range (x)
  3  (
  4    partition p1 values less than (300000),
  5    partition p2 values less than (600000),
  6    partition p3 values less than (900000),
  7    partition p4 values less than (1200000),
  8    partition pmax values less than (maxvalue)
  9  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 1000000;

1000000 rows created.

SQL>
SQL> create index IX on T ( x ) local;

Index created.

SQL>
SQL> alter table T add constraint T_PK primary key ( x ) using index local
  2  /

Table altered.

SQL>
SQL> select bytes
  2  from user_segments
  3  where segment_name = 'T';

     BYTES
----------
  75497472
  75497472
  75497472
  25165824

You can see that the earlier partitions are approx 70meg in size, and the “current” one is 25meg in size.  Now in another session I’m running inserts in batches to simulate online activity which will make the table grow as the new rows come in.


SQL> declare
  2    l_start int;
  3    l_cnt int := 1;
  4  begin
  5    select max(x) into l_start from t;
  6
  7    for i in 1 .. 1000 loop
  8      for j in 1 .. 10000 loop
  9        insert into t values (l_start+l_cnt,i);
 10        l_cnt := l_cnt + 1;
 11      end loop;
 12      commit;
 13      dbms_lock.sleep(4);
 14    end loop;
 15  end;
 16  /

And now, here’s my routine that keep’s an eye on things and automatically do some partition maintenance when the next-to-last partition gets large than 30meg


SQL> set serverout on
SQL> declare
  2    l_has_Rows int;
  3    l_hi_par  varchar2(30);
  4    l_hi_bytes int;
  5    l_hwm int;
  6
  7    procedure ddl(m varchar2) is
  8    begin
  9      dbms_output.put_line(m);
 10      execute immediate m;
 11    end;
 12  begin
 13    for i in ( select p.partition_name, s.bytes, p.partition_position
 14               from   user_segments s,
 15                      user_tab_partitions p
 16               where  p.table_name= 'T'
 17               and    p.table_name = s.segment_name(+)
 18               and    p.partition_name = s.partition_name(+)
 19               order by p.partition_position asc
 20               )
 21    loop
 22        execute immediate 'select count(*) from t partition ( '||i.partition_name||') where rownum = 1' into l_has_rows;
 23        dbms_output.put_line(i.partition_name||':'||i.bytes||':'||l_has_rows);
 24        if l_has_rows > 0 then
 25          --
 26          -- we've hit a partition with rows
 27          --
 28          if i.partition_name = 'PMAX' then
 29            raise_application_error(-20000,'We got rows in PMAX...thats a problem');
 30          end if;
 31
 32          l_hi_par := i.partition_name;
 33          l_hi_bytes := i.bytes;
 34        else
 35          --
 36          -- see if we've hit PMAX, check size of prev one
 37          --
 38          if l_hi_bytes > 30*1024*1024 then
 39            execute immediate 'select max(x) from t partition ( '||l_hi_par||')' into l_hwm;
 40
 41            ddl('alter table T split partition '||l_hi_par||' at ('||(l_hwm+10000)||') into (partition '||l_hi_par||', partition ptmp)');
 42            ddl('alter table T split partition pmax at ('||(l_hwm+10000+300000)||') into (partition p'||i.partition_position||', partition pmax)');
 43            ddl('alter table T merge partitions ptmp,p'||i.partition_position||' into partition p'||i.partition_position);
 44
 45          end if;
 46          exit;
 47        end if;
 48    end loop;
 49  end;
 50  /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:25165824:1
PMAX::0

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
PMAX::0
alter table T split partition P4 at (1020000) into (partition P4, partition ptmp)
alter table T split partition pmax at (1320000) into (partition p5, partition pmax)
alter table T merge partitions ptmp,p5 into partition p5

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:8388608:1
PMAX::0

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:16777216:1
PMAX::0

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:33554432:1
PMAX::0
alter table T split partition P5 at (1140000) into (partition P5, partition ptmp)
alter table T split partition pmax at (1440000) into (partition p6, partition pmax)
alter table T merge partitions ptmp,p6 into partition p6

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:33554432:1
P6:8388608:1
PMAX::0

PL/SQL procedure successfully completed.

SQL>

The first execution did nothing but report that partition  P4 was 25megs in size.  By the time of the next run, it was 33meg in size and so we split it, and created a new empty partition P5, being the merger of the two remnats we carved off the existing P4 and PMAX.  Then the next couple of executions required nothing to be done, and finally we did the same to end up with P6.

So there’s the beginnings of a tool to keep partition sizes in check for random data arrival.  Enjoy.

Something new learned every day

One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you thought you already knew.

Just today, someone posted a question about the new DEFAULT ON NULL syntax in 12c, with the following observation:

“Standard inserts advance the sequence only when needed, but a PL/SQL for-loop advances the sequence all the time”

And here was their test case… (Sidebar:  WOO HOO!!! Yippee !!!! A test case !! A test case !!! People get it !!! Smile )


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> BEGIN
  2  INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');
  3  INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');
  4  INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');
  5  INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         1 DESCRIPTION only

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> BEGIN
  2  FOR i IN 1..3 LOOP
  3  INSERT INTO t1 (col1, description) VALUES (999 + i,999 + i || ',DESCRIPTION');
  4  END LOOP;
  5
  6  INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
  7
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         4 DESCRIPTION only


That seems fairly conclusive – the sequence was fetched once in the first instance, and four times in the second instance.

But we can explore a little further.  I took out the PL/SQL elements and reduced it down to a simpler version


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         1 DESCRIPTION only

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         4 DESCRIPTION only

SQL>
SQL>
SQL>
SQL>



No PL/SQL here yet the observation is the same.  In fact, the only difference is that in the latter case, the INSERT’s contain an expression for column bound the sequence.  And that’s the clue.

The column is defined as:

DEFAULT ON NULL default_seq.NEXTVAL

So only if the target value for the column is null, we’ll use the sequence.  For an expression, we won’t know whether we’ll need a sequence value until the moment that expression is evaluated.  So we might need a sequence value handy to slot in there in case the expression comes out as null.  So my hypothesis is that we’ll need to grab one and bring it along for the ride, so to speak.  Let’s redo the experiment, still with a default but without the ON NULL.




SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT default_seq.NEXTVAL,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         1 DESCRIPTION only


So there you have it. Expressions combined with DEFAULT ON NULL might massage your sequence more than you expect. Not this this should be a problem, because we don’t really care what number the sequence is anyway. But you might want to bump up your sequence cache size to accommodate it. Note, that the same applies for just a simple bind variable (I’ve put the ON NULL clause back in and run the script below)


SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
         0 999,DESCRIPTION
         0 999,DESCRIPTION
         0 999,DESCRIPTION
         4 DESCRIPTION only


On building SQL

I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL.

You can see the question and the answer here

But I thought it might be worth explaining the process.  Of course, the way I approach the problem might be totally different to the way others would, and that’s fine.  But for the novice, you might get something useful out of this.

So here is January 2016.

image

The first thing I’m thinking to print a calendar is

  • I will need to know the number of days in the month
  • I will need to know what day the month starts on
  • I will need to segment the data into weeks

So let’s tackle them.

Number of days in the month

I can use LAST_DAY for that.


SQL> select last_day(date '2016-01-16') end_of_month from dual;

END_OF_MO
---------
31-JAN-16

SQL>
SQL> select to_char(last_day(date '2016-01-16'),'DD') days_in_month from dual;

DA
--
31

and because I will be printing 31 days, I will need to generate 31 days worth of rows. Taking the above, I can use the familiar ‘connect by’ trick to generate my date


SQL> select rownum d
  2  from dual
  3  connect by level <= to_number(to_char(last_day(date '2016-01-16'),'DD'));

         D
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31

31 rows selected.

So there are my days, now I just need to jiggle them around so they look like a calendar Smile

Next I need to know when the month starts.  First I truncate the date down to the month level


SQL> select trunc(date '2016-01-16','MM') mth_start from dual;

MTH_START
---------
01-JAN-16

and then I want to know what day of the week it commences on


SQL> select to_number(to_char(trunc(date '2016-01-16','MM'),'D')) starts_on from dual;

 STARTS_ON
----------
         6

My first attempt at working out a week number is then simply to divide by 7 and truncate. I’ve just substituted the explicit first of month literal here for simplicity. We’ll put the full expression from above back in later.


SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-1+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3  from dual
  4  connect by level <= 31
  5  /

DY          WEEK_NUM
--------- ----------
01-JAN-16          0
02-JAN-16          1
03-JAN-16          1
04-JAN-16          1
05-JAN-16          1
06-JAN-16          1
07-JAN-16          1
08-JAN-16          1
09-JAN-16          2
10-JAN-16          2
11-JAN-16          2
...

Now I compare that to my true calendar – I see that the 3rd starts on a Sunday, whereas my basic trunc would have the 2nd being Sunday.  So I need to offset things by 1, and try again



SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3  from dual
  4  connect by level <= 31;

DY          WEEK_NUM
--------- ----------
01-JAN-16          0
02-JAN-16          0
03-JAN-16          1
04-JAN-16          1
05-JAN-16          1
06-JAN-16          1
07-JAN-16          1
08-JAN-16          1
09-JAN-16          1
10-JAN-16          2
11-JAN-16          2
12-JAN-16          2
13-JAN-16          2
...

That looks better. Now I can also add in the day of the week



SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3         to_char(trunc(date '2016-01-01','MM') -1 + rownum,'D') dow
  4  from dual
  5  connect by level <= 31;

DY          WEEK_NUM D
--------- ---------- -
01-JAN-16          0 6
02-JAN-16          0 7
03-JAN-16          1 1
04-JAN-16          1 2
05-JAN-16          1 3
06-JAN-16          1 4
07-JAN-16          1 5
08-JAN-16          1 6
09-JAN-16          1 7
10-JAN-16          2 1
11-JAN-16          2 2
12-JAN-16          2 3

And we are pretty close to being complete. We can now use PIVOT or the common DECODE expression to flip the rows into columns. And hence we arrive at this



SQL> select
  2   max(decode(dow,1,d,null)) Sun,
  3   max(decode(dow,2,d,null)) Mon,
  4   max(decode(dow,3,d,null)) Tue,
  5   max(decode(dow,4,d,null)) Wed,
  6   max(decode(dow,5,d,null)) Thu,
  7   max(decode(dow,6,d,null)) Fri,
  8   max(decode(dow,7,d,null)) Sat
  9  from
 10   ( select rownum d
 11            ,rownum-2+to_number(to_char(date '2016-01-01','D')) p
 12            ,to_char(date '2016-01-01' -1 + rownum,'D') dow
 13     from dual
 14     connect by level <= 31
 15    )
 16  group by trunc(p/7)
 17  order by trunc(p/7);

       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                1          2
         3          4          5          6          7          8          9
        10         11         12         13         14         15         16
        17         18         19         20         21         22         23
        24         25         26         27         28         29         30
        31


Finally, we put back our original expressions so that we can accept any date of the month, and work from that. Hence our complete solution becomes



SQL> define my_date = 12-JAN-16
SQL>
SQL> select
  2   max(decode(dow,1,d,null)) Sun,
  3   max(decode(dow,2,d,null)) Mon,
  4   max(decode(dow,3,d,null)) Tue,
  5   max(decode(dow,4,d,null)) Wed,
  6   max(decode(dow,5,d,null)) Thu,
  7   max(decode(dow,6,d,null)) Fri,
  8   max(decode(dow,7,d,null)) Sat
  9  from
 10   ( select rownum d
 11            ,rownum-2+to_number(to_char(trunc(to_date('&my_date'),'MM'),'D')) p
 12            ,to_char(trunc(to_date('&my_date'),'MM') -1 + rownum,'D') dow
 13     from dual
 14     connect by level <=
 15        to_number(to_char(last_day(to_date('&my_date')),'DD'))
 16    )
 17  group by trunc(p/7)
 18  order by trunc(p/7);

       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                1          2
         3          4          5          6          7          8          9
        10         11         12         13         14         15         16
        17         18         19         20         21         22         23
        24         25         26         27         28         29         30
        31

and we’re done. I hope you found this useful.