PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# 10
External Procedures

Well, if I called the wrong number, why did you answer the phone?

James Thurber

PL/SQL is a powerful development tool; you can use it for almost any purpose. But, it is specialized for SQL transaction processing. So, some tasks are more quickly or easily done in a lower-level language such as C, which is more efficient at machine-precision calculations. For example, a Fast Fourier Transform (FFT) routine written in C runs faster than one written in PL/SQL.

To support such special-purpose processing, PL/SQL provides an interface for calling routines written in other languages. This makes the strengths and capabilities of those languages available to you. No longer are you restricted to one language with its inherent limitations.

##### Major Topics

What Is an External Procedure?
Creating an External Procedure
Registering an External Procedure
Calling an External Procedure
How PL/SQL Calls an External Procedure
Passing Parameters to an External Procedure
Using Service Routines
Doing Callbacks
Debugging External Procedures
Guidelines for External Procedures
Restrictions on External Procedures

## What Is an External Procedure?

An external procedure is a third-generation-language routine stored in a dynamic link library (DLL), registered with PL/SQL, and called by you to do special-purpose processing. The routine must be callable from C but can be written in any language.

At run time, PL/SQL loads the library dynamically, then calls the routine as if it were a PL/SQL subprogram. To safeguard your database, the routine runs in a separate address space. But, it participates fully in the current transaction. Furthermore, the routine can call back to the database to do SQL operations.

External procedures promote reusability, efficiency, and modularity. DLLs already written and available in other languages can be called from PL/SQL programs. The DLLs are loaded only when needed, so memory is conserved. Moreover, the DLLs can be enhanced without affecting the calling programs.

Typically, external procedures are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes. For example, you might use external procedures to send instructions to a robot, solve partial differential equations, process signals, analyze time series, or create animation on a video display.

Moreover, external procedures enable you to

- move computation-bound programs from client to server, where they will execute faster thanks to more computing power and less across-network communication
- interface the database server with external systems and data sources
- extend the functionality of the database server itself

  Note: This feature is available only on platforms that support DLLs or dynamically loadable shared libraries such as Solaris `.so` libraries.

## Creating an External Procedure

To create an external procedure, you and your DBA take the following steps:

##### 1. Set Up the Environment

Your DBA sets up the environment for calling external procedures by adding entries to the files tnsnames.ora and listener.ora and by starting a Listener process exclusively for external procedures. For details, see Oracle8 Administrator's Guide.

##### 2. Identify the DLL

In this context, a DLL is any dynamically loadable operating-system file that stores external procedures. For safety, your DBA controls access to the DLL. Using the `CREATE` `LIBRARY` statement, the DBA creates a schema object called an alias library, which represents the DLL. Then, if you are an authorized user, the DBA grants you `EXECUTE` privileges on the alias library.

If the DBA grants you `CREATE` `ANY` `LIBRARY` privileges, you can create your own alias libraries using the following syntax:

    CREATE LIBRARY library_name {IS | AS} 'file_path';

You must specify the full path to the DLL because the linker cannot resolve references to just the DLL name. In the following example, you create alias library `c_utils`, which represents DLL `utils.so`:

    create library c_utils as '/DLLs/utils.so';

##### 3. Designate the External Procedure

You find or write a new routine, then add it to the DLL, or simply designate a routine already in the DLL.

##### 4. Register the External Procedure

Before you can call the external procedure, you must register it. That is, you must tell PL/SQL where to find the procedure, how to call it, and what to pass it. After registering the external procedure, you can call it from any PL/SQL program. It executes with the privileges granted to your userid.

## Registering an External Procedure

You do that by writing a special kind of PL/SQL stand-alone or packaged subprogram, which acts like a proxy for the external procedure. (By default, they have the same name.)

You write the PL/SQL stored subprogram in the usual way except that, in its body, instead of declarations and a `BEGIN` ... `END` block, you code the `EXTERNAL` clause. This clause records information about the external procedure such as its location, its name, the programming language in which it was written, and the calling standard under which it was compiled. The syntax follows:

    EXTERNAL LIBRARY library_name
       [NAME external_procedure_name]
       [LANGUAGE language_name]
       [CALLING STANDARD {C | PASCAL}]
       [WITH CONTEXT]
       [PARAMETERS (external_parameter[, external_prameter]...)];

where `external_parameter` stands for

    {  CONTEXT
     | {parameter_name | RETURN} [property] [BY REF] [external_datatype]}

and `property` stands for

    {INDICATOR | LENGTH | MAXLEN | CHARSETID | CHARSETFORM}

### Understanding the EXTERNAL Clause

