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:
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,
where NN is the version number. So if you are running Oracle 7.3, you will find the source code files in /this directory/:
If you are working in Windows NT, these files are located in,
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,
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:
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.
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 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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.