1.4.1 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:

[4] This insight was provided by Solomon Yakobson.

/* 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.

1.4.2 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.