The `EXTERNAL` clause is the interface between PL/SQL and an external procedure. The following subclauses tell PL/SQL where to find the procedure, how to call it, and what to pass it. (Only the `LIBRARY` subclause is required.)

#### LIBRARY

Specifies a local alias library. (You cannot use a database link to specify a remote library.) The library name is a PL/SQL identifier. So, if you enclose the name in double quotes, it becomes case sensitive. (By default, the name is stored in upper case.) You must have `EXECUTE` privileges on the alias library.

#### NAME

Specifies the external procedure to be called. If you enclose the procedure name in double quotes, it becomes case sensitive. (By default, the name is stored in upper case.) If you omit this subclause, the procedure name defaults to the upper-case name of the PL/SQL subprogram.

#### LANGUAGE

Specifies the third-generation language in which the external procedure was written. Currently, only the language name C is allowed. If you omit this subclause, the language name defaults to C.

#### CALLING STANDARD

Specifies the Windows NT calling standard (C or Pascal) under which the external procedure was compiled. (Under the Pascal Calling Standard, arguments are reversed on the stack and the called function must pop the stack.) If you omit this subclause, the calling standard defaults to C.

#### WITH CONTEXT

Specifies that a context pointer will be passed to the external procedure. The context data structure is opaque to the external procedure but is available to service routines called by the external procedure. For more information, see "Using the WITH CONTEXT Clause".

#### PARAMETERS

Specifies the positions and datatypes of parameters passed to the external procedure. It can also specify parameter properties such as current length and maximum length, and the preferred parameter passing method (by value or by reference). For more information, see "Using the PARAMETERS Clause".

### An Example

Assume that C routine `c_gcd`, which finds the greatest common divisor of two numbers, is stored in DLL `utils.so` and that you have `EXECUTE` privileges on alias library `c_utils`. The C prototype for `c_gcd` follows:

    int c_gcd(int x_val, int y_val);

In the following example, you write a PL/SQL stand-alone function named `gcd` that registers C routine `c_gcd` as an external function:

    CREATE FUNCTION gcd (
    -- find greatest common divisor of x and y
       x BINARY_INTEGER,
       y BINARY_INTEGER)
    RETURN BINARY_INTEGER AS EXTERNAL
       LIBRARY c_utils
       NAME "c_gcd"  -- quotes preserve lower case
       LANGUAGE C;

## Calling an External Procedure

You do not call an external procedure directly. Instead, you call the PL/SQL subprogram that registered the external procedure. Such calls, which you code in the usual way, can appear in

- anonymous blocks
- stand-alone and packaged subprograms
- methods of an object type
- database triggers
- SQL statements (calls to packaged functions only)

  Note: To call a packaged function from SQL statements, you must use the pragma `RESTRICT_REFERENCES`, which asserts the purity level of the function (the extent to which the function is free of side effects). PL/SQL cannot check the purity level of the corresponding external routine. So, make sure the routine does not violate the pragma. Otherwise, you might get unexpected results. For more information about the pragma, see Oracle8 Application Developer's Guide.

Any PL/SQL block or subprogram executing on the server side or on the client side (for example, in a Developer/2000 tool such as Oracle Forms) can call an external procedure. The only requirement is that you call the external procedure from C code.

On the server side, the external procedure runs in a separate process address space, which safeguards your database. Figure 10-1 shows how Oracle8 and an external procedure interact.

#### Figure 10-1 Oracle8 Interacting with an External Procedure

### An Example

In the last example, you wrote PL/SQL function `gcd`, which registered external procedure `c_gcd`, as follows:

    CREATE FUNCTION gcd (
    -- find greatest common divisor of x and y
       x BINARY_INTEGER,
       y BINARY_INTEGER)
    RETURN BINARY_INTEGER AS EXTERNAL
       LIBRARY c_utils
       NAME "c_gcd"  -- quotes preserve lower case
       LANGUAGE C;

In the example below, you call PL/SQL function `gcd` from an anonymous block. PL/SQL passes the two integer parameters to external function`c_gcd`, which returns their greatest common divisor.

    DECLARE
       g BINARY_INTEGER;
       a BINARY_INTEGER;
       b BINARY_INTEGER;
       ...
    BEGIN
       ...
       g := gcd(a, b);  -- call function
       IF g IN (2,4,8) THEN ...

## How PL/SQL Calls an External Procedure

To call an external procedure, PL/SQL must know in which DLL it resides. So, PL/SQL looks up the alias library in the `EXTERNAL` clause of the subprogram that registered the external procedure, then has Oracle look up the DLL in the data dictionary.

