jump to navigation

STANDARD date considerations in Oracle SQL and PL/SQL July 29, 2015

Posted by mwidlake in internals, PL/SQL.
Tags: , ,

Most of us know that the Oracle DATE datatype has upper and lower limits. From the Oracle 11g Database Concepts manual:

Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default.

I never believe 100% anything I read, so I’ll try that. I’ll set my session to show dates with the AD/BC indicator and step back in time:

Session altered.

-- select today
ora122> select sysdate from dual;


--now let us go back to "the edge of time"
ora122> select sysdate -2457232 from dual;


ora122> select sysdate -2457233 from dual;
select sysdate -2457233 from dual
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

-- Now to do similar in PL/SQL

out_text varchar2(100);
  select to_char(sysdate) into out_text from dual;
  dbms_output.put_line (out_text);

ora122> @std1


PL/SQL procedure successfully completed.

How did I do that? We can see from the SQL that the documentation is correct and SQL refuses to accept a date before the lower limit. How did I get a date before 01-JAN-4712BC in my PL/SQL? Especially as my default SYSDATE?

I’ll let you think about that for 30 seconds, you can look at a picture of my recently gone and much missed cat (NB she is happily snoozing in this shot!).

I miss this fleabag

I *really* miss this fleabag

So how did I do it? I cheated.

But I cheated in a very, very interesting way. I did not show you all of my PL/SQL code, which I now reveal below…:

sysdate varchar2(20) := '31-DEC-4713BC';
out_text varchar2(100);
  select to_char(sysdate) into out_text from dual;
  dbms_output.put_line (out_text);

So, showing you my whole code (see, don’t believe everything you read – sometimes things are simply wrong and sometimes people deceive you) you can see the critical part at the start. My anonymous PL/SQL block is in fact a child block to another. And in that parent block, right at the start, I over-ride the definition of SYSDATE. in the declaration section

sysdate varchar2(20) := ’31-DEC-4713BC’;

I not only set it to a specific value, I set it to be a Varchar2 datatype. The TO_CHAR of it later on in the logic, which I included in the code I originally showed you, was just more subterfuge on my part. The PL/SQL engine does not care if you TO_CHAR an already CHAR-type field, but it hid the fact that I’d played this trick.

You could define a local SYSDATE variable, as a date, set to a specific date and time if you wish. Even one in the future. And anyone seeing odd behaviour and checking to see if the initialization paramater FIXED_DATE had been set would find that it had not and might soon be questioning their own sanity.

How many of you knew you could do that? You can over-ride what most of us would consider a Reserved Word in PL/SQL. I suspect it is something that people new to PL/SQL might find out by accident (because no one told them you could not use reserved words for variable names) but experienced people would not as it is simply a daft thing to do. I’d go further, it is a stupid thing to do. Think how much trouble it could cause in introducing bugs and making the code hard to understand. {And thinking further about this, I might see if I can get re-employed at a couple of places and starting doing things like this, just for the evil pleasure, as it could be an utter nightmare to spot}.

The reason this trick works is that SYSDATE, along with many interesting things, are not “Built In” to the PL/SQL language but are defined in two key packages – SYS.STANDARD and SYS.DBMS_STANDARD. These are always there and define many core things. You can DESC both of them in SQL*Plus or pull the package specification out of DBA_SOURCE and, unlike many of the other Built In packages, the code is not wrapped for STANDARD, so you can look at it. You can do this with a “lowly” DBA-type user, you do not need to be on as SYS or SYSTEM.

I am not sure of the exact rules but I think that when you use a locally qualified variable (ie you do not state the code block, package or stored function/procedure it comes from) it looks at the current variables as defined in the current and parent PL/SQL blocks first and then looks at STANDARD and then DBMS_STANDARD. I am not going to mess with STANDARD or DBMS_STANDARD, even on my play box, to find out the exact order of the two. If I spent 10 minutes looking at the specifications I might be able to see that one references the others I suppose…

This is part of the specification from DBMS_STANDARD:

