Chapter 27. External Procedures

Beginner

Q:

27-1.

An external procedure is an operating system file or program that can be called from within Oracle.

Q:

27-2.

External procedures can be called anywhere a native PL/SQL procedure or function is called. The external procedure has a “wrapper” PL/SQL unit around it that calls the proper OS code. It can be used in situations where the programmer has implemented a program in a language other than PL/SQL but wishes to call it from within PL/SQL.

Q:

27-3.

The statements are:

  1. False. Interprocess communication using external procedures requires much less complexity than using database pipes. With pipes, the programmer must set up a listener process that picks the messages off the pipe and acts on them. With external procedures, the program is executed directly on behalf of the user.

  2. False. With external procedures, communication is bidirectional. The programmer can pass information to an external procedure, and the external program can pass information back.

  3. True. With the appropriate pragmas (assuming that the PL/SQL wrapper is inside a package), calls to external procedures can be used anywhere PL/SQL stored functions can be used—for example, in SQL statements, in assignments, etc.

  4. True. The most common and well-supported language for external procedures is C (mostly because this is what Oracle is written in). The code for an external procedure can be written in any language that can be compiled into a C-callable format.

  5. False. External procedures do not have to return a value but are definitely permitted to do so. If the external program returns a value, this should be mapped to a PL/SQL function, whereas a program that does not return a value should be mapped to a PL/SQL procedure.

  6. False. In addition to the library itself and the PL/SQL wrapper, the DBA must also have set up an external procedure listener.

  7. False. The Oracle server starts one extproc process for each session; they are not shared between users. For example, if user A calls an external procedure, an extproc process is started for that user. All subsequent calls to an external process use this extproc process. However, user B, connected at the same time, has her own extproc process.

  8. False. Oracle can use only modules compiled in a shared library format as external procedures. Regular executable files cannot be used. If it’s necessary to run a regular file, a shared library can be created that simply calls this file with the appropriate parameters.

  9. True. The CREATE LIBRARY command provides access to the library itself and not to a particular module within that library. Therefore, the one library can access any routines inside that external library.

  10. False. Oracle will raise an error if a CREATE LIBRARY statement references an external library that doesn’t exist

Q:

27-4.

Each external procedure to be called must have a PL/SQL program unit that provides the information needed to call the program from within Oracle. For example, if there is an external program called generate_key that resides in the library called UTIL_LIB, you should declare a PL/SQL “wrapper” function as follows:

CREATE OR REPLACE FUNCTION get_new_key
RETURN PLS_INTEGER IS EXTERNAL
LIBRARY UTIL_LIB
NAME "generate_key"
LANGUAGE C;

This example assumes that the library UTIL_LIB had been previously created using statements similar to the following:

CREATE LIBRARY UTIL_LIB AS '/usr/local/lib/cryptolib.sl';

Q:

27-5.

In a Windows environment, shared libraries are generally identified by a .DLL (dynamic link library) extension. In Unix environments, the libraries usually have the extension .so (shared object) or .sl (shared library). These extensions are not actually what’s important, however, and any file compiled in the correct format can be used, regardless of extension.

Q:

27-6.

A user who wishes to create a library must have been granted the CREATE LIBRARY or CREATE ANY LIBRARY privilege by the DBA. If the appropriate privileges have not been granted, the user receives the error message:

ORA-01031: insufficient privileges

Q:

27-7.

Yes, external procedures do take part in the current database transaction. This means that:

  • If any uncommitted change is made in the session before calling the external procedure, it is visible.

  • Any changes made in the external procedure are visible to the rest of the session.

  • Changes can be rolled back.

Q:

27-8.

In versions of Oracle before 8.0, the only way of establishing communications between Oracle and the operating system was to have a stored procedure send messages using a database pipe and to implement a second “listener” program written in a language supported by the operating system and Oracle (e.g., Pro*C, Pro*COBOL, Pro*PL/I). This listener program then took the messages off the pipe and processed them, calling the appropriate OS program.

This approach had several disadvantages:

  • It was labor-intensive, since it required the programmer to write both a message server and a listener process.

  • It required skills with multiple computer language and precompiler.

  • The code had to handle concurrency if there was more than one process taking messages off the pipe.

Q:

27-9.

