Chapter 21 - External Procedures
| Chapter 21 |
21. External Procedures
Contents:
Introduction to External Procedures
Steps in Creating an External Procedure
Syntax for External Procedures
Mapping Parameters
OCI Service Routines
External Procedure Housekeeping
Examples
I've lost count of how many times I've heard the question "Can I call whatever from within Oracle?" Typically, whatever is a program that interacts with the external environment: sending email, polling or controlling hardware, or invoking the C library functions that PL/SQL lacks. Until very recently, the standard answer was "No, you can't do that directly, but you can use a database pipe and write a daemon that responds to requests on the pipe." An early paper on this approach[1] describes pipes as an alternative to an even more primitive approach: using a temporary table as a sort of "bulletin board" for interprocess communication.[2]
[1] See Dan Bikle, "Inter-Application Communication Using Oracle7 Database Pipes," Select Vol. 1, No. 2, Winter 93/94, p. 34.
[2] The original paper did not present a true UNIX daemon (which has a specific definition to UNIX and C programmers), but rather discussed the generic idea of a continuously running process.
Temporary tables have serious limitations for this use, and even database pipe-based daemons have their shortcomings. Packing and unpacking the contents of the pipe is a challenge; the daemon typically execute in a separate Oracle session (and thus can't participate in a transaction), and the solution is inherently single-threaded. Moreover, with the pipe solution, it's difficult to get return values back from the daemon to the caller. What we need from Oracle is a fast, reliable way of calling out to operating system commands or functions from within PL/SQL. Better yet, Oracle should allow external routines to serve as user-defined functions so that you can use them in SQL statements.
Enter external procedures. This long-awaited Oracle feature allows you to call anything that you can compile into the native "shared library" format of the operating system. Yes, the external procedures features is reliable; yes, it's multi-threaded; yes, communication is bidirectional; and yes, you can use external procedures as user-defined functions in SQL.
Under UNIX, a shared library is a shared object or .so file; under Windows NT, it's a DLL (dynamically linked library). You can write the external routine in any language you wish, as long as your compiler and linker will generate the appropriate shared library format that is callable from C. In Oracle 8.0, however, C will be the most common language for external procedures, since all of Oracle's support libraries are written in C. Curiously, Oracle has named this feature external "procedures," although the external routines you invoke are, technically speaking, C functions. (There is no such thing as a procedure in C.) If the C function returns a value, you map it to a PL/SQL function; if it returns no value, you map it to a PL/SQL procedure.
This chapter presents a few examples of external procedures (and functions). In addition, we review the preconditions you must establish before they will work, and present the syntax for creating and using this new feature. So, the next time you hear the question about calling whatever, you can answer, "You bet!...in Oracle8."
This chapter does not discuss "distributed external procedures," which are a way of accessing non-Oracle data sources from an Oracle server. Despite the name, these procedures are not closely related to external procedures. Neither do we discuss at length the programming techniques that allow your 3GL code to call back to Oracle (we'll leave that to the books on programming C language access to Oracle). But we do include some samples that you can use right away.
21.1 Introduction to External Procedures
How do external procedures work? How can I build my own? What are their advantages and disadvantages? Before answering in detail, let's take a look at a quick example.
21.1.1 Example: Determining Free Disk Space on Windows NT
Here is an external procedure that will discover the amount of free space on a given disk drive. This example is just to get you going. We won't try to explain all the details at this point. This example was designed for Windows NT 4.0, but the idea can be applied to any operating system that meets the requirements for external procedures. In this case, we simply make a call to the appropriate function in the Windows kernel, rather than writing our own DLL.
Windows NT's kernel, kernel32.dll, contains a routine called GetDiskFreeSpaceA, which accepts a drive letter as an input parameter and returns four statistics about the drive. When we register the routine with PL/SQL, we will provide mappings for each of these parameters to a PL/SQL parameter. Then, when we invoke the external procedure from a PL/SQL program, we'll use these statistics to compute the free disk space.
First, we need to define a "library" to tell Oracle where the DLL lives:
/* Filename on companion disk: nt_space.sql */ CREATE OR REPLACE LIBRARY nt_kernel AS 'c:\winnt\system32\kernel32.dll';
We'll create a package called disk_util that will contain our function, which we will call get_disk_free_space as shown here:
CREATE OR REPLACE PACKAGE disk_util AS FUNCTION get_disk_free_space (root_path IN VARCHAR2, sectors_per_cluster OUT PLS_INTEGER, bytes_per_sector OUT PLS_INTEGER, number_of_free_clusters OUT PLS_INTEGER, total_number_of_clusters OUT PLS_INTEGER) RETURN PLS_INTEGER; PRAGMA RESTRICT_REFERENCES (get_disk_free_space, WNPS, RNPS, WNDS, RNDS); END disk_util;
All the magic is in the package body, which uses the EXTERNAL clause rather than a BEGIN..END block. This clause is where we define the interface between PL/SQL and the external routine:
CREATE OR REPLACE PACKAGE BODY disk_util AS FUNCTION get_disk_free_space (root_path IN VARCHAR2, sectors_per_cluster OUT PLS_INTEGER, bytes_per_sector OUT PLS_INTEGER, number_of_free_clusters OUT pls_integer, total_number_of_clusters OUT PLS_INTEGER) RETURN PLS_INTEGER IS EXTERNAL LIBRARY nt_kernel -- our library (defined previously) NAME "GetDiskFreeSpaceA" -- name of function in kernel32.dll LANGUAGE C -- external routine is written in C CALLING STANDARD PASCAL -- uses Pascal parameter convention PARAMETERS -- map PL/SQL to C parameters by -- position (root_path STRING, sectors_per_cluster BY REFERENCE LONG, bytes_per_sector BY REFERENCE LONG, number_of_free_clusters BY REFERENCE LONG, total_number_of_clusters BY REFERENCE LONG, RETURN LONG); -- "return code" indicating success or failure END disk_util;
Assuming that the DBA has set up the environment to support external procedures (see Section 21.2.1, "Step 1: Set Up the Listener" later in this chapter), we can make an easy call to compute free disk space on the C: drive:
SET SERVEROUTPUT ON SIZE 100000 DECLARE lroot_path VARCHAR2(3) := 'C:\'; -- look at C drive lsectors_per_cluster PLS_INTEGER; lbytes_per_sector PLS_INTEGER; lnumber_of_free_clusters PLS_INTEGER; ltotal_number_of_clusters PLS_INTEGER; return_code PLS_INTEGER; free_meg REAL; BEGIN /* Call the external procedure. We ignore the return code || in this simple example. */ return_code := disk_util.get_disk_free_space (lroot_path, lsectors_per_cluster, lbytes_per_sector, lnumber_of_free_clusters, ltotal_number_of_clusters); /* Using the drive statistics that are returned from the || external procedure, compute the amount of free disk space. || Remember Megabytes = (Bytes / 1024 / 1024) */ free_meg := lsectors_per_cluster * lbytes_per_sector * lnumber_of_free_clusters / 1024 / 1024; DBMS_OUTPUT.PUT_LINE('free disk space, megabytes = ' || free_meg); END;On my machine, this fragment produces the following output:
free disk space, megabytes = 214.53515625
Of course, you could put this computation in a named function or procedure, and even make it part of the disk_util package.
Figure 21.1: Invoking an external procedure