package dbms_standard is
  -- types
   type ora_name_list_t is table of varchar2(64);

  -- DBMS_ID and DBMS_QUOTED_ID define the length of identifiers
  -- in objects for SQL, PL/SQL and users.
   subtype dbms_id is varchar2(30);
   subtype dbms_quoted_id is varchar2(32);

   subtype dbms_id_30 is varchar2(30);
   subtype dbms_quoted_id_30 is varchar2(32);
   subtype dbms_id_128 is varchar2(128);
   subtype dbms_quoted_id_128 is varchar2(130);

  -- Trigger Operations
  procedure raise_application_error(num binary_integer, msg varchar2,
      keeperrorstack boolean default FALSE);
    pragma interface (C, raise_application_error);         -- 1 (see psdicd.c)
    pragma restrict_references (raise_application_error, WNPS, RNPS, WNDS, RNDS);
  function inserting return boolean;
    pragma interface (C, inserting);                       -- 2
    pragma restrict_references (inserting, WNPS, RNPS, WNDS);
  function deleting  return boolean;
    pragma interface (C, deleting);                        -- 3
    pragma restrict_references (deleting, WNPS, RNPS, WNDS);
  function updating  return boolean;
    pragma interface (C, updating);                        -- 4
    pragma restrict_references (updating, WNPS, RNPS, WNDS);

You won’t find a package body of DBMS_STANDARD – that is because, I believe, all entries in the package specification are types or functions/procedures that lead to C functions, via the ADA-like {If you did not know, PL/SQL is based on the ADA language} pragma directives of “pragma interface (C, {something}), which says this function/procedure is coded in another language (C in this case) and is called {something}. Don’t ask me more, I don’t know.

procedure commit;
pragma interface (C, commit);

Even the base data types are defined in STANDARD:

package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SED edit occurs!

  /********** Types and subtypes, do not reorder **********/
  type BOOLEAN is (FALSE, TRUE);

  type DATE is DATE_BASE;

  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)
  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;
  subtype SMALLINT is NUMBER(38,0);
  subtype DECIMAL is NUMBER(38,0);
  subtype NUMERIC is DECIMAL;
  subtype DEC is DECIMAL;

  subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
  subtype NATURAL is BINARY_INTEGER range 0..2147483647;
  subtype NATURALN is NATURAL not null;
  subtype POSITIVE is BINARY_INTEGER range 1..2147483647;
  subtype POSITIVEN is POSITIVE not null;
  subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;  -- for SIGN functions


  subtype VARCHAR is VARCHAR2;
  subtype STRING is VARCHAR2;

  subtype LONG is VARCHAR2(32760);

Anyway, I leave the reader to go and look at the package specifications and the STANDARD package body {some of which I show at the end} but I leave you with a repeat of the above warnings: Don’t go replacing the core variables and functions in your PL/SQL code just because you can and do not, repeat, do NOT mess with those two packages. I am sure Oracle Corp will throw your support contract out the window if you do.

As promised above, here is the code for SYSDATE, in SYS.STANDARD, and it is very interesting – in the manner as mentioned above it calls a function (pessdt) that only calls a C program (presumably to get the datetime from the server clock) and failing that, reverts to the SQL method of selecting the pseudocolumn from dual. SYSTIMESTAMP below it is the same:

  function pessdt return DATE;
    pragma interface (c,pessdt);

  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is
    d date;
    d := pessdt;
    return d;
      select sysdate into d from sys.dual;
      return d;
  function pessts return timestamp_tz_unconstrained;
    pragma interface (c,pessts);

  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT systimestamp FROM dual;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  FUNCTION systimestamp RETURN timestamp_tz_unconstrained
  IS  t timestamp_tz_unconstrained;
    t := pessts;
    RETURN t;
      SELECT systimestamp INTO t FROM sys.dual;
      RETURN t;


1. Brian Tkatch - July 29, 2015

>sometimes things are simnply wrong
Beautiful! 🙂

>The reason this trick works is that SYSDATE, along with many interesting things,
>are not “Built In” to the PL/SQL language
>but are defined in two key packages – SYS.STANDARD and SYS.DBMS_STANDARD.

Wow. Good stuff!

mwidlake - July 30, 2015

