Configuration Files for External Procedures

The configuration files listener.ora and tnsnames.ora must have appropriate entries so that the Listener can dispatch the external procedures.

The Listener configuration file listener.ora must have a SID_DESC entry for the external procedure. For example:

# Listener configuration file  
# This file is generated by stkconf.tsc  
 
CONNECT_TIMEOUT_LISTENER = 0  
 
LISTENER = (ADDRESS_LIST=  
 (ADDRESS=(PROTOCOL=ipc)(KEY=o8))  
 (ADDRESS=(PROTOCOL=tcp)(HOST=unix123)(PORT=1521))  
)  
 
SID_LIST_LISTENER = (SID_LIST=   
(SID_DESC=(SID_NAME=o8)(ORACLE_HOME=/rdbms/u01/app/oracle/product/8.0
.3))  
 
(SID_DESC=(SID_NAME=extproc)(ORACLE_HOME=/rdbms/u01/app/oracle/product/
8.0.3)(PROGRAM=extproc))  
)

This listener.ora example assumes the following:

• The Oracle instance is called o8.

• The system or node on which the Oracle server runs is named unix123.

• The installation directory for the Oracle server is /rdbms/u01.

• The port number for Oracle TCP/IP communication is the default Listener port 1521.

The tnsnames.ora file (network substrate configuration file) must also be updated to refer to the external procedure. For example:

o8      = 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unix123)(PORT=1521))(CONNECT_
DATA=(SID=o8)))
extproc_connection_data = 
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=o8))(CONNECT_DATA=(SID=extproc)))

This tnsnames.ora example assumes that IPC mechanisms are used to communicate with the external procedure. You can also use, for example, TCP/IP for communication, in which case the PROTOCOL parameter must be set to tcp.

For more information about configuring the listener.ora and tnsnames.ora files, see the Oracle8i Administrator's Guide.

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.

An example of parameter passing is shown in "Doing Callbacks", where the package function DS_Findmin(data CLOB) calls the C routine c_findmin and the CLOB argument is passed to the C routine as an OCILobLocator *.

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 4-1. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)

Table 4-1 Parameter Datatype Mappings  

BINARY_INTEGER,
BOOLEAN,
PLS_INTEGER 

CHAR, UNSIGNED CHAR, SHORT, 
UNSIGNED SHORT, INT, UNSIGNED 
INT, LONG, UNSIGNED LONG, SB1, 
UB1, SB2, UB2, SB4, UB4, SIZE_
T

INT

NATURAL,
NATURALN, 
POSITIVE, 
POSITIVEN, 
SIGNTYPE

CHAR, UNSIGNED CHAR, SHORT, 
UNSIGNED SHORT, INT, UNSIGNED 
INT, LONG, UNSIGNED LONG, SB1, 
UB1, SB2, UB2, SB4, UB4, SIZE_
T

UNSIGNED INT

FLOAT,
REAL

FLOAT

FLOAT

DOUBLE PRECISION

DOUBLE

DOUBLE

CHAR, 
CHARACTER,
LONG,
ROWID,
VARCHAR, 
VARCHAR2 

STRING

STRING

LONG RAW,
RAW

RAW

RAW

BFILE, 
BLOB, 
CLOB

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 4-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 CHAR, specify the datatype char * in your C prototype.

Table 4-2 External Datatype Mappings  

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

You can optionally use the PARAMETERS clause to pass additional information about PL/SQL formal parameters and function return values to an external procedure. You can also use this clause to reposition parameters.

Using the WITH CONTEXT Clause

Once launched, an external procedure may need to access the database. For example, DS_Findmin does not copy the entire CLOB data over to c_findmin, because doing so would vastly increase the amount of stack that the C routine needs. Instead, the PL/SQL function just passes a LOB locator to the C routine, with the intent that the database will be re-accessed from C to read the actual LOB data.

When the C routine reads the data, it can use the OCI buffering and streaming interfaces associated with LOBs (see the Oracle Call Interface Programmer's Guide for details), so that only incremental amounts of stack are needed. Such re-access of the database from an external procedure is known as a callback.

To be able to call back to a database, you need to use the WITH CONTEXT clause to give the external procedure access to the database environment, service, and error handles. When an external procedure is called using WITH CONTEXT, the corresponding C routine automatically gets as its first parameter an argument of type OCIExtProcContext *. (The order of the parameters can be changed using the PARAMETERS clause.) You can use this context pointer to fetch the handles using the OCIExtProcGetEnv call, and then call back to the database. This procedure is shown in "Doing Callbacks" .