These data dictionary views display the following information:

USER_LIBRARIES

All libraries owned by the current user

ALL_LIBRARIES

All libraries available to the current user

DBA_LIBRARIES

All libraries defined in the whole database; only DBAs (or users with the DBA role) can use this view

Q:

27-10.

Only operating system programs that can be compiled into a shared library can be called using the external procedures option. In a Unix environment, this is a shared object (.sl or .so file). In a Windows environment, this means a .DLL file. The important thing is that the format of the object be callable from C.

Q:

27-11.

Deciding whether an external program should be mapped to a function or procedure is no different from using native PL/SQL. If the external process returns a value (number, string, Boolean), it should be mapped to a PL/SQL function. Conversely, if the external process does not return a value, it should be mapped to a PL/SQL procedure.

Q:

27-12.

When an external procedure is called, the order of events is as follows:

A PL/SQL application calls a special PL/SQL module body.
PL/SQL looks for a special listener process that is running in the background.
The listener process spawns a program called extproc that loads the external library.
The code in the library returns the result back to PL/SQL.

Q:

27-13.

The proper syntax to create the library is as follows:

CREATE LIBRARY UTIL_LIB AS 'C:MYAPPUTILAPPUTIL.DLL'

The PL/SQL wrapper looks like this:

CREATE FUNCTION GET_PASSWORD
RETURN STRING IS EXTERNAL
LIBRARY UTIL_LIB
LANGUAGE C;

Q:

27-14.

The syntax that creates the PL/SQL wrapper must be changed to include the PARAMETERS clause to specify the new parameter. The revised PL/SQL wrapper looks like this:

CREATE FUNCTION GET_PASSWORD
RETURN STRING IS EXTERNAL
LIBRARY UTIL_LIB
PARAMETERS (password_length INT)
LANGUAGE C;

There is no change required to the library since it establishes only the link to the shared library and does not contain any details about each routine inside the shared library.

Q:

27-15.

With the declaration of the PL/SQL wrapper, there is an optional NAME clause. If it is not specified, Oracle assumes that the name of the routine in the external shared library is the same as the PL/SQL wrapper. If the name of the routine is different, in either case (upper or lower) or name, you can use the NAME clause to specify this name. For example, you might specify the following declaration for the function mentioned, if the actual name of the routine were Collect_Data:

CREATE FUNCTION COLLECT_DATA
RETURN PLS_INTEGER IS EXTERNAL
LIBRARY UTIL_LIB
NAME "Collect_Data"
LANGUAGE C;

Q:

27-16.

The proper syntax to create a library is as follows:

CREATE LIBRARY library name AS 'path to library'

Note that some operating systems (such as Unix) are case-sensitive, and thus the following statement is not equivalent to the previous one:

CREATE LIBRARY MY_LIBRARY AS '/USR/LOCAL/MYLIB.SL'

However, on an operating system such as Windows NT which is case-insensitive, the following commands:

CREATE LIBRARY MY_LIBRARY AS 'c:winntsystem32kernal32.dll'

CREATE LIBRARY MY_LIBRARY AS 'C:wiNntSysTeM32KErnAl32.dLL'

are functionally identical.

The statements shown in the problems are:

  1. This one is valid:

    CREATE LIBRARY libc_l AS '/lib/libc.so'

  2. It is possible, but unlikely, that the following example is also valid:

    CREATE LIBRARY libc_l AS '/LIB/LIBC.SO'

    Since it appears that the operating system is Unix, it is unlikely that directories or files are named in uppercase (remember that Unix is case-sensitive).

  3. The statement:

    CREATE LIBRARY UNIX_operating_system_c_library AS '/lib/libc.so'

    fails because library names are subject to the usual rules for identifier names. In other words, the library name must be 30 characters or less; it must begin with a letter; and it cannot contain certain special symbols. The library name given in the example exceeds the maximum length of an identifier and fails with the error message:

    ORA-00972: identifier is too long

  4. This statement appears to work, but does, in fact, create an invalid library:

    CREATE LIBRARY libc_l FOR '/lib/libc.so'

    This can be verified by examining the USER_LIBRARIES view:

    SQL> SELECT * FROM user_libraries;

