There are several steps involved in using a built-in package:
Install the built-in package into the database. In most cases, this will be done for you automatically. However, some packages, such as UTL_RAW, are not automatically installed or made publicly accessible, at least in the most recent version of Oracle as this book went to press. The individual chapters in this book will tell you about any special steps you need to take to ensure that the package is installed.
Learn about what is available in the package. You can do this by reading the appropriate section of this book and also by looking at the source code (explained later in this chapter).
Place references to elements of the package in your own code. To do this, you need to know the correct syntax for referencing package elements (read on to find out how to do this).
This section offers a brief introduction to packages. You can find more detailed treatments in both Oracle PL /SQL Programming (O’Reilly & Associates, 1995 and 1997), and Advanced Oracle PL /SQL Programming with Packages (O’Reilly & Associates, 1996), my two previous books on PL /SQL.
A package is a collection of PL /SQL elements that are “packaged” or grouped together within a special BEGIN-END syntax, a kind of “meta-block” of code. Here is a partial list of the kinds of elements you can place in a package:
Cursors
Variables (scalars, records, tables, etc.) and constants
Exception names and PRAGMAs for associating an error number with an exception
PL /SQL table and record TYPE statements
Procedures and functions
Packages are among the least understood and most underutilized features of PL /SQL. That’s a shame, because the package structure is also one of the most useful constructs for building well-designed PL /SQL-based applications. Packages provide a structure in which you can organize your modules and other PL /SQL elements. They encourage proper structured programming techniques in an environment that often befuddles the implementation of structured programming. When you place a program unit into a package, you automatically create a “context” for that program. By collecting related PL /SQL elements in a package, you express that relationship in the very structure of the code itself. Packages are often called “the poor man’s objects” because they support some, but not all, object-oriented rules.
The PL /SQL package is a deceptively simple yet powerful construct. It consists of up to two distinct parts: the specification and the body. The package specification defines the public interface or API (Application Programming Interface) of the package: those elements that can be referenced outside of the package. The package body contains the implementation of the package. In just a few hours you can learn the basic elements of package syntax and rules; there’s not all that much to it. You can spend weeks and months, however, uncovering all the nuances and implications of the package structure.
Of course, if you are working with built-in packages, you can leave those details to Oracle. You just have to figure out how to make the best use of the packages provided.
Probably the most important implication of package structure is how the builder of the package has complete control over what you can see or do. The users of a package can do either of the following:
Execute programs listed in the package specification
Reference elements (variables, constants, exceptions, etc.) listed in the package specification
What can’t a user of a package do? You can’t look inside the package and see how the code was implemented. You can’t bypass the programs in the package specification in order to modify (corrupt) data structures managed inside the package body.
These restrictions are closely tied to the power and usefulness of the package structure. To illustrate this point, consider the following simple timer package. First, the specification:
PACKAGE tmr IS PROCEDURE capture; PROCEDURE show_elapsed; END tmr;
The tmr.capture procedure captures the current time. The tmr.show_elapsed procedure shows the elapsed time. The following script, for example, displays the amount of time it takes to run the calc_totals procedure:
BEGIN tmr.capture; calc_totals; tmr.show_elapsed; END; /
Now let’s take a look at the package body (where all the code for those two procedures resides):
PACKAGE BODY tmr
IS
last_timing NUMBER := NULL;
PROCEDURE capture IS
BEGIN
last_timing := DBMS_UTILITY.GET_TIME;
END;
PROCEDURE show_elapsed IS
BEGIN
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.GET_TIME - last_timing);
END;
END tmr;The DBMS_UTILITY.GET_TIME program is a function from the built-in package, DBMS_UTILITY, which returns the number of hundredths of seconds that have elapsed since an arbitrary point in time. DBMS_OUTPUT is another built-in package; its PUT_LINE procedure displays output from a PL /SQL program to your screen.
Notice that there is another code element defined inside the package body besides the capture and show_elapsed procedures: the last_timing variable. This variable holds the timing value from the last call to tmr.capture. Since last_timing does not appear in the package specification, an external program (i.e., one that is not defined in this package) cannot directly reference that variable. This restriction is illustrated in the Booch diagram[2] Figure 1.1.
So if I try to access the last_timing variable from outside the tmr package, I get an error. This is shown as follows:
SQL> exec tmr.last_timing := 100; begin tmr.last_timing := 100; end; * ERROR at line 1: ORA-06550: line 1, column 14: PLS-00302: component 'LAST_TIMING' must be declared
Why should you or anyone else care about where you define the last_timing variable? Because it illustrates a critical aspect of a package’s value: integrity. If I had placed the variable in the specification, then a user of the package could write over the value of last_timing—and completely invalidate the integrity of the package. Suppose my package specification looked like this:
PACKAGE tmr IS last_timing NUMBER; PROCEDURE capture; PROCEDURE show_elapsed; END tmr;
The package compiles and seems to work as before. But consider the following rewrite of my script to time the calc_totals procedure:
BEGIN tmr.capture; calc_totals; tmr.last_timing := DBMS_UTILITY.GET_TIME; tmr.show_elapsed; END; /
Since tmr.last_timing is now in the package specification, this code will compile, and completely subvert the usefulness of the tmr package. For no matter how much time calc_totals actually takes to execute, the tmr.show_elapsed procedure will always display 0—or very close to 0—hundredths of seconds for elapsed time.
If, on the other hand, I keep last_timing inside the body of the package, only the tmr.capture procedure can modify its value. A user of tmr is, therefore, guaranteed to get dependable results.
This absolute control is the reason that the package structure has been so useful to Oracle Corporation—and one of the reasons the company has constructed dozens of built-in packages. Since you can perform only the operations and access the data structures listed in the package specification, Oracle can make technology available in a highly controlled fashion. As long as its developers write their code properly, there will never be any danger that we can disrupt Oracle Server internals by calling built-in packaged functionality.
As noted earlier, a package can have up to two parts: the specification and the body. When it comes to built-in packages, you really don’t need to concern yourself with the package body. That is the implementation of the package, and something that is the responsibility of Oracle Corporation. With very few exceptions, those package bodies are "wrapped,” which means that they are distributed in an encrypted format that you cannot read. This is just as well, because what you really need to do is study the specification to learn about the capabilities offered in that package.
There are two ways to use a built-in package in your own code:
Notice that you never actually execute a package itself. The package is simply a “container” for the various code elements defined in the package. Let’s take a look at an example to make all this very clear. The DBMS_SQL package (examined at great length in Chapter 2) allows you to execute dynamic SQL (SQL statements constructed at runtime), a feature previously unavailable in the PL /SQL language. Here is a portion of the specification of that package:
CREATE OR REPLACE PACKAGE DBMS_SQL
IS
-- CONSTANTS
--
v6 constant integer := 0;
native constant integer := 1;
v7 constant integer := 2;
--
-- PROCEDURES AND FUNCTIONS
--
FUNCTION open_cursor RETURN INTEGER;
PROCEDURE parse
(c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER);What this tells you is that there are three different constants, one procedure, and one function defined in the package. (There is actually much, much more, of course, but this is all we need to get the point across.) To reference any of the elements, you will use the same " dot notation” used to specify columns in tables.
So if I want to open a dynamic cursor, I use this:
DECLARE dyncur PLS_INTEGER; BEGIN dyncur := DBMS_SQL.OPEN_CURSOR;
And if I want to parse a string using the “native” database method, I would write the following code:
PROCEDURE showemps (where_in IN VARCHAR2)
IS
dyncur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE (dyncur,
'SELECT ename FROM emp WHERE ' || NVL (where_in, '1=1'),
DBMS_SQL.NATIVE);
...
END;In this case, I have qualified my references to the OPEN_CURSOR, PARSE, and NATIVE elements of the DBMS_SQL package. The first two instances are programs (a function and a procedure). The third instance is a constant, passed as the third argument in my call to DBMS_SQL.PARSE.
Programs in built-in packages can raise exceptions. You will often want to write code to check for and handle these exceptions. You should know about the different ways that exceptions can be defined and raised by programs in the built-in packages. This will affect the way you write your exception handlers.
At the beginning of each package’s coverage, you will find a description of the exceptions defined within that package. Within the documentation of many of the programs within a package, you will also find an explanation of the specific exceptions that may be raised by those individual programs. When references are made to named exceptions in these explanations, they will appear in one of two forms:
PACKAGE.exception_name
or:
exception_name
If the exception name is unqualified (i.e., no package name appears before the exception name), then this exception is defined either:
In the package currently under discussion, or
In the STANDARD package; examples are VALUE_ERROR and NO_DATA_FOUND.
In this section, I will review the four types of exceptions you may encounter when working with built-in packages. I will then show you the kind of code you will need to write to handle exceptions properly when they propagate out from built-in packages. The following sections demonstrate how to write code to work with these different types of exceptions.
Table 1.2 summarizes these types.
Table 1-2. Types of Exceptions
|
Type |
How Exception Is Defined |
How Exception Is Raised |
How to Handle Exception | ||
|---|---|---|---|---|---|
|
The package gives a name to a specific Oracle error number using the PRAGMA EXCEPTION_INIT statement. |
The packaged program issues a RAISE statement. |
Returns the Oracle error number. |
Returns the standard Oracle error message text. |
You can handle it by number within a WHEN OTHERS clause, or by name with its own exception handler; the latter improves the readability of your code. | |
|
The package declares one or more exceptions; these exceptions have names, but no message text and no unique number. |
The packaged program RAISEs that exception by name. |
Returns 1. |
Returns “Unhandled user-defined exception” message. |
You can only handle it by name or with a WHEN OTHERS clause, in which case it is impossible to tell which exception was raised. | |
|
It is previously given a name in the STANDARD package or it is simply an error number. |
The packaged program issues a RAISE statement. |
Returns the Oracle error number. |
Returns the standard Oracle error message text. |
You can handle it by name in its own exception handler, if a name has been associated with that error number. Otherwise, you handle the exception by number in a WHEN OTHERS clause. | |
|
In this case, Oracle has rudely appropriated for itself one or more of the application-specific error numbers between -20,999 and -20,000 set aside for customers. |
The packaged program calls RAISE_APPLICATION_ERROR. |
Returns the number in the -20NNN range. |
Returns the message text provided in the call to RAISE_APPLICATION_ERROR. |
You can handle these exceptions by number within a WHEN OTHERS clause. |
In this scenario, the package gives a name to a specific Oracle error number using the PRAGMA EXCEPTION_INIT statement. You can then handle the exception by name with its own exception handler or by number within a WHEN OTHERS clause. Let’s look at an example.
The DBMS_DEFER package associates names with a number of Oracle errors. Here is an example of one such association:
updateconflict EXCEPTION; PRAGMA EXCEPTION_INIT (updateconflict, -23303);
If a program in DBMS_DEFER raises this exception, you can handle it in either of the following ways:
EXCEPTION
WHEN DBMS_DEFER.UPDATECONFLICT
THEN
/* SQLCODE returns -23303 and SQLERRM returns the standard
Oracle error message */or:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -23303
THEN
/* SQLERRM returns the standard Oracle error message */Note
You will not be able to write a statement like WHEN DBMS_DEFER.UPDATECONFLICT in the Oracle Developer/2000 Release 1 environment. See Section 1.3.6 for more information on this restriction.
In this scenario, the package declares one or more exceptions by name only; these exceptions do not have message text or a unique number associated with them. When this exception has been raised, SQLCODE will always return 1 and SQLERRM will always return the “Unhandled user-defined exception” message. As a consequence, you have two basic options for handling these exceptions:
Let’s look at the UTL_FILE package for an example. The following exceptions are defined in the package specification:
PACKAGE UTL_FILE IS invalid_path EXCEPTION; invalid_mode EXCEPTION; invalid_filehandle EXCEPTION; invalid_operation EXCEPTION; read_error EXCEPTION; write_error EXCEPTION; internal_error EXCEPTION; END;
The UTL_FILE.FOPEN function can raise the INVALID_MODE, INVALID_OPERATION, or INVALID_PATH exceptions. I can write an exception section for a program using UTL_FILE.FOPEN in one of two ways:
PROCEDURE my_program
IS
fid UTL_FILE.FILE_TYPE;
BEGIN
fid := UTL_FILE.FOPEN ('/tmp', 'myfile.txt', 'R');
...
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION
THEN
...
WHEN UTL_FILE.INVALID_MODE
THEN
...
WHEN UTL_FILE.INVALID_PATH
THEN
...
END;or:
PROCEDURE my_program
IS
fid UTL_FILE.FILE_TYPE;
BEGIN
fid := UTL_FILE.FOPEN ('/tmp', 'myfile.txt', 'R');
...
EXCEPTION
WHEN OTHERS /* Not recommended! Information is lost... */
THEN
...
END;When working with this kind of exception, always use the first approach. With the WHEN OTHERS clause, there is no way for you to know which of the three UTL_FILE exceptions was raised. SQLCODE returns the same value of 1 regardless of the specific exception raised.
Note
You will not be able to write a statement like WHEN UTL_FILE.INVALID_MODE in the Oracle Developer/2000 Release 1 environment. See Section 1.3.6 for more information on this restriction.
In this scenario, the package does not contain any statements that define new exceptions, nor does it give names to existing Oracle error numbers. Instead, a program in the package simply raises one of the errors defined in the Oracle documentation. You can then handle this exception by its name (if there is one) or by its number within a WHEN OTHERS clause. Let’s look at an example.
The UTL_FILE.GET LINE procedure raises the NO_DATA_FOUND exception (ORA-01403, but SQLCODE actually returns a value of 100) if you try to read past the end of a file. You can handle this error in either of the following ways:
EXCEPTION WHEN NO_DATA_FOUND THEN ...
or:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = 100
THEN
/* SQLERRM returns the standard Oracle error message */
...
END;Of course, if you need to handle an exception that does not have a name associated with it, you can only rely on the WHEN OTHERS clause and an IF statement with SQLCODE to handle that error specifically.
In some packages, Oracle developers decided to appropriate for their own use error numbers in the range set aside by Oracle Corporation for customer use (-20999 through -20000). This is very poor practice, as it can cause conflicts with your ownuse of these values. Unfortunately, it does happen and you need to know what to do about it.
For example, the DBMS_OUTPUT package uses the -20000 error number to communicate back to the calling program either one of these errors:
ORU-10027: buffer overflow, limit of <buf_limit> bytes. ORU-10028: line length overflow, limit of 255 bytes per line.
Here is a attempt to call DBMS_OUTPUT.PUT_LINE that raises an unhandled exception in a SQL*Plus session:
SQL> exec dbms_output.put_line (rpad ('abc', 300, 'def'))
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 99
ORA-06512: at "SYS.DBMS_OUTPUT", line 65
ORA-06512: at line 1I can handle this error if I call the built-in procedure from within a PL /SQL block as follows:
/* Filename on companion disk: myput.sp /*
CREATE OR REPLACE PROCEDURE myput (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (str);
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -20000
THEN
IF SQLERRM LIKE '%ORU-10027%'
THEN
DBMS_OUTPUT.ENABLE (1000000);
myput (str);
ELSIF SQLERRM LIKE '%ORU-10028%'
THEN
myput (SUBSTR (str, 1, 255));
myput (SUBSTR (str, 256));
END IF;
END IF;
END;
/The myput procedure implements the following logic: try to display the string. If an exception is raised, check to see if it is a -20000 error. If so, see if the error message indicates that it is a “buffer too small” error. If so, expand the buffer to the maximum size and try again to display the string. If the error message indicates a “string too long” error, display the first 255 bytes and then call myput again recursively to display the rest of the string.
One interesting situation you may run into when working with some of the built-in packages is that the same exception can be raised from different circumstances. Specifically, the NO_DATA_FOUND exception is raised by the PL /SQL runtime engine under any of these conditions:
You execute a SELECT INTO query (an implicit cursor) that does not identify any rows.
You attempt to access a row in a PL /SQL or index-by table that is not yet defined.
You try to read past the end of a file using UTL_FILE.GET_LINE.
You read past the end of a large object with DBMS_LOB.READ.
If you are writing code that could raise NO_DATA_FOUND for different reasons, you may not be able to get by with a single exception handler like this:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/* ?? What caused the problem? */
...
END;You will want to know in the exception handler whether the problem was that the query returned no rows, or you read past the end of the file, or you tried to access an undefined row in an index-by table, or something else. If you face this problem, you may want to use a technique I call exception aliasing . Consider the very short program below:
CREATE OR REPLACE PROCEDURE just_a_demo
(file IN UTL_FILE.FILE_TYPE, empno_in IN emp.empno%TYPE)
IS
line VARCHAR2(1000);
end_of_file EXCEPTION;
v_ename emp.ename%TYPE;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = empno_in;
BEGIN
UTL_FILE.GET_LINE (file, line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE end_of_file;
END;
EXCEPTION
WHEN end_of_file
THEN
DBMS_OUTPUT.PUT_LINE ('Read past end of file!');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('No employee found for ' || TO_CHAR (empno_in));
END:
/I have embedded the call to UTL_FILE.GET_LINE inside its own block. If that program reads past the end of a file and raises NO_DATA_FOUND, that block’s exception section “translates” NO_DATA_FOUND into another, distinct exception: end_of_file (declared in the procedure itself ). The exception section of the procedure as a whole can then distinguish between the two different NO_DATA_FOUND scenarios.
You will discover (both through reading this book and through your own experience) that there are many reasons to avoid directly accessing built-in packaged functionality. In a number of cases, you will want to build your own package on top of the built-in package. This process is usually referred to as encapsulation.
Why would you bother with an encapsulation package? Any of the following reasons will do:
The built-in packages offer lots of interesting technology, but they are not always very easy to use. You can hide the complexity, or in some cases, the poor design, and make it much easier for yourself and others to reap the benefits.
Some of the packages contain programs that you would not want to make generally or widely available. Conversely, other programs in that same package might be very useful for the “general public.” An encapsulation package can offer only those programs that should be available, while hiding the others. (In this case, you will want to revoke any EXECUTE privileges on the underlying package.)
Write less code. Have you ever noticed the really impressive lengths of the names of Oracle built-in packages and their programs? Studies conducted by the Institute for Study Conduction estimate that by the year 2000, developers using PL /SQL will have experienced a $150 trillion loss in productivity due to having to type names like DBMS_DESCRIBE.DESCRIBE_PROCEDURE. Your encapsulations can use shorter names and thereby erase the federal deficit.
Take full advantage of built-in packages from Oracle Developer/2000 Release 1. As you will read in the next section, there are many restrictions on accessing stored code from products like Oracle Forms. Encapsulation can help you work around these restrictions.
Roughly speaking, there are two types of encapsulation to consider when working with the built-in packages:
- Extension encapsulation
This is the most common type of encapsulation for built-in packages. In this case, you provide one or more programs that extend the functionality or usability of the underlying package.
- Covering encapsulation
When you create a cover for a built-in package, you create a package with a specification that matches that of the built-in package (same program names, same parameter lists). You can even give your package the same name as the built-in package, but you install it in a schema other than SYS.
When you revoke EXECUTE authority on the built-in package and grant EXECUTE authority on your package, users of the built-in package will automatically be directed to your replacement. Oracle recommends this technique, for example, with the DBMS_APPLICATION_INFO package.
This book (and the accompanying disk) contains many packages that encapsulate or cover an underlying built-in package (or, in some cases, a subset of the package). Table 1.3 shows the encapsulation packages in the book.
Table 1-3. Encapsulation Packages for Built-ins
If you use Oracle Developer/2000 Release 1 to build your client-side application, you can use the built-in packages, but you should be aware of the following restrictions:[3]
You can reference only packaged procedures and functions. You cannot, for example, make reference in your client-side code to DBMS_SQL.NATIVE (a constant) or UTL_FILE.INVALID_OPERATION (an exception).
You must supply a value for each argument in a packaged procedure or function. You cannot rely on default values in the headers of those programs. This is true even if those default values do not reference elements in the built-in packages (in other words, are literal values).
You must be connected to the Oracle database before you can compile program units that reference built-in packages. While it is possible to work on your client-side module without connecting to Oracle, the PL /SQL compiler cannot resolve references to server-side objects like built-in packages unless you are connected.
The following sections explore these restrictions in more detail and suggest work-arounds for making full use of the built-in packages from within products like Oracle Forms and Oracle Reports.
Consider the DBMS_SQL.PARSE procedure. Here is the header for this program:
PROCEDURE DBMS_SQL.PARSE (c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER);
The third argument, language_flag, can be any of the following values, as defined by constants in the DBMS_SQL specification:
DBMS_SQL.V6 DBMS_SQL.V7 DBMS_SQL.NATIVE
Now, if you try to execute this program in an Oracle Forms program unit, as in the following,
BEGIN DBMS_SQL.PARSE (cur, 'SELECT ...', DBMS_SQL.NATIVE); ...
you will receive this error:
Error 302: component NATIVE must be declared
Oracle Forms simply does not know how to interpret anything but procedures and functions in stored packages. So what’s a developer to do? You have several options:
Find out the literal value behind the named constant and use that, or
Create a stored function that encapsulates the constant and call that, or
Create a stored procedure that calls DBMS_SQL.PARSE and hides the use of the constant.
The first option would result in code like this:
BEGIN DBMS_SQL.PARSE (cur, 'SELECT ...', 1); ...
I suggest that you do not take this approach. You are always better off not proliferating the use of literals like this one in your code. They are hard to understand and leave you vulnerable to errors caused by changes in the way that DBMS_SQL behaves.
The second option (encapsulating the constant inside a function) is better. I could, for example, create a tiny package as follows:
/* Filename on companion disk: dynconst.spp */* CREATE OR REPLACE PACKAGE dynsql_value IS FUNCTION v6 RETURN INTEGER; FUNCTION v7 RETURN INTEGER; FUNCTION native RETURN INTEGER; END; / CREATE OR REPLACE PACKAGE BODY dynsql_value IS FUNCTION v6 RETURN INTEGER IS BEGIN RETURN DBMS_SQL.V6; END; FUNCTION v7 RETURN INTEGER IS BEGIN RETURN DBMS_SQL.V7; END; FUNCTION native RETURN INTEGER IS BEGIN RETURN DBMS_SQL.NATIVE; END; END; /
With this code in place on the server, I can then call DBMS_SQL.PARSE as follows:
BEGIN DBMS_SQL.PARSE (cur, 'SELECT ...', dynsql_value.native); ...
This code is almost identical to my first example, but I am calling a function rather than referencing a literal, and that makes all the difference.
The third option, encapsulating the call to DBMS_SQL.PARSE, is perhaps the optimal solution. Why should you even have to bother passing the database mode? You might as well just always make it “native.” Here is some code that hides this argument entirely:
*Filename on companion disk: dynconst.spp */ CREATE OR REPLACE PACKAGE dynsql_value IS PROCEDURE parse (cur IN INTEGER, sql_str IN VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY dynsql_value IS PROCEDURE parse (cur IN INTEGER, sql_str IN VARCHAR2) IS BEGIN DBMS_SQL.PARSE (cur, sql_str, DBMS_SQL.NATIVE) END; END; /
Now I can parse a SQL statement from within Oracle Forms as follows:
BEGIN dynsql_value.parse (cur, 'SELECT ...'); ...
I recommend this last technique, because you will inevitably find other workaround needs having to do with DBMS_SQL or another built-in package. Why not collect them all together in a single encapsulator package? This point is driven home in the next section.
An earlier section in this chapter (“Exception Handling and Built-in Packages”) explored the different types of exceptions that can be raised from within built-in packages. One type in particular, the package-specific exception, presents a challenge to Oracle Developer/2000 programmers.
Consider once again the UTL_FILE package. It declares a number of exceptions and, as noted previously, the only way to handle those exceptions (and know which exception was raised) is to create an explicit exception handler, as in:
EXCEPTION
WHEN UTL_FILE.INVALID_MODE
THEN
...
END;Unfortunately, you cannot write this kind of code from Oracle Forms. It cannot resolve the reference to UTL_FILE.INVALID_MODE. What can you do? If you are going to make extensive use of UTL_FILE from Oracle Forms (or Oracle Reports), and you want to build in some robust error handling, you should probably consider building a wrapper package around UTL_FILE.
Instead of calling UTL_FILE.FOPEN directly, for example, and risk raising an exception you cannot interpret accurately, you might want to consider something like this:
/* Filename on companion disk: myfile.spp */ CREATE OR REPLACE PACKAGE myfile IS /* Document in the package specification that: - INVALID_MODE is returned as -20100. - INVALID_PATH is returned as -20101. - INVALID_OPERATION is returned as -20102. */ PROCEDURE fopen (loc IN VARCHAR2, file IN VARCHAR2, fmode IN VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY myfile IS g_file UTL_FILE.FILE_TYPE; PROCEDURE fopen (loc IN VARCHAR2, file IN VARCHAR2, fmode IN VARCHAR2) IS BEGIN g_file := UTL_FILE.FOPEN (loc, file, fmode); EXCEPTION WHEN UTL_FILE.INVALID_MODE THEN RAISE_APPLICATION_ERROR (-20100, 'Invalid mode ' || fmode); WHEN UTL_FILE.INVALID_PATH THEN RAISE_APPLICATION_ERROR (-20101, 'Invalid path ' || loc); WHEN UTL_FILE.INVALID_MODE THEN RAISE_APPLICATION_ERROR (-20102, 'Invalid operation'); END; END; /
I accomplish two things with this prototypes package:
I translate the package-specific exceptions to -20NNN exceptions. Therefore, my UTL_FILE exception now has a number. I can check for that number within my client-side application and take appropriate action.
I hide the UTL_FILE.FILE_TYPE record. From Oracle Forms, I cannot even declare a record of this type (it is not a program, so UTL_FILE.FILE_TYPE cannot be referenced from within Oracle Developer/2000 Release 1).
With this wrapper approach, you can build a package that allows you to read and write a particular server-side file from Oracle Forms. You would still need to build read, write, and close procedures, but the technique should be clear.
Throughout this book, you will find documentation indicating whether a particular packaged function can be called from within an SQL statement, or whether a packaged procedure can be called by a function that, in turn, is called from within SQL. This section explains the significance of that capability.
If you are running a version of Oracle Server 7.1 and beyond, you can call PL /SQL functions from within SQL statements. (If you are not running at least Oracle Server 7.1, you can skip this section—but you should also certainly upgrade your database software as soon as possible!) Let’s take a look at an example to give you a feel for this capability.
Suppose that my formula for calculating total compensation for an employee is “salary plus commission.” Here is that formula implemented in PL /SQL:
CREATE OR REPLACE FUNCTION totcomp (sal_in IN NUMBER, comm_in IN NUMBER) RETURN NUMBER IS BEGIN RETURN sal_in + NVL (comm_in, 0); END; /
Once this program is stored in the database, I can call it from within a query as follows:
SQL> SELECT ename, totcomp (sal, comm) total_compensation FROM emp; ENAME TOTAL_COMPENSATION ---------- ------------------ SMITH 800 ... MILLER 1300
You can also call a packaged function from within a SQL statement. In this case, however, you must also provide a special statement, the RESTRICT_REFERENCES pragma, to enable that function for use inside SQL. Here, for example, is the code you would have to write to place totcomp inside a package and still call it from a query:
CREATE OR REPLACE PACKAGE empcomp
IS
FUNCTION totcomp
(sal_in IN NUMBER, comm_in IN NUMBER)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (total, WNDS, RNDS, WNPS, RNPS);
END;
/
CREATE OR REPLACE PACKAGE BODY empcomp
IS
FUNCTION totcomp
(sal_in IN NUMBER, comm_in IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (sal_in + NVL (comm_in, 0));
END;
END;
/The line in bold is the statement asserting that the empcomp.total function does not violate any of the restrictions on functions in SQL. Here is how you would call this packaged function inside SQL:
SQL> SELECT ename, empcomp.total (sal, comm) total_comp from emp; ENAME TOTAL_COMP ---------- ---------- SMITH 800 ... MILLER 1300
The same rules apply for built-in packaged programs callable from SQL. Oracle Corporation itself must provide a RESTRICT_REFERENCES pragma in its own package specifications for any procedure or function that is to be used from within SQL. And since Oracle did not pragmatize built-in packages prior to Oracle 7.3.3, you will not be able to call built-in packaged programs from SQL (directly or indirectly) until you install Oracle 7.3.4 or later.
If you try to call a packaged function in SQL that does not have such a pragma, you will receive this error:
SQL> SELECT utl_file.fopen ('/tmp', ename || '.dat', 'R')
2 FROM employee;
select utl_file.fopen ('a', 'b', 'r') from employee
*
ERROR at line 1:
ORA-06571: Function FOPEN does not guarantee not to update databaseDon’t you hate those double negatives?
You will also encounter this same error if you try to execute a function in SQL that, in turn, calls a packaged procedure that does not have a pragma. For example, the DBMS_JOB.SUBMIT procedure is not “pragma-tized” for use in SQL. Consequently, the following function (exactly the same as that shown earlier, except for the addition of the procedure call) will not be executable within SQL:
CREATE OR REPLACE FUNCTION totcomp (sal_in IN NUMBER, comm_in IN NUMBER) RETURN NUMBER IS myjob INTEGER; BEGIN DBMS_JOB.SUBMIT (myjob, 'calc_totals;'); RETURN (sal_in + NVL (comm_in, 0)); END; /
Here is the error I get when I try to execute my new and “improved” function:
SQL> SELECT totcomp (salary, NULL) FROM employee;
SELECT totcomp (salary, NULL) FROM employee
*
ERROR at line 1:
ORA-06571: Function TOTCOMP does not guarantee not to update databaseIf you want to use a packaged function in a SQL statement, it must have a RESTRICT_REFERENCES pragma. If that is the case, you are all set! Just call the function as you would call a built-in function such as SUBSTR or TO_CHAR.
Suppose that I am working on the large objects stored in files. The DBMS_LOB package includes several RESTRICT_REFERENCES pragmas. Here is the pragma for the GETLENGTH function:
PRAGMA RESTRICT_REFERENCES (getlength, WNDS, RNDS, WNPS, RNPS);
Here are the meanings for each of those purity levels :
- WNDS
Writes No Database State. In other words, does not make any changes to database structures by calling an INSERT, UPDATE, or DELETE.
- WNPS
Writes No Package State. In other words, does not change the values of any package data structures.
- RNDS
Read No Database State. In other words, does not SELECT from any database tables or other database objects.
- RNPS
Reads No Package State. In other words, does not reference any package data structures.
The absolute minimum purity level required to allow a program to be used (directly or indirectly) inside SQL is WNDS. You can never update the database. In some situations, such as when you want to call a function from within a WHERE clause, the program will also need to have asserted the WNPS purity level.
Since DBMS_LOB.GETLENGTH asserts all four purity levels, I can use it in SQL, both in the SELECT list of a query and even in the WHERE clause. Here is an example; in it, I display the length of each photograph stored in the archives for my family:
SELECT DBMS_LOB.GETLENGTH (portrait_lob_loc) FROM photo_archive WHERE family = 'FEUERSTEIN' AND DBMS_LOB.GETLENGTH (portrait_lob_loc) < 1000;
Table 1-4 provides a complete list of all packaged programs that can be called (directly or indirectly) from within a SQL statement, the purity levels for each, and the Oracle versions in which these purity levels become available (thus enabling you to call the programs from within SQL). The rest of this section explains how to use packaged functions and procedures, and the meaning of the various purity levels.
Table 1-4. Purity Levels for Oracle Built-in Package Programs
|
Package |
Program |
WNDS |
WNPS |
RNDS |
RNPS |
|---|---|---|---|---|---|
|
DBMS_LOB |
COMPAREa |
X |
X |
X |
X |
|
FILEEXISTSa |
X |
X |
X |
X | |
|
FILEISOPEN |
X |
X |
X |
X | |
|
GETLENGTH |
X |
X |
X |
X | |
|
INSTR |
X |
X |
X |
X | |
|
SUBSTR |
X |
X |
X |
X | |
|
DBMS_OUTPUT |
DISABLEb |
X |
X | ||
|
ENABLEb |
X |
X | |||
|
GET_LINEb |
X |
X | |||
|
GET_LINESb |
X |
X | |||
|
NEW_LINEb |
X |
X | |||
|
PUTb |
X |
X | |||
|
PUT_LINEb |
X |
X | |||
|
DBMS_PIPE |
CREATE_PIPEb |
X |
X | ||
|
NEXT_ITEM_TYPEb |
X |
X | |||
|
PACK_MESSAGEb |
X |
X | |||
|
PACK_MESSAGE_RAWb |
X |
X | |||
|
PACK_MESSAGE_ROWIDb |
X |
X | |||
|
PURGEb |
X |
X | |||
|
RECEIVE_MESSAGEb |
X |
X | |||
|
REMOVE_PIPEb |
X |
X | |||
|
RESET_BUFFERb |
X |
X | |||
|
SEND_MESSAGEb |
X |
X | |||
|
UNIQUE_SESSION_NAMEb |
X |
X |
X | ||
|
UNPACK_MESSAGEb |
X |
X | |||
|
UNPACK_MESSAGE_RAWb |
X |
X | |||
|
UNPACK_MESSAGE_ROWIDb |
X |
X | |||
|
DBMS_ROWID |
ROWID_BLOCK_NUMBERa |
X |
X |
X |
X |
|
ROWID_CREATEa |
X |
X |
X |
X | |
|
ROWID_INFOa |
X |
X |
X |
X | |
|
ROWID_OBJECTa |
X |
X |
X |
X | |
|
ROWID_RELATIVE_FNOa |
X |
X |
X | ||
|
ROWID_ROW_NUMBERa |
X |
X |
X |
X | |
|
ROWID_TO_ABSOLUTEa |
X |
X |
X |
X | |
|
ROWID_TO_EXTENDEDa |
X |
X |
X | ||
|
ROWID_TO_RESTRICTEDa |
X |
X |
X |
X | |
|
ROWID_TYPEa |
X |
X |
X |
X | |
|
ROWID_VERIFYa |
X |
X |
X | ||
|
DBMS_SESSION |
UNIQUE_SESSION_IDb |
X |
X |
X | |
|
DBMS_STANDARD |
DELETINGb |
X |
X |
X | |
|
INSERTINGb |
X |
X |
X | ||
|
RAISE_APPLICATION_ERRORb |
X |
X |
X |
X | |
|
UPDATINGb |
X |
X |
X | ||
|
DBMS_UTILITY |
DATA_BLOCK_ADDRESS_BLOCKb |
X |
X |
X |
X |
|
DATA_BLOCK_ADDRESS_FILEb |
X |
X |
X |
X | |
|
GET_HASH_VALUEb |
X |
X |
X |
X | |
|
MAKE_DATA_BLOCK_ADDRESSb |
X |
X |
X |
X | |
|
PORT_STRINGb |
X |
X |
X |
X | |
|
UTL_RAW |
BIT_ANDb |
x |
x |
x |
x |
|
BIT_COMPLEMENTb |
X |
X |
X |
X | |
|
BIT_ORb |
X |
X |
X |
X | |
|
BIT_XORb |
X |
X |
X |
X | |
|
CAST_TO_RAWb |
X |
X |
X |
X | |
|
COMPAREb |
X |
X |
X |
X | |
|
CONCATb |
X |
X |
X |
X | |
|
CONVERTb |
X |
X |
X |
X | |
|
COPIESb |
X |
X |
X |
X | |
|
LENGTHb |
X |
X |
X |
X | |
|
OVERLAYb |
X |
X |
X |
X | |
|
REVERSEb |
X |
X |
X |
X | |
|
SUBSTRb |
X |
X |
X |
X | |
|
TRANSLATEb |
X |
X |
X |
X | |
|
TRANSLITERATEb |
X |
X |
X |
X | |
|
XRANGEb |
X |
a Indicates availability in Oracle8 and above only.
b Indicates availability in Oracle7.3 and above only.
You cannot call a PL /SQL procedure directly inside an SQL statement. Instead, you would call that procedure from within a function that is called in SQL (or within another program that is, in turn, called by that function, and so on). That function will not work within SQL unless the procedure it calls has a RESTRICT_REFERENCES pragma.
You will most likely run into this situation when you want to add some trace capabilities to your SQL statement. Suppose that I want to write a general trace function that I can add to any SELECT statement to obtain information about the rows’ queries. Here is one possible implementation:
CREATE OR REPLACE FUNCTION sql_trace (str IN VARCHAR2) RETURN NUMBER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Display from SQL: ' || str);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN RETURN SQLCODE;
END;
/Now I will use this function inside SQL:
SQL> SELECT last_name, sql_trace (first_name) trc 2 FROM employee 3 WHERE department_id = 20;
And here are the results:
LAST_NAME TRC --------------- ---------- SMITH 0 JONES 0 SCOTT 0 ADAMS 0 FORD 0
Wait a minute! Where’s the trace output from the function? It turns out that you must call DBMS_OUTPUT.ENABLE to flush out the current contents of the buffer (a “standalone” call to DBMS_OUTPUT.PUT_LINE will also do the trick). Here we go:
SQL> exec dbms_output.enable Display from SQL: JOHN Display from SQL: TERRY Display from SQL: DONALD Display from SQL: DIANE Display from SQL: JENNIFER
Get Oracle Built-in Packages now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
