|Oracle 8i Data Cartridge Developer's Guide
Release 2 (8.1.6)
Part Number A76937-01
Methods: Using C/C++ and Java , 6 of 13
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 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.
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.
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 *.
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.)
|PL/SQL Type||Supported External Types||Default External Type|
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
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.
|External Datatype||IN, RETURN||IN by Ref, RETURN by Ref||IN OUT, OUT|
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.
For more information about the
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
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
CONTEXT clause to give the external procedure access to the database environment, service, and error handles. When an external procedure is called using
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" .