PL/SQL User's Guide and Reference
Release 8.0

A58236-01

Library

Product

Contents

Index

Prev Next

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

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

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:

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

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 LOBs. 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:

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:




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index