LIBRARY_NAME         FILE_SPEC                      D STATUS
-------------------- ------------------------------ - -------
LIBC_L                                                INVALID

    A successfully created library has the FILE_SPEC column populated, and the status is VALID.

  5. The following statement also fails for the reason described in (d):

    CREATE LIBRARY libc_l AS /lib/libc.so

Q:

27-17.

A library can be removed from the system by using the DROP LIBRARY command. Of course, you must have been granted the DROP LIBRARY or DROP ANY LIBRARY privilege by your DBA.

Q:

27-18.

Access to the functions can be removed from Sue with no impact on other users. The most efficient way to do this would be to issue the command:

REVOKE EXECUTE ON util_pkg FROM sue;

Remember that you do not need to grant users access to the library. You simply grant EXECUTE privilege on the code that references the library. In this way, it is not an “all or nothing” approach, and access can be controlled at the routine level instead of at the library level.

Q:

27-19.

When using external procedures, you must map the datatype that is being returned to the appropriate datatype in PL/SQL as shown in this table.

External Datatype

PL/SQL Datatype

int

BINARY_INTEGER, PLS_INTEGER

char * (string)

CHAR, VARCHAR2, VARCHAR (i.e., any character datatype)

double

DOUBLE PRECISION

float

FLOAT, REAL

For example, in the case of an external procedure expecting a string, passing a number generates the following number:

ORA-28577: argument 1 of external procedure test_proc has unsupported
           datatype OCINumber

Intermediate

Q:

27-20.

The following table summarizes the similarities and differences between native PL/SQL procedures and external procedures.

Native PL/SQL Procedures

External Procedures

Support for all Oracle built-in and user-defined datatypes

Support for only a subset of Oracle built-in datatypes

Source code stored in database

Compiles source code into a shared library on the native operating system

Functions can be used in SQL statements

Functions in an external library cannot be used in an SQL statement

Listener isn’t required

Requires a listener process to interact with the database

Q:

27-21.

With the advent of external procedures comes the ability to call programs that can do things not currently supported by the PL/SQL language. Even with the large strides made in the Oracle8i release, there are operations that still require operating system-level programming (e.g., deleting an OS file). Programmers are no longer limited to performing tasks in PL/SQL that are directly supported. They can now write code in any Oracle-supported language and then call this code using the external procedure method.

Q:

27-22.

To use an external procedure, you must follow these steps:

  1. The DBA must have configured the listener to run the external program on the caller’s behalf. Details of how this listener should be created, and other configuration file changes, are available in Oracle documentation (it involves changes to both the TNSNAMES.ORA and LISTENER.ORA files).

  2. Create a shared library (.so or .DLL file) that is callable from C or make sure one is available on your operating system.

  3. Execute a CREATE LIBRARY statement to define an alias in the data dictionary for the external shared library file. This allows the database engine to find it when it is called.

  4. Create a PL/SQL program unit that references the function or procedure contained in the newly created library.

Q:

27-23.

The current implementation of external procedures has the following disadvantages:

  • It is single-threaded (i.e., each session currently requires its own external procedure process).

  • Only scalar datatypes are supported currently; user-defined datatypes are not supported.

Q:

27-24.

This error occurs when Oracle is unable to find the external procedure listener. Without this listener, Oracle cannot communicate with the OS library or shared object that contains the code for the external procedure. This is quite common if the DBA forgets to restart the external procedure listener after a system reboot (only the database listener is restarted by default).

Q:

27-25.

The BY REFERENCE option in the PARAMETERS clause passes the parameter by reference (i.e., a pointer is passed to the module, and the result is changed directly by the external procedure). However, this option affects only numeric datatypes, since the other datatype (STRING) is always passed by reference.

Q:

27-26.

Since Oracle uses dynamic linking, once a particular module has been loaded into memory (when the first reference to it has been made), subsequent invocations of it do not cause any further overhead. In other words, all users share the same copy of the external library code in memory. This makes the memory requirements much lower in a busy system where many users quite often call the same code. By collecting commonly used modules into their own libraries, the system load is decreased and things become more manageable.

Q:

27-27.