Next, PL/SQL alerts a Listener process, which in turn spawns (launches) a session-specific agent named extproc. Then, the Listener hands over the connection to extproc. PL/SQL passes to extproc the name of the DLL, the name of the external procedure, and any parameters.

Then, extproc loads the DLL and runs the external procedure. Also, extproc handles service calls (such as raising an exception) and callbacks to the Oracle server. Finally, extproc passes to PL/SQL any values returned by the external procedure. Figure 10-2 shows the flow of control.

#### Figure 10-2 How an External Procedure Is Called

After the external procedure completes, extproc remains active throughout your Oracle session. (When you log off, extproc is killed.) So, you incur the cost of spawning extproc only once no matter how many calls you make. Still, you should call an external procedure only when the computational benefits outweigh the cost

.

Note: The Listener must start extproc on the machine that runs the Oracle server. Starting extproc on a different machine is not supported.

### Environment Variables

The Listener sets a few required environment variables (such as `ORACLE_HOME`, `ORACLE_SID`, and `LD_LIBRARY_PATH`) for extproc. Otherwise, it provides extproc with a "clean" environment. The environment variables set for extproc are independent of those set for the client, server, and Listener. So, external procedures, which run in the extproc process, cannot read environment variables set for the client, server, or Listener process.

## Passing Parameters to an External Procedure

Passing parameters to an external procedure is complicated by several circumstances:

- The set of PL/SQL datatypes does not correspond one-to-one with the set of C datatypes.
- PL/SQL parameters can be `NULL`, whereas C parameters cannot. (Unlike C, PL/SQL includes the RDBMS concept of nullity.)
- The external procedure might need the current length or maximum length of `CHAR`, `LONG` `RAW`, `RAW`, and `VARCHAR2` parameters.
- The external procedure might need character set information about `CHAR`, `VARCHAR2`, and `CLOB` parameters.
- PL/SQL might need the current length, maximum length, or null status of values returned by the external procedure.

In the following sections, you learn how to specify a parameter list that deals with these circumstances.

### Specifying Datatypes

You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. For guidance, see Table 10-1. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.) PL/SQL does all the datatype conversions for you.

##### Table 10-1 Parameter Datatype Mappings

PL/SQL Type | Supported External Types | Default External Type
--- | --- | ---
    BINARY_INTEGER
    BOOLEAN
    PLS_INTEGER
    [UNSIGNED] CHAR
    [UNSIGNED] SHORT
    [UNSIGNED] INT
    [UNSIGNED] LONG
    SB1, SB2, SB4
    UB1, UB2, UB4
    SIZE_T
    INT
    NATURAL
    NATURALN
    POSITIVE
    POSITIVEN
    SIGNTYPE
    [UNSIGNED] CHAR
    [UNSIGNED] SHORT
    [UNSIGNED] INT
    [UNSIGNED] LONG
    SB1, SB2, SB4
    UB1, UB2, UB4
    SIZE_T
    UNSIGNED INT
    FLOAT
    REAL
    FLOAT
    FLOAT
    DOUBLE PRECISION
    DOUBLE
    DOUBLE
    CHAR
    CHARACTER
    LONG
    NCHAR
    NVARCHAR2
    ROWID
    VARCHAR
    VARCHAR2
    STRING
    STRING
    LONG RAW
    RAW
    RAW
    RAW
    BFILE
    BLOB
    CLOB
    NCLOB
    OCILOBLOCATOR
    OCILOBLOCATOR

In some cases, you can use the `PARAMETERS` clause to override the default datatype mappings. For example, you can re-map the PL/SQL datatype `BOOLEAN` from external datatype `INT` to external datatype `CHAR`.

To avoid errors when declaring C prototype parameters, refer to Table 10-2, which shows the C datatype to specify for a given external datatype and PL/SQL parameter mode. For example, if the external datatype of an `OUT` parameter is `STRING`, specify the datatype char * in your C prototype.

##### Table 10-2 External Datatype Mappings

