Topic:

Hacking' pays off (1 of 10), Read 54 times

Conf:

General PL/SQL Discussions

From:

Solomon Yakobson syakobson@erols.com

Date:

Wednesday, November 29, 2000 01:14 PM

Today I had some free time on my hands and decided to browse through STANDARD.SQL package on Oracle 8.1.6. I'm so glad I did. I bet most of us had issues with DATE datatype. Not being able to store just time, not being able to do time intervals, not being able to get date and time with less than a second precision, not being able to handle time zones, etc, etc, etc. Let me tell you, looks like these days are over. All the above features are already in PL/SQL - they are just disabled. And if you dare, you can enable them (courtesy of Oracle developers leaving comments in STANDARD.SQL). Anyway, I've done it before (I do not know if my postings on "hacking" STANDARD.SQL are still on the Pipeline) and I did it again. Here it is.
If you look at STANDARD.SQL you'll notice a comment:

 
--#### This is the end of 8.0 Standard


followed by very interesting type definitions and various functions against them:

 
  type TIME is new DATE_BASE;
  type TIMESTAMP is new DATE_BASE;
  type "TIME WITH TIME ZONE" is new DATE_BASE;
  type "TIMESTAMP WITH TIME ZONE" is new DATE_BASE;
  type "INTERVAL YEAR TO MONTH" is new DATE_BASE;
  type "INTERVAL DAY TO SECOND" is new DATE_BASE;


However, trying to declare a variable of one of these types would cause errors. Fortunately, STANDARD.SQL has one more comment:

 
--#### new_names pragmas

  -- This is an internal pragma that restricts the use
  -- of particular new entries in package standard.
  -- It is only valid in package standard.
  -- Note that left out of the 8.1.5 set are non datetime
  -- entries urowid, "UROWID ", self_is_null and trim.


Followed by pragma new_names set for various elements. So I simply commented out new_names pragma:

 
  -- pragma new_names('8.1.5',
  --                  time,"TIME WITH TIME ZONE",
  --                  timestamp,"TIMESTAMP WITH TIME ZONE",
  --                  "INTERVAL DAY TO SECOND",
  --                  "INTERVAL YEAR TO MONTH",
  --                  to_time, to_timestamp,
  --                  to_time_tz, to_timestamp_tz,
  --                  " SYS$DSINTERVALSUBTRACT",
  --                  " SYS$YMINTERVALSUBTRACT",
  --                  to_yminterval,to_dsinterval,
  --                  NUMTOYMINTERVAL, NUMTODSINTERVAL,
  --                  current_date, 
  --                  current_time,current_timestamp);

  -- pragma new_names('8.1.6',
  --                  dbtimezone, sessiontimezone, localtimestamp,
  --                  localtime, to_local_tz, to_db_tz,
  --                  cube, rollup, grouping);


and recompiled package STANDARD.SQL. Guess what? It is amazing to get time up to .000000001 sec. precision:

 
SQL> DECLARE t TIME;
  2  BEGIN
  3  t:=localtime;
  4  DBMS_OUTPUT.PUT_LINE(t);
  5  END;
  6  /
11.07.33.000000945 AM

PL/SQL procedure successfully completed.


I am not sure how Oracle managed to get nine digits after the dot. Maybe Oracle uses internal clock? But internal clock frequency on my NT is 100MHZ which means 1 power cycle takes .00000001 of a sec and only 8 digits after the dot.
You can also get date and time with .00001 sec. precision:

 
SQL> DECLARE ts TIMESTAMP;
  2  BEGIN
  3  ts:=current_timestamp;
  4  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ts,'DD-Mon-YYYY HH24.MI.SS.SSSSS'));
  5  END;
  6  /
29-Nov-2000 11.21.38.40898

PL/SQL procedure successfully completed.


Why am I saying .00001 sec precision? Empirically I noticed fractional part is repeated again if you specify more decimal points:

 
SQL> DECLARE ts TIMESTAMP;
  2  BEGIN
  3  ts:=current_timestamp;
  4  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ts,'DD-Mon-YYYY HH24.MI.SS.SSSSSSSSSSSSSSS'));
  5  END;
  6  /
29-Nov-2000 11.24.37.410774107741077

PL/SQL procedure successfully completed.


I am sure Oracle is testing it and fixing it, that is why all these features are disabled in production release.
You can also get time/date and time without and with time zone:

 
SQL> DECLARE t TIME;
  2  BEGIN
  3  t:=current_time;
  4  DBMS_OUTPUT.PUT_LINE(t);
  5  END;
  6  /
06.05.38 PM

PL/SQL procedure successfully completed.

SQL> DECLARE t "TIME WITH TIME ZONE";
  2  BEGIN
  3  t:=current_time;
  4  DBMS_OUTPUT.PUT_LINE(t);
  5  END;
  6  /
05.59.51 PM +01:00

PL/SQL procedure successfully completed.


Quite interesting. Database I am connected to is "hacked" 8.1.7 on Sun Solaris on EST (GMT - 5) while client is "hacked" 8.1.6 on NT on GMT + 1 time zone. So we can assume TIME WITH TIME ZONE data type shows time converted to client session time zone. Remember, DATE data type always returns time in database server time zone (which, so far, we never were able to calculate):

 
SQL> exec dbms_output.put_line(to_char(sysdate,'HH24:MI:SS'))
12:07:30

PL/SQL procedure successfully completed.


I put "so far" since we can now. One of the functions enabled by my "hacking" is dbtimezone:

 
SQL> exec dbms_output.put_line(dbtimezone)
-05:00

PL/SQL procedure successfully completed.


Tells me time zone of the database I am connected to. Another function sessiontimezone tells me my session time zone:

 
SQL> exec dbms_output.put_line(sessiontimezone)
+ 01:00

PL/SQL procedure successfully completed.


Now interval data types:

 
SQL> DECLARE t "TIME WITH TIME ZONE";
  2  i "INTERVAL DAY TO SECOND";
  3  BEGIN
  4  t:=current_time;
  5  DBMS_LOCK.SLEEP(2);
  6  i:=current_time - t;
  7  DBMS_OUTPUT.PUT_LINE(i);
  8  END;
  9  /
+00 00:00:02.394452

PL/SQL procedure successfully completed.


Well, it is quite exciting but. back to reality - I have some work to do. Hope I inspired some of you to play with these "back to the future" PL/SQL features.

Solomon Yakobson