The PL/SQL wrapper for an external procedure can be created as a stand-alone function, a standalone procedure, or a function/procedure within a package. While any of these methods works, there are several advantages to placing the wrapper inside a package:

  • The details of whether or not the code is implemented as an external procedure is hidden from the caller (this may be desired to obtain a certain level of abstraction).

  • By placing the wrapper in a package, a RESTRICT_REFERENCES pragma can be specified to allow a function to be used in SQL.

  • Similar external procedures can be bundled together into logical groupings.

Q:

27-28.

Dropping a library can be a potentially dangerous operation if you are not sure whether any stored modules are utilizing it. The easiest way to check if the library is referenced anywhere is by querying the USER_DEPENDENCIES view as shown here:

SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'MYLIB_NAME'

Q:

27-29.

Logging messages to a file is often desired because of the limitations with Oracle’s DBMS_OUTPUT package (e.g., output is unavailable until the program completes and is limited to 255 characters per line).

The steps in the process are listed here:

  1. Ensure that the DBA for your site has properly configured an external procedure listener to handle the calls you make to OS libraries. This requires a combination of entries in both the TNSNAMES.ORA and LISTENER.ORA files.

  2. Create a shared library to call the appropriate OS command to log the message. For this example, assume that the underlying operating system is Unix, and that the following code has been compiled into a shared library in the directory /usr/local/bin:

    #include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <signal.h>

