Oracle Built-in Packages

Examining Built-in Package Source Code

When you install the Oracle database, the source code files for all of the built-in packages are placed on your hard disk (either on your own personal computer or on some “remote” server). I recommend strongly that you take some time to examine these files for several reasons:

1. The files contain documentation that may complement the contents of this book and improve your understanding of how to use the packages.

2. The files are the source code. They contain the definitive listing of what is available in the package. This book makes a valiant effort to offer up-to-date information on almost every one of the most commonly used packages, but it never hurts to check the original. For example, I suggest that every time you install a new version of the database, you should check (at a minimum) the dbmsutil.sql file, to see if Oracle Corporation has added anything to its miscellaneous package, DBMS_UTILITY.

3. It can be an awful lot of fun to see how Oracle’s own developers construct and document their own PL /SQL packages. This is particularly true of the file containing the STANDARD package, standard.sql.

4. Dependencies. The built-in package file can provide you with other valuable information, including which built-in packages and/or other objects must already be in the database before you can proceed with the installation.

Where do you find all of this interesting code? If you are working in a UNIX environment, the files that define the built-in packages may be found in,

$ORACLE_HOME/rdbmsNN/admin

where NN is the version number. So if you are running Oracle 7.3, you will find the source code files in /this directory/:

$ORACLE_HOME/rdbms73/admin

If you are working in Windows NT, these files are located in,

C:\OraNT\RdbmsNN\admin

where C is the drive on which Oracle was installed. You can probably deduce the pattern at this point for other operating systems.

If you are working in a UNIX environment that conforms to the OFA (Optimal Flexible Architecture) configuration standards, the catalog scripts will be found under,

$ORACLE_HOME/rdbms/admin

with the $ORACLE_HOME environment variable containing the Oracle version information.

If you are working with VAX/VMS, the software directory tree structure often looks like this:

[ORACLE733.RDBMS.ADMIN]
[ORACLE803.RDBMS.ADMIN]

Note that you do not necessarily have read access on these directories, so I cannot guarantee that you will be able to view the contents of these files. If you can, however, I suggest that you first stop at standard.sql (present up through Oracle 7.3, but seems to disappear with some installations of Oracle 8.0). This file contains the definition of the STANDARD package, which is explained in the next section.

The STANDARD Package

One of the most surprising things I ever learned about the PL /SQL language is that the most basic elements of that language are defined in a PL /SQL package called STANDARD. According to the modification history in the standard.sql file, this package was created on November 21, 1992. Contained inside it are many lessons about the very nature of the PL /SQL language.

Consider the TO_DATE and SUBSTR functions. Although they seem like basic, low-level language elements, both of these are functions defined (and overloaded) in the STANDARD package. Even more astonishing, the most basic operators in the PL /SQL language, such as +, IN, and LIKE, are actually defined as functions in that same package.

Here, for example, are the definitions of the LIKE and = operators,

function 'LIKE' (str VARCHAR2, pat VARCHAR2) return BOOLEAN;
function '='  (LEFT NUMBER, RIGHT NUMBER) return BOOLEAN;

and here is the implementation of LIKE:

function 'LIKE' (str varchar2, pat varchar2) return boolean is
begin
  return peslik(str, pat);
end;

What is this peslik function? Ah, that is where, when, and how Oracle “cheats” (or, at least, makes the rest of us PL /SQL developers jealous):

function peslik(str varchar2, pat varchar2) return boolean;
    pragma interface (c,peslik);

The peslik function is a stub program for a callout to C.

You will also discover that all PL /SQL datatypes are defined in the STANDARD package specification as types and subtypes:

subtype INTEGER is NUMBER(38,0);
subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
subtype NATURAL is BINARY_INTEGER range 0..2147483647;
subtype NATURALN is NATURAL not null;
subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;  -- for SIGN functions

Interestingly, the subtype SIGNTYPE is not listed in PL /SQL manual. Once discovered here, though, you can put it to use in your code.^[4] If, for example, you need a variable that can only have values -1, 0, 1, and NULL, you can write something like this:

/* Filename on companion disk: creind.sp */
DECLARE my_variable SIGNTYPE;

Trying to assign my_variable outside of SIGNTYPE range results in an error:

SQL> DECLARE my_variable SIGNTYPE;
  2  BEGIN
  3    my_variable := 2;
  4  END;
  5  /
DECLARE my_variable SIGNTYPE;

*

ERROR at line 1:
ORA-06502: PL /SQL: numeric or value error
ORA-06512: at line 3

All predefined exceptions are also defined in the STANDARD package specification:

LOGIN_DENIED exception;
  pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');

If you are still running Oracle7, take a look at standard.sql. You might even want to copy it to some other location, so that when you upgrade to Oracle8, you won’t lose access to this file and all of its fascinating contents.

The DBMS_STANDARD Package

The other default package, DBMS_STANDARD, deserves some mention. It can be found in the dbmsstdx.sql file, and it contains, according to the file’s own documentation, “kernel extensions to package STANDARD... mostly utility routines for triggers.” You will find in DBMS_STANDARD the following programs:

PROCEDURE raise_application_error
   (num BINARY_INTEGER, 
    msg VARCHAR2,
    keeperrorstack BOOLEAN DEFAULT FALSE);

FUNCTION inserting RETURN BOOLEAN;
FUNCTION deleting  RETURN BOOLEAN;
FUNCTION updating  RETURN BOOLEAN;
FUNCTION updating (colnam varchar2) RETURN BOOLEAN;

So whenever you write code like this,

IF hiredate < ADD_MONTHS (SYSDATE, -216)
THEN
   RAISE_APPLICATION_ERROR 
     (-20000, ' Employee must be at least 18 years old.');
END IF;

you are actually calling a program in the DBMS_STANDARD package. Again, this package is defaulted, so you do not need to qualify references to these procedures and functions.

---

^[4] This insight was provided by Solomon Yakobson.