Well, if you like it, I know I hit the spot 🙂

Now, don’t tell him, but Steve F lead me down this path.

2. David Harper - July 31, 2015

First of all, condolences on the loss of your cat.

Your discussion on the limitations of dates caught my attention, as an astronomer, when I saw the year -4713 in the error message. Astronomers use a day count called the Julian Day Number to keep track of dates and times over long periods without the complications introduced by specific calendar systems. The start epoch of the Julian Day Number system is midday (12:00) on 1 January 4713 BCE. I’d hazard a guess that Oracle probably uses a day count based on the Julian Day Number as the internal canonical representation of a date, in much the same way that UNIX systems use a count of seconds since 1 January 1970 as the canonical representation of a date/time for things like file creation timestamps.

The rather curious choice of 1 January 4713 BCE as the starting epoch was apparently made by the inventor of the Julian Day Number because it marks the most recent year when three important calendrical cycles started at the same time: the 28-year cycle of days of the week against the calendar; the 19-year Metonic cycle, after which the phases of the Moon repeat on the same calendar dates; and the 15-year Roman taxation cycle known as the Indiction.

For astronomers, this very distant starting date has the advantage that the Julian Day Number will always be positive, even for historical records of events such as eclipses. The down side is that values are now seven-digit numbers. At midday (UT) today, for example, the JD number will be 2457235.

mwidlake - July 31, 2015

Thank you David, both for the astronomical detail and also the kind thoughts on my cat – She was “just a moggy” but we’d had her for 18 years and she was a very good-natured companion.

If anyone else wants to know more about the Julian Date and certain oddities with it in respect of Oracle’s interpretation of it, I can do no better then refer you to a post by a friend of mine, David Fitzjarrell, on the subject : https://dfitzjarrell.wordpress.com/2008/08/07/what-do-julius-caesar-jacques-cassini-and-pope-gregory-xiii-have-in-common-oracle/

3. Oren Nakdimon - August 3, 2015

Hi Martin.
A nice trick 🙂
And a good “don’t try it at home, kids” warning.

There is a nuance regarding using keywords that I think is worth mentioning.
There are reserved words in PL/SQL that are not reserved in SQL. For example: WHEN.
So, wearing your SQL hat, you can name a table column WHEN, and it’s fine, since it is NOT a reserved word in SQL. But later, wearing the PL/SQL hat, this column may be referenced in some PL/SQL program unit, for example for data type anchoring:

l_var tbl.when%type;

This will throw the PLS-00103 exception during compilation, because WHEN is a reserved word in PL/SQL.

A solution is using quoting:

l_var tbl.”WHEN”%type;

A better solution would probably be refraining from using (SQL reserved words) UNION (PL/SQL reserved words), but this requires much more attention, because you don’t get any warning when using a PL/SQL reserved word in SQL.
When trying to use a SQL reserved word in SQL you get an exception.
When trying to use a PL/SQL reserved word in PL/SQL you get an exception.


mwidlake - August 3, 2015

Thanks Oren, it is wise advice.

I suppose as I have used both SQL and PL/SQL for most of my working life, I just know what the command words are (if not explicitly the reserved words as shown in V$RESERVED_WORDS) and do not use them as object names or variables. The only one I can remember coming up against for real is a table called EXCEPTIONS. It is listed as a reserved word, but it is not (the below works for both EXCEPTION and EXCEPTIONS). The SQL reserved words view shows that EXCEPTIONS is reserved but NOT explicitly prevented from being used as an identifier, type or attribute.

ora122> create table exceptions(col_a varchar2(10));

Table created.

ora122> exec insert into exceptions(col_a) values (‘ERIC’)

PL/SQL procedure successfully completed.

A list of PL/SQL reserved words and key words for oracle 12 can be found at http://docs.oracle.com/database/121/LNPLS/reservewords.htm

4. » Some Oracle Bloggers You Should Check Out - September 14, 2015

[…] Martin Widlake – trivia: one of the nicest Martin’s I know and is responsible for the London Beer Oracle meetups. Suggested post: STANDARD date considerations in Oracle SQL and PL/SQL […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: