There are several steps involved in using a built-in package:
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:
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.
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:
If the exception name is unqualified (i.e., no package name appears before the exception name), then this exception is defined either:
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.
188.8.131.52 Package-named system exception
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 */
EXCEPTION WHEN OTHERS THEN IF SQLCODE = -23303 THEN /* SQLERRM returns the standard Oracle error message */
184.108.40.206 Package-defined exception
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;
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.
220.127.116.11 Standard system exception
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 ...
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.
18.104.22.168 Package-specific exception
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 1
I 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:
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:
Roughly speaking, there are two types of encapsulation to consider when working with the built-in packages:
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.
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 ]
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.
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:
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 prototype package:
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 database
Don'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 database
If 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);
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.
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
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.