void log_text(const char * text) {
  FILE * LOGTEXT;
  char buf[80];
  sprintf(buf, "/tmp/debug.log");

  LOGTEXT = fopen (buf, "a+");

  if ( ! LOGTEXT ) {
    if ( (LOGTEXT = fopen ( buf, "w+" ) ) == NULL )  {
         return ;
    }
  }

  fprintf (LOGTEXT,"%s
", text);
  fclose(LOGTEXT);
}

  3. Create a library to reference the external code. If the name of the library is debug_lib and it is located in the directory /usr/local/bin, the statement:

    CREATE LIBRARY DEBUG_LIB AS '/usr/local/bin/debug.sl'

    creates the Oracle library necessary to execute the program.

  4. Construct a PL/SQL wrapper to call the external procedure that logs the desired text to a file. Here is a possible wrapper:

    CREATE OR REPLACE
PACKAGE LOG_PKG IS
  PROCEDURE write_debug_message(message_in IN VARCHAR2);
END;
/

CREATE OR REPLACE
PACKAGE BODY LOG_PKG IS
  PROCEDURE write_debug_message(message_in IN VARCHAR2) IS
   external
   library debug_lib
   name "log_text"
   language c;

END;
/

All the pieces described must be in place before the file logging will work. If you’ve created all of the pieces and run without errors, but the logging still does not work, check to make sure that:

  • The listener is up and listening on the appropriate port.

  • The shared library has the appropriate privileges for the Oracle Unix account to read and execute it.

  • The file to which the external procedure is writing (/tmp/debug.log) is readable and writeable by the Oracle Unix account.

Other programmers can then place calls to this procedure in their code to generate messages into the OS file. For example:

BEGIN
  ...
  log_pkg.write_debug_message('Attempting to add client information'),
  ...
END;

Expert

Q:

27-30.

One of the most common mistakes is forgetting to refresh the information available to the listener. If information in the LISTENER.ORA file is changed while the listener is up, it does not take effect until the listener is stopped and restarted. Alternatively, the reload command can be used to refresh the information while the listener is up.

Q:

27-31.

One of the most common requests from developers or clients is to have an email message sent based on some triggering event in the database. The solution for the email-notification problem is to use an external procedure to call an operating system library to send the email. Here are the steps in the process:

  1. Ensure that the DBA for your site has properly configured an external procedure listener to handle the calls you make to OS libraries. This requires a combination of entries in both the TNSNAMES.ORA and LISTENER.ORA files.

  2. Create a shared library to call the appropriate OS command to send an email message. For this example, assume that the underlying operating system is Unix, and that the following code has been compiled into a shared library in the directory /usr/local/bin:

    #include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <signal.h>

void send_email(char * address, char * message) {
  char command[256];
  FILE * PIPE;
  sprintf(command, "/usr/bin/mailx %s", address);
  PIPE = popen (command, "w");
  if (PIPE)
  {
    fprintf(PIPE, message);
    pclose(PIPE);
  }
}

  3. Create a library to reference the OS object that contains the code that eventually sends the email message. Assuming that the name of the library is mail_util_lib, and it is located in the directory /usr/local/bin, the statement:

    CREATE LIBRARY MAIL_UTIL_LIB IS '/usr/local/bin/mail_util.sl'

    creates the Oracle library necessary to execute the program.

  4. Construct a PL/SQL wrapper to call the external procedure that sends the email. A possible wrapper is included here:

    
/* Filename on web page: email.pkg */
CREATE OR REPLACE
PACKAGE EMAIL_PKG IS
  PROCEDURE send_mail(email_address_in IN VARCHAR2,
                      message_in IN VARCHAR2);

END;
/

CREATE OR REPLACE
PACKAGE BODY EMAIL_PKG IS

--   P R I V A T E   I N T E R F A C E   ---

  FUNCTION unix_send_mail(address_in IN VARCHAR2,
                          msg_in IN VARCHAR2) return PLS_INTEGER is
   external
   library mail_util_lib
   name "send_email"
   language c;

--   P U B L I C     I N T E R F A C E   ---

  PROCEDURE send_mail(email_address_in IN VARCHAR2,
                      message_in IN VARCHAR2) IS
    jobid PLS_INTEGER;
    report_type varchar2(10) := upper(job_type_in);
    the_program varchar2(100);
  BEGIN
    -- if either piece of info is missing, do nothing
    IF email_address_in IS NULL OR
       message_in IS NULL THEN
      NULL;

    ELSE
      -- call the local wrapper to the external procedure
      unix_send_mail(email_address_in, message_in);
    END IF;

  END;

END;
/

  5. The event that causes the email to be sent is sometimes referred to as the triggering event. In this case, the triggering event is the addition of a new product, or more precisely, the insertion of a row into the products table. In order to perform an action when this event occurs, create an AFTER INSERT trigger on the products table that sends an email for each row in the clients_to_notify table. This is done by calling the following PL/SQL wrapper program unit (which in turn calls the external library you have created to send the email message):

    /* Filename on web page: notify.sql */
-- table that stores email addresses of clients that want to be notified
CREATE TABLE clients_to_notify
(email_address VARCHAR2(100))
/

-- trigger on the PRODUCTS table that will actually send the email
CREATE OR REPLACE TRIGGER email_trg
AFTER INSERT
ON products
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  CURSOR email_list IS
    SELECT email_address FROM clients_to_notify;
BEGIN
  -- for each email address we find in the clients_to_notify
  -- table, send a message
  FOR client IN email_list LOOP
    mail_pkg.send_mail(email_list.email_address,
                       'We have added to our product line.
                        Please come and visit us at www.widget.com'),
  END LOOP;

END;
/

All the pieces described must be in place before the email notifications will work. If all pieces exist and are without errors, and the email messages are still not being delivered, check to make sure that:

  • The listener is up and listening on the appropriate port.

  • The shared library has the appropriate privileges for the Oracle Unix account to read and execute it.

  • The trigger is actually firing (check this by putting a debug message in the trigger body).

  • The email server is functioning normally.

When the system is functioning, notifying a client of new products is as easy as inserting the client’s email address into the clients_to_notify table. The email message is sent automatically by the trigger.

Q:

27-32.

A single-threaded process is one that guarantees only one copy of itself is running at any given time. Since only one extproc is created for each database session, you know that only one is ever running. If Oracle were to change the implementation, you’d need to be careful that the structures or system objects being accessed in the external procedure could handle concurrent access. For example, if the external procedure attempted to open and write to a file, you’d have to handle a situation in which two requests tried to open the same file at the same time. These issues are often referred to as serialization problems, because the requests must be “serialized” or sorted and processed in the order in which they were received.

Q:

27-33.

Yes, in Oracle 8.1.5 using the DEBUG_EXTPROC package. Here is how it’s done:

  1. Execute DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT in SQL*Plus.

  2. Determine the process ID (PID) of the agent session started in the previous step.

  3. Start up your debugger and load the PID identified in the previous step.

  4. Set a break point on the pextproc function and continue the debugger.

Execute your external procedure, and the debugger should break at pextproc. Consult the Oracle documentation for a complete description of installing and using this package.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.16.70.101