External Datatype | IN, RETURN | IN by Ref, RETURN by Ref | IN OUT, OUT
--- | --- | --- | ---
    CHAR
    char
    char *
    char *
    UNSIGNED CHAR
    unsigned char
    unsigned char *
    unsigned char *
    SHORT
    short
    short *
    short *
    UNSIGNED SHORT
    unsigned short
    unsigned short *
    unsigned short *
    INT
    int
    int *
    int *
    UNSIGNED INT
    unsigned int
    unsigned int *
    unsigned int *
    LONG
    long
    long *
    long *
    UNSIGNED LONG
    unsigned long
    unsigned long *
    unsigned long *
    SIZE_T
    size_t
    size_t *
    size_t *
    SB1
    sb1
    sb1 *
    sb1 *
    UB1
    ub1
    ub1 *
    ub1 *
    SB2
    sb2
    sb2 *
    sb2 *
    UB2
    ub2
    ub2 *
    ub2 *
    SB4
    sb4
    sb4 *
    sb4 *
    UB4
    ub4
    ub4 *
    ub4 *
    FLOAT
    float
    float *
    float *
    DOUBLE
    double
    double *
    double *
    STRING
    char *
    char *
    char *
    RAW
    unsigned char *
    unsigned char *
    unsigned char *
    OCILOBLOCATOR
    OCILobLocator *
    OCILobLocator *
    OCILobLocator **

### Using the PARAMETERS Clause

Generally, the PL/SQL subprogram that registers an external procedure declares a list of formal parameters, as the following example shows:

    CREATE FUNCTION interp (
    -- find the value of y at x degrees using Lagrange interpolation
       x IN FLOAT,
       y IN FLOAT)
    RETURN FLOAT AS EXTERNAL
       NAME "interp"
       LIBRARY mathlib
       LANGUAGE C;

Each formal parameter declaration specifies a name, parameter mode, and PL/SQL datatype (which maps to the default external datatype). That might be all the information the external procedure needs. If not, you can provide more information using the `PARAMETERS` clause, which lets you specify

- non-default external datatypes
- the current and/or maximum length of a parameter
- null/not null indicators for parameters
- character set IDs and forms
- the positions of parameters in the list
- how `IN` parameters are passed (by value or by reference)

For every formal parameter, there must be a corresponding parameter in the `PARAMETERS` clause. If you include the `WITH` `CONTEXT` clause, you must specify the parameter `CONTEXT`, which shows the position of the context pointer in the parameter list. Also, if the external routine is a function, you must specify the parameter `RETURN` in the last position.

#### Specifying Properties

You can also use the `PARAMETERS` clause to pass additional information about PL/SQL formal parameters and function results to an external procedure. You do that by specifying the following properties:

  `INDICATOR`
  `LENGTH`
  `MAXLEN`
  `CHARSETID`
  `CHARSETFORM`

Table 10-3 shows the external datatypes, PL/SQL datatypes, and PL/SQL parameter modes allowed for a given property. Notice that `MAXLEN` cannot be applied to an `IN` parameter.

##### Table 10-3 Property Datatype Mappings

Property | C Parameter | PL/SQL Parameter
--- | --- | ---
Allowed External Types | Default External Type | Allowed Types | Allowed Modes
    INDICATOR
    SHORT
    INT
    LONG
    SHORT
| all scalars |
    IN
    IN OUT
    OUT
    RETURN
    LENGTH
    [UNSIGNED] SHORT
    [UNSIGNED] INT
    [UNSIGNED] LONG
    INT
    CHAR
    LONG RAW
    RAW
    VARCHAR2
    IN
    IN OUT
    OUT
    RETURN
    MAXLEN
    [UNSIGNED] SHORT
    [UNSIGNED] INT
    [UNSIGNED] LONG
    INT
    CHAR
    LONG RAW
    RAW
    VARCHAR2
    IN OUT
    OUT
    RETURN
    CHARSETID
    CHARSETFORM
    UNSIGNED SHORT
    UNSIGNED INT
    UNSIGNED LONG
    UNSIGNED INT
    CHAR
    CLOB
    VARCHAR2
    IN
    IN OUT
    OUT
    RETURN

In the following example, we use the `PARAMETERS` clause to specify properties for the PL/SQL formal parameters and function result:

    CREATE FUNCTION parse (
       x IN BINARY_INTEGER,
       Y IN OUT CHAR)
    RETURN CHAR AS EXTERNAL
       LIBRARY c_utils
       NAME "c_parse"
       LANGUAGE C
       CALLING STANDARD PASCAL
       PARAMETERS (
          x,            -- stores value of x
          x INDICATOR,  -- stores null status of x
          y,            -- stores value of y
          y LENGTH,     -- stores current length of y
          y MAXLEN,     -- stores maximum length of y
          RETURN INDICATOR,
          RETURN);

With this `PARAMETERS` clause, the C prototype becomes

    char * c_parse(int x, short x_ind, char *y, int *y_len,
                   int *y_maxlen, short *retind);

The additional parameters in the C prototype correspond to the `INDICATOR`, `LENGTH`, and `MAXLEN` parameters in the `PARAMETERS` clause. The parameter `RETURN` corresponds to the C function identifier, which stores the result value.

