ROWID: "this dreadful identifier"

Today I'm taking a look at the core packages of the PL/SQL language, namely the STANDARD and DBMS_STANDARD packages in the SYS schema. These packages contain a lot of the language features we use every day in PL/SQL programming.

These two packages are special in that you do not need to qualify the name of functions, procedures and constants in these packages with the package name. For example, SYSDATE and USER and DECODE and so on are declared in the STANDARD package, but you can use it without having to specify STANDARD.SYSDATE or STANDARD.USER or STANDARD.DECODE (which would get a bit tedious after a while...).

But enough lecturing! This post is really about the fun and/or weird stuff found in the comments of the STANDARD package.

You can have a peek at the package specification yourself by running the following query:


select text
from all_source
where owner = 'SYS'
and name = 'STANDARD'
and type = 'PACKAGE'
order by line


I'm using Oracle 10g XE to run this, the code and comments may obviously be different in other versions.

"PRIOR is WEIRD"

Line 700:


  -- PRIOR is WEIRD - For now, it will be treated as a function call.
  -- Does the function only take a column name?  how about its use in
  -- a predicate?
  function 'PRIOR'(colname VARCHAR2 CHARACTER SET ANY_CS)
          return VARCHAR2 CHARACTER SET colname%CHARSET;
      pragma FIPSFLAG('PRIOR', 1452);

As programmers, I guess we all leave some questions in our code, but I am a bit concerned to find questions like this in production code in the core package of the database...

"ROWID: this dreadful identifier"

Line 863:

  -- ROWID: this dreadful identifier is supposed to represent a datatype

  -- outside of SQL and and a pseudo-column (function, to us) when inside

  -- a sql statement.  ADA data model doesn't allow for any

  -- function X return X;

  -- so we must special case this.  Yuk.  There's special-case code in ph2nre

  -- which maps "rowid" to "rowid " if we're inside a SQL stmt.

  function "ROWID " return ROWID;

    pragma builtin('ROWID ', 1, 209, 240);  -- this had better never be called.

Yeah, yuk, nobody likes to special case stuff.

And remember: This had better never be called...! :-)

Who is JEM?

Line 978:

  -- Next two added for NLS 6/3/92 JEM.

  function TO_CHAR(left date, format varchar2, parms varchar2) return varchar2;

  function TO_CHAR(left number, format varchar2, parms varchar2)

    return varchar2;

This just reminds us that PL/SQL has been around for a long time; it's 20 years since this comment was added to the code (1992). That's kind of cool. Does anybody know who JEM is, and if he/she still works at Oracle?

"Under development"

Line 1483:

  --## Support for ANSI datetime data types is under development.

  --## The following operations, as well as the related types and

  --## operations defined above in the 8.1.5 section, are not yet

  --## available for use and are still subject to change.

I'm looking at version 10.2 (XE) and I see comments related to work under development in version 8.1.6. Was this ever finished?

"Should there be a pragma?"

Line 3088:

  -- Should there be a 8.2 new names pragma ?

I don't know. Since there was never a version 8.2 I guess the question is now moot.

Oracle 10i ?

Line 3132:

  -- 10i Binary Floating-point Built-in Functions

Looks like Oracle version 10 was intended to be called 10i (for "internet"), like its predecessors 8i and 9i. As we all know, it was called 10g (for "grid") instead, just like 11g.

Rumor has it the next version will be called 12c (for "cloud"). Ironic, since Larry himself called cloud "just water vapor" a few years back. The marketing guys are to blame, I guess.

"Ref Cursor has problems"

Line 3242:

--  Ref Cursor has problems. The MOVCR instruction needs more information than

--  the other MOV* instructions, including the PVM register of the destination

--  This cannot be easily supplied through the generic NVL instruction, so

--  for now, this flavor will continue to have a real body

--  PRAGMA interface(c,nvl,"pes_dummy",9);       -- Ref Cursor

I won't pretend to understand any of that, except the fact that the Ref Cursor has some kind of problem. Despite this, it's doing a pretty good job in my PL/SQL programs.

We are at the end of the package specification, but for some reason the package body is not wrapped (obfuscated) like most other package bodies in the SYS schema. So we can continue reading...

"The old 'select from dual' thing"

Line 62:

  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do

  -- the old 'select soundex(...) 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 SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS)

        return VARCHAR2 CHARACTER SET ch%CHARSET is

    c VARCHAR2(2000) CHARACTER SET ch%CHARSET;

Sounds like a good idea.

"Perhaps more intelligently in the future"

Line 109:

-- Just call the other to_char with a null format string.

-- Perhaps this can be done more intelligently in the future. JEM 3/14/90.

--  function TO_CHAR(LEFT NUMBER)        return varchar2 is

--  begin

--    return TO_CHAR(LEFT, '');

--  end TO_CHAR;

There's JEM again. This time the comment is even older (22 years ago!). As the whole block of code is now commented out, I guess they already found "a more intelligent way" to do it.

"Why do we need these?"

Line 371:

-- why do we need these dummy bodies for LEVEL and ROWNUM?

  function LEVEL return NUMBER is

        begin return 0.0; end;

  function ROWNUM return NUMBER is

        begin return 0.0; end;

Don't ask me...!

That's it, we're at the end of the STANDARD package. The DBMS_STANDARD package is much shorter and does not really contain any interesting comments. But I'm sure there are other packages in the SYS schema that contain some other gold nuggets -- can you find them?