##### Using INDICATOR

An indicator is a parameter whose value "indicates" whether or not another parameter is null. PL/SQL does not need indicators because the RDBMS concept of nullity is built into the language. However, an external procedure might need to know if a parameter or function result is null. Also, an external procedure might need to signal the server that a returned "value" is actually a null, and should be treated accordingly.

In such cases, you can use the property `INDICATOR` to associate an indicator with a formal parameter. If the PL/SQL subprogram is a function, you can also associate an indicator with the function result.

To check the value of an indicator, you can use the constants `OCI_IND_NULL` and `OCI_IND_NOTNULL`. If the indicator equals `OCI_IND_NULL`, the associated parameter or function result is null. If the indicator equals `OCI_IND_NOTNULL`, the parameter or function result is not null.

For `IN` parameters, `INDICATOR` is passed by value (unless you specify `BY REF`) and is read-only (even if you specify `BY REF`). For `OUT`, `IN OUT`, and `RETURN` parameters, `INDICATOR` is passed by reference.

##### Using LENGTH and MAXLEN

In PL/SQL, there is no standard way to indicate the length of a raw or string parameter. However, in many cases, you want to pass the length of a parameter to and from an external procedure. Using the properties `LENGTH` and `MAXLEN`, you can specify parameters that store the current length and maximum length of a formal parameter.

Note: With parameters of type `RAW` or `LONG` `RAW`, you must use the property `LENGTH`.

For `IN` parameters, `LENGTH` is passed by value (unless you specify `BY REF`) and is read-only (even if you specify `BY REF`). For `OUT`, `IN OUT`, and `RETURN` parameters, `LENGTH` is passed by reference.

`MAXLEN` does not apply to `IN` parameters. For `OUT`, `IN OUT`, and `RETURN` parameters, `MAXLEN` is passed by reference but is read-only.

##### Using CHARSETID and CHARSETFORM

Oracle provides national language support, which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments.

The properties `CHARSETID` and `CHARSETFORM` identify the nondefault character set from which the character data being passed was formed. With `CHAR`, `CLOB`, and `VARCHAR2` parameters, you can use `CHARSETID` and `CHARSETFORM` to pass the character set ID and form to the external procedure.

For `IN` parameters, `CHARSETID` and `CHARSETFORM` are passed by value (unless you specify `BY REF`) and are read-only (even if you specify `BY REF`). For `OUT`, `IN OUT`, and `RETURN` parameters, `CHARSETID` and `CHARSETFORM` are passed by reference but are read-only.

The OCI attribute names for these properties are `OCI_ATTR_CHARSET_ID` and `OCI_ATTR_CHARSET_FORM`. For more information about using NLS data with the OCI, see Oracle Call Interface Programmer's Guide.

#### Repositioning Parameters

Remember, each formal parameter must have a corresponding parameter in the `PARAMETERS` clause. Their positions can differ because PL/SQL associates them by name, not by position. However, the `PARAMETERS` clause and the C prototype for the external procedure must have the same number of parameters in the same order.

#### Passing Parameters by Reference

In C, you can pass `IN` scalar parameters by value (the value of the parameter is passed) or by reference (a pointer to the value is passed). When an external procedure expects a pointer to a scalar, specify the `BY` `REF` phrase to pass the parameter by reference. An example follows:

    CREATE PROCEDURE find_root (
       x IN REAL, ...)
    AS EXTERNAL
       LIBRARY c_utils
       NAME "c_find_root"
       PARAMETERS (
          x BY REF, ...);

In this case, the C prototype would be

    void c_find_root(float *x, ...);

rather than the default

    void c_find_root(float x, ...);

### Using the WITH CONTEXT Clause

By including the `WITH` `CONTEXT` clause, you can give an external procedure access to information about parameters, exceptions, memory allocation, and the user environment. The `WITH` `CONTEXT` clause specifies that a context pointer will be passed to the external procedure. For example, if you write the following PL/SQL function

    CREATE FUNCTION get_num (
       x IN REAL)
    RETURN BINARY_INTEGER AS EXTERNAL
       LIBRARY c_utils
       NAME "c_get_num"
       LANGUAGE C
       WITH CONTEXT
       PARAMETERS (
          CONTEXT,
          x BY REF,
          RETURN INDICATOR);

then the C prototype would be

    int c_get_num(
       OCIExtProcContext *with_context,
       float *x,
       short *retind);

The context data structure is opaque to the external procedure but is available to service routines called by the external procedure.

If you also include the `PARAMETERS` clause, you must specify the parameter `CONTEXT`, which shows the position of the context pointer in the parameter list. If you omit the `PARAMETERS` clause, the context pointer is the first parameter passed to the external procedure.

## Using Service Routines

When called from an external procedure, a service routine can raise exceptions, allocate memory, and get OCI (Oracle Call Interface) handles for callbacks to the server. To use the functions, you must specify the `WITH` `CONTEXT` clause, which lets you pass a context structure to the external procedure. The context structure is declared in header file `ociextp.h` as follows:

    typedef struct OCIExtProcContext OCIExtProcContext;

Now, let us see how service routines use the context information.

### OCIExtProcAllocCallMemory

This service routine allocates n bytes of memory for the duration of the external procedure call. Any memory allocated by the function is freed automatically as soon as control returns to PL/SQL.

Note: The external procedure need not (and should not) call the C function free() to free memory allocated by this service routine.

The C prototype for this function follows:

    dvoid *OCIExtProcAllocCallMemory(
       OCIExtProcContext *with_context,
       size_t amount);

The parameters `with_context` and `amount` are the context pointer and number of bytes to allocate, respectively. The function returns an untyped pointer to the allocated memory. A return value of zero indicates failure.

In SQL*Plus, suppose you register external function `concat`, as follows:

    SQL> CREATE FUNCTION concat (
      2     str1 IN VARCHAR2,
      3     str2 IN VARCHAR2)
      4  RETURN VARCHAR2 AS EXTERNAL
      5     NAME "concat"
      6     LIBRARY stringlib
      7     WITH CONTEXT
      8     PARAMETERS (
      9        CONTEXT,
     10        str1   STRING,
     11        str1   INDICATOR short,
     12        str2   STRING,
     13        str2   INDICATOR short,
     14        RETURN INDICATOR short,
     15        RETURN LENGTH short,
     16        RETURN STRING);

When called, `concat` concatenates two strings, then returns the result. If either string is null, the result is also null. As the following example shows, `concat` uses `OCIExtProcAllocCallMemory` to allocate memory for the result string:

    char *concat(ctx, str1, str1_i, str2, str2_i, ret_i, ret_l)
    OCIExtProcContext *ctx;
    char   *str1;
    short  str1_i;
    char   *str2;
    short  str2_i;
    short  *ret_i;
    short  *ret_l;
    {
      char *tmp;
      short len;
      /* Check for null inputs. */
      if ((str1_i == OCI_IND_NULL) || (str2_i == OCI_IND_NULL))
      {
          *ret_i = (short)OCI_IND_NULL;
          /* PL/SQL has no notion of a null ptr, so
             return a zero-byte string. */
          tmp = OCIExtProcAllocCallMemory(ctx, 1);
          tmp[0] = '\0';
          return(tmp);
      }
      /* Allocate memory for result string, including null terminator. */
      len = strlen(str1) + strlen(str2);
      tmp = OCIExtProcAllocCallMemory(ctx, len + 1);

      strcpy(tmp, str1);
      strcat(tmp, str2);

      /* Set null indicator and length. */
      *ret_i = (short)OCI_IND_NOTNULL;
      *ret_l = len;

      /* Return pointer, which PL/SQL frees later. */
      return(tmp);
    }

### OCIExtProcRaiseExcp

This service routine raises a predefined exception, which must have a valid Oracle error number in the range 1 .. 32767. After doing any necessary cleanup, the external procedure must return immediately. (No values are assigned to `OUT` or `IN` `OUT` parameters.) The C prototype for this function follows:

    int OCIExtProcRaiseExcp(
       OCIExtProcContext *with_context,
       size_t error_number);

The parameters `with_context` and `error_number` are the context pointer and Oracle error number. The return values `OCIEXTPROC_SUCCESS` and `OCIEXTPROC_ERROR` indicate success or failure.

In SQL*Plus, suppose you register external procedure `divide`, as follows:

    SQL> CREATE PROCEDURE divide (
      2     dividend IN BINARY_INTEGER,
      3     divisor  IN BINARY_INTEGER,
      4     result   OUT FLOAT)
      5  AS EXTERNAL
      6     NAME "divide"
      7     LIBRARY mathlib
      8     WITH CONTEXT
      9     PARAMETERS (
     10        CONTEXT,
     11        dividend int,
     12        divisor  int,
     13        result   float);

When called, `divide` finds the quotient of two numbers. As the following example shows, if the divisor is zero, `divide` uses `OCIExtProcRaiseExcp` to raise the predefined exception `ZERO_DIVIDE`:

    void divide (ctx, dividend, divisor, result)
    OCIExtProcContext *ctx;
    int    dividend;
    int    divisor;
    float  *result;
    {
      /* Check for zero divisor. */
      if (divisor == (int)0)
      {
        /* Raise exception ZERO_DIVIDE, which is Oracle error 1476. */
        if (OCIExtProcRaiseExcp(ctx, (int)1476) == OCIEXTPROC_SUCCESS)
        {
          return;
        }
        else
        {
          /* Incorrect parameters were passed. */
          assert(0);
        }
      }
      *result = (float)dividend / (float)divisor;
    }

### OCIExtProcRaiseExcpWithMsg

This service routine raises a user-defined exception and returns a user-defined error message. The C prototype for this function follows:

    int OCIExtProcRaiseExcpWithMsg(
       OCIExtProcContext *with_context,
       size_t error_number,
       text   *error_message,
       size_t  len);

The parameters `with_context`, `error_number`, and `error_message` are the context pointer, Oracle error number, and error message text. The parameter `len` stores the length of the error message. If the message is a null-terminated string, `len` is zero. The return values `OCIEXTPROC_SUCCESS` and `OCIEXTPROC_ERROR` indicate success or failure.

In the previous example, you registered external procedure `divide`, as follows:

    SQL> CREATE PROCEDURE divide (
      2     dividend IN BINARY_INTEGER,
      3     divisor  IN BINARY_INTEGER,
      4     result   OUT FLOAT)
      5  AS EXTERNAL
      6     NAME "divide"
      7     LIBRARY mathlib
      8     WITH CONTEXT
      9     PARAMETERS (
     10        CONTEXT,
     11        dividend int,
     12        divisor  int,
     13        result   float);

In the example below, you use a different version of `divide`. With this version, if the divisor is zero, `divide` uses `OCIExtProcRaiseExcpWithMsg` to raise a user-defined exception:

    void divide (ctx, dividend, divisor, result)
    OCIExtProcContext *ctx;
    int    dividend;
    int    divisor;
    float  *result;
      /* Check for zero divisor. */
      if (divisor == (int)0)
      {
        /* Raise a user-defined exception, which is Oracle error 20100,
           and return a null-terminated error message. */
        if (OCIExtProcRaiseExcpWithMsg(ctx, (int)20100,
              "divisor is zero", 0) == OCIEXTPROC_SUCCESS)
        {
          return;
        }
        else
        {
          /*  Incorrect parameters were passed. */
          assert(0);
        }
      }
      *result = dividend / divisor;

    }

### OCIExtProcGetEnv

This service routine enables OCI callbacks to the database during an external procedure call. Use the OCI handles obtained by this function only for callbacks. If you use them for standard OCI calls, the handles establish a new connection to the database and cannot be used for callbacks in the same transaction. In other words, during an external procedure call, you can use OCI handles for callbacks or a new connection but not for both.

The C prototype for this function follows:

    sword OCIExtProcGetEnv(
       OCIExtProcContext *with_context,
       OCIEnv    **envh,
       OCISvcCtx **svch,
       OCIError  **errh);

The parameter `with_context` is the context pointer, and the parameters `envh`, `svch`, and `errh` are the OCI environment, service, and error handles, respectively. The return values `OCIEXTPROC_SUCCESS` and `OCIEXTPROC_ERROR` indicate success or failure.

The next section shows how `OCIExtProcGetEnv` might be used in callbacks. For a working example, see "Demo Program".

## Doing Callbacks

An external procedure executing on the Oracle server can call a service routine to obtain OCI environment and service handles. With the OCI, you can use callbacks to execute SQL statements and PL/SQL subprograms, fetch data, and manipulate `LOB`s. Moreover, callbacks and external procedures operate in the same user session and transaction context. So, they have the same user privileges.

In SQL*Plus, suppose you run the following script:

    CREATE TABLE emptab (empno NUMBER(10))
    /
    CREATE PROCEDURE insert_emptab (
       empno BINARY_INTEGER)
    AS EXTERNAL
       NAME "insert_emptab"
       LIBRARY insert_lib
       WITH CONTEXT
       PARAMETERS (
          CONTEXT,
          empno LONG)
    /

Later, you might call service routine `OCIExtProcGetEnv` from external procedure `insert_emptab`, as follows:

    #include <stdio.h>
    #include <stdlib.h>
    #include <oratypes.h>
    #include <oci.h>
    ...
    void insert_emptab (ctx, empno)
    OCIExtProcContext *ctx;
    long empno;
    {
      OCIEnv    *envhp;
      OCISvcCtx *svchp;
      OCIError  *errhp;
      int        err;
      ...
      err = OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp);
      ...
    }

### Restrictions on Callbacks

With callbacks, the following SQL commands and OCI routines are not supported:

- transaction control commands such as `COMMIT`
- data definition commands such as `CREATE`
- object-oriented OCI routines such as `OCIRefClear`
- polling-mode OCI routines such as `OCIGetPieceInfo`
- all these OCI routines:
    `OCIEnvInit`
    `OCIInitialize`
    `OCIPasswordChange`
    `OCIServerAttach`
    `OCIServerDetach`
    `OCISessionBegin`
    `OCISessionEnd`
    `OCISvcCtxToLda`
    `OCITransCommit`
    `OCITransDetach`
    `OCITransRollback`
    `OCITransStart`

Also, with OCI routine `OCIHandleAlloc`, the following handle types are not supported:

  `OCI_HTYPE_SERVER`
  `OCI_HTYPE_SESSION`
  `OCI_HTYPE_SVCCTX`
  `OCI_HTYPE_TRANS`

## Debugging External Procedures

Usually, when an external procedure fails, its C prototype is faulty. That is, the prototype does not match the one generated internally by PL/SQL. This can happen if you specify an incompatible C datatype. For example, to pass an `OUT` parameter of type `REAL`, you must specify float *. Specifying float, double *, or any other C datatype will result in a mismatch.

In such cases, you might get a lost RPC connection to external procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, refer to Table 10-2.

### Using Package DEBUG_EXTPROC

To help you debug external procedures, PL/SQL provides the utility package `DEBUG_EXTPROC`. To install the package, run the script `dbgextp.sql`, which you can find in the PL/SQL demo directory. (For the location of the directory, see your Oracle installation or user's guide.)

To use the package, follow the instructions in `dbgextp.sql`. Your Oracle account must have `EXECUTE` privileges on the package and `CREATE` `LIBRARY` privileges.

Note: `DEBUG_EXTPROC` works only on platforms with debuggers that can attach to a running process.

## Demo Program

Also in the PL/SQL demo directory is the script `extproc.sql`, which demonstrates the calling of an external procedure. The companion file `extproc.c` contains the C source code for the external procedure.

To run the demo, follow the instructions in `extproc.sql`. You must use the `SCOTT/TIGER` account, which must have `CREATE` `LIBRARY` privileges.

## Guidelines for External Procedures

In future releases, extproc might be a multi-threaded process. So, be sure to write thread-safe external procedures. That way, they will continue to run properly if extproc becomes multi-threaded. In particular, avoid using static variables, which can be shared by routines running in separate threads. Otherwise, you might get unexpected results.

For help in creating a dynamic link library, look in the RDBMS subdirectory `/public`, where a template makefile can be found.

When calling external procedures, never write to `IN` parameters or overflow the capacity of `OUT` parameters. (PL/SQL does no runtime checks for these error conditions.) Likewise, never read an `OUT` parameter or a function result. Also, always assign a value to `IN` `OUT` and `OUT` parameters and to function results. Otherwise, your external procedure will not return successfully.

If you include the `WITH` `CONTEXT` and `PARAMETERS` clauses, you must specify the parameter `CONTEXT`, which shows the position of the context pointer in the parameter list. If you omit the `PARAMETERS` clause, the context pointer is the first parameter passed to the external procedure.

If you include the `PARAMETERS` clause and the external routine is a function, you must specify the parameter `RETURN` (not `RETURN` property) in the last position.

For every formal parameter, there must be a corresponding parameter in the `PARAMETERS` clause. Also, make sure that the datatypes of parameters in the `PARAMETERS` clause are compatible with those in the C prototype because no implicit conversions are done.

With a parameter of type `RAW` or `LONG` `RAW`, you must use the property `LENGTH`. Also, if that parameter is `IN` `OUT` or `OUT` and null, you must set the length of the corresponding C parameter to zero.

## Restrictions on External Procedures

Currently, the following restrictions apply to external procedures:

- This feature is available only on platforms that support DLLs.
- Only routines callable from C code (not C++ code) are supported.
- You cannot pass PL/SQL cursor variables, records, collections, or instances of an object type to an external procedure.
- In the `LIBRARY` subclause, you cannot use a database link to specify a remote library.
- The Listener must start agent extproc on the machine that runs the Oracle server. Starting extrproc on a different machine is not supported.
- The maximum number of parameters that you can pass to a C external procedure is 128. However, if you pass float or double parameters by value, the maximum is less than 128. How much less depends on the number of such parameters and your operating system. To get a rough estimate, count each float or double passed by value as two parameters.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index