Define the C procedures using one of these prototypes:

• Kernighan & Ritchie style prototypes; for example:

  void C_findRoot(x)
   float x;
  ...
  

• ISO/ANSI prototypes other than numeric data types that are less than full width (such as float, short, char); for example:

  void C_findRoot(double x)
  ...
  

• Other data types that do not change size under default argument promotions.

  This example changes size under default argument promotions:

  void C_findRoot(float x)
  ...

When you use the default configuration for external procedures, Oracle Database spawns extproc directly. You need not make configuration changes for listener.ora and tnsnames.ora. Define the environment variables to be used by external procedures in the file extproc.ora (located at $ORACLE_HOME/hs/admin on UNIX operating systems and at ORACLE_HOME\hs\admin on Windows), using this syntax:

SET name=value (environment_variable_name value)

Set the EXTPROC_DLLS environment variable, which restricts the DLLs that extproc can load, to one of these values:

• NULL; for example:

  SET EXTPROC_DLLS=
  

  This setting, the default, allows extproc to load only the DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib.

• ONLY: followed by a colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:

  SET EXTPROC_DLLS=ONLY:DLL1:DLL2
  

  This setting allows extproc to load only the DLLs named DLL1 and DLL2. This setting provides maximum security.

• A colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:

  SET EXTPROC_DLLS=DLL1:DLL2
  

  This setting allows extproc to load the DLLs named DLL1 and DLL2 and the DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib.

• ANY; for example:

  SET EXTPROC_DLLS=ANY
  

  This setting allows extproc to load any DLL.

Set the ENFORCE_CREDENTIAL environment variable, which enforces the usage of credentials when spawning an extproc process. The ENFORCE_CREDENTIAL value can be TRUE or FALSE (the default). For a discussion of ENFORCE_CREDENTIAL and the expected behaviors of an extproc process based on possible authentication and impersonation scenarios, see the information about securing external procedures in Oracle Database Security Guide.

To change the default configuration for external procedures and have your extproc agent spawned by Oracle Listener, configure your database to use external procedures that are written in C, or can be called from C applications, as follows.

Figure F-1 in Oracle Call Interface Programmer's Guide illustrates the architecture of the multithreaded extproc agent.

In this context, a DLL is any dynamically loadable operating-system file that stores external procedures.

For security reasons, 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. Alternatively, the DBA might grant you CREATE ANY LIBRARY privileges, in which case you can create your own alias libraries using this syntax:

CREATE LIBRARY [schema_name.]library_name
  {IS | AS} 'file_path'
  [AGENT 'agent_link'];

Oracle recommends that you specify the path to the DLL using a directory object, rather than only the DLL name. In this example, you create alias library c_utils, which represents DLL utils.so:

CREATE LIBRARY C_utils AS 'utils.so' IN DLL_DIRECTORY;

where DLL_DIRECTORY is a directory object that refers to '/DLLs'.

As an alternative, you can specify the full path to the DLL, as in this example:

CREATE LIBRARY C_utils AS '/DLLs/utils.so';

To allow flexibility in specifying the DLLs, you can specify the root part of the path as an environment variable using the notation ${VAR_NAME}, and set up that variable in the ENVS section of the listener.ora entry.

In this example, the agent specified by the name agent_link is used to run any external procedure in the library C_Utils:

create database link agent_link using 'agent_tns_alias';
create or replace library C_utils is
  '${EP_LIB_HOME}/utils.so' agent 'agent_link';

The environment variable EP_LIB_HOME is expanded by the agent to the appropriate path for that instance, such as /usr/bin/dll. Variable EP_LIB_HOME must be set in the file listener.ora, for the agent to be able to access it.

For security reasons, extproc, by default, loads only DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib. Also, only local sessions—that is, Oracle Database client processes that run on the same system—are allowed to connect to extproc.

To load DLLs from other directories, set the environment variable EXTPROC_DLLS. The value for this environment variable is a colon-separated (semicolon-separated on Windows systems) list of DLL names qualified with the complete path. For example:

EXTPROC_DLLS=/private1/home/johndoe/dll/myDll.so:/private1/home/johndoe/dll/newDll.so

While you can set up environment variables for extproc through the ENVS parameter in the file listener.ora, you can also set up environment variables in the extproc initialization file extproc.ora in directory $ORACLE_HOME/hs/admin. When both extproc.ora and ENVS parameter in listener.ora are used, the environment variables defined in extproc.ora take precedence. See the Oracle Net manual for more information about the EXTPROC feature.

You find or write an external C procedure, and add it to the DLL. When the procedure is in the DLL, you publish it using the call specification mechanism described in Publishing External Procedures.

The AS LANGUAGE clause is the interface between PL/SQL and a Java class method.

These subclauses tell PL/SQL where to locate the external C procedure, how to call it, and what to pass to it:

• LIBRARY

• NAME

• LANGUAGE

• CALLING STANDARD

• WITH CONTEXT

• PARAMETERS

• "AGENT IN"

Of the preceding subclauses, only LIBRARY is required.

CREATE OR REPLACE PACKAGE Demo_pack 
AUTHID DEFINER 
AS
   PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS LANGUAGE C
      NAME "C_demoExternal"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);
END;

CREATE OR REPLACE PACKAGE Demo_pack 
   AUTHID CURRENT_USER
AS 
   PROCEDURE plsToC_demoExternal_proc(x PLS_INTEGER, y VARCHAR2, z DATE);
END;
 
CREATE OR REPLACE PACKAGE BODY Demo_pack 
   SQL_NAME_RESOLVE CURRENT_USER
AS
   PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
   AS LANGUAGE JAVA
      NAME 'pkg1.class4.methodProc1(int,java.lang.String,java.sql.Date)';
END;

CREATE OR REPLACE LIBRARY SOMELIB AS '/tmp/lib.so';

CREATE OR REPLACE TYPE Demo_typ 
AUTHID DEFINER 
AS OBJECT
   (Attribute1   VARCHAR2(2000), SomeLib varchar2(20),
   MEMBER PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS LANGUAGE C
      NAME "C_demoExternal"
      LIBRARY SomeLib
      WITH CONTEXT
    --  PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE)
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE, SELF)
);

CREATE OR REPLACE TYPE Demo_typ 
AUTHID CURRENT_USER 
AS OBJECT
   (attribute1 NUMBER,
   MEMBER PROCEDURE plsToJ_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
);

CREATE OR REPLACE TYPE BODY Demo_typ 
AS
   MEMBER PROCEDURE plsToJ_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
   AS LANGUAGE JAVA
      NAME 'pkg1.class4.J_demoExternal(int,java.lang.String,java.sql.Date)';
END;

Here is an example of a publishing a Java class method in a standalone PL/SQL procedure.

CREATE OR REPLACE PROCEDURE plsToJ_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
   AUTHID CURRENT_USER 
AS LANGUAGE JAVA
   NAME 'pkg1.class4.methodProc1(int,java.lang.String,java.sql.Date)';

Here is an example of AS EXTERNAL publishing a C procedure in a standalone PL/SQL program, in which the AUTHID clause is optional. This maintains compatibility with the external procedures of Oracle Database version 8.0.

CREATE OR REPLACE PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
AS 
   EXTERNAL
   NAME "C_demoExternal"
   LIBRARY SomeLib
   WITH CONTEXT
   PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);

CREATE OR REPLACE PACKAGE Demo_pack 
AUTHID DEFINER 
AS 
   PROCEDURE plsToC_InBodyOld_proc (x PLS_INTEGER, y VARCHAR2, z DATE);
   PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE); 
   PROCEDURE plsToC_InBody_proc (x PLS_INTEGER, y VARCHAR2, z DATE);
   PROCEDURE plsToJ_InBody_proc (x PLS_INTEGER, y VARCHAR2, z DATE);

   PROCEDURE plsToJ_InSpec_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
   IS LANGUAGE JAVA
      NAME 'pkg1.class4.J_InSpec_meth(int,java.lang.String,java.sql.Date)';

PROCEDURE C_InSpec_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS LANGUAGE C
      NAME "C_demoExternal"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);
END;

CREATE OR REPLACE PACKAGE BODY Demo_pack 
AS 
PROCEDURE plsToC_InBodyOld_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS EXTERNAL
      NAME "C_InBodyOld"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE); 
PROCEDURE plsToC_demoExternal_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS LANGUAGE C
      NAME "C_demoExternal"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);
   
PROCEDURE plsToC_InBody_proc (x PLS_INTEGER, y VARCHAR2, z DATE) 
   AS LANGUAGE C
      NAME "C_InBody"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);
PROCEDURE plsToJ_InBody_proc (x PLS_INTEGER, y VARCHAR2, z DATE)
   IS LANGUAGE JAVA
      NAME 'pkg1.class4.J_InBody_meth(int,java.lang.String,java.sql.Date)';
END;

Do not pass parameters to an external procedure directly. Instead, pass them to the PL/SQL procedure that published the external procedure, specifying PL/SQL data types for the parameters. PL/SQL data types map to default external data types, as shown in Table 21-1.

BINARY_INTEGER
BOOLEAN
PLS_INTEGER

[UNSIGNED] CHAR
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
SB1, SB2, SB4
UB1, UB2, UB4
SIZE_T

INT

NATURALFoot 1
NATURALNFootref 1
POSITIVEFootref 1
POSITIVENFootref 1
SIGNTYPEFootref 1

[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
OCISTRING

STRING

LONG RAW 
RAW

RAW
OCIRAW

RAW

BFILE 
BLOB 
CLOB
NCLOB

OCILOBLOCATOR

OCILOBLOCATOR

NUMBER
DECFootref 1
DECIMALFootref 1
INTFootref 1
INTEGERFootref 1
NUMERICFootref 1
SMALLINTFootref 1

OCINUMBER

OCINUMBER

DATE

OCIDATE

OCIDATE

TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

OCIDateTime

OCIDateTime

INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH

OCIInterval

OCIInterval

composite object types: ADTs

dvoid

dvoid

composite object types: collections (associative arrays, varrays, nested tables)

OCICOLL

OCICOLL

^Footnote 1

This PL/SQL type compiles only if you use AS EXTERNAL in your call spec.

Each external data type maps to a C data type, and the data type conversions are performed implicitly. To avoid errors when declaring C prototype parameters, see Table 21-2, which shows the C data type to specify for a given external data type and PL/SQL parameter mode. For example, if the external data type of an OUT parameter is STRING, then specify the data type char * in your C prototype.

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 *

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 **

OCINUMBER

OCINumber *

OCINumber *

OCINumber *

OCISTRING

OCIString *

OCIString *

OCIString *

OCIRAW

OCIRaw *

OCIRaw *

OCIRaw *

OCIDATE

OCIDate *

OCIDate *

OCIDate *

OCICOLL

OCIColl * or OCIArray * or OCITable *

OCIColl **
or OCIArray **
or OCITable **

OCIColl ** or OCIArray ** or OCITable **

OCITYPE

OCIType *

OCIType *

OCIType *

TDO

OCIType *

OCIType *

OCIType *

ADT
(final types)

dvoid*

dvoid*

dvoid*

ADT (nonfinal types)

dvoid*

dvoid*

dvoid**

Composite data types are not self describing. Their description is stored in a Type Descriptor Object (TDO). Objects and indicator structs for objects have no predefined OCI data type, but must use the data types generated by Oracle Database's Object Type Translator (OTT). The optional TDO argument for INDICATOR, and for composite objects, in general, has the C data type, OCIType *.

OCICOLL for REF and collection arguments is optional and exists only for completeness. You cannot map a REF or collection type onto any other data type, or any other data type onto a REF or collection type.

If you specify BY VALUE, then scalar IN and RETURN arguments are passed by value (which is also the default). Alternatively, you might have them passed by reference by specifying BY REFERENCE.

By default, or if you specify BY REFERENCE, then scalar IN OUT, and OUT arguments are passed by reference. Specifying BY VALUE for IN OUT, and OUT arguments is not supported for C. The usefulness of the BY REFERENCE/VALUE clause is restricted to external data types that are, by default, passed by value. This is true for IN, and RETURN arguments of these external types:

[UNSIGNED] CHAR
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
SIZE_T
SB1
SB2
SB4
UB1
UB2
UB4
FLOAT
DOUBLE

All IN and RETURN arguments of external types not on this list, all IN OUT arguments, and all OUT arguments are passed by reference.

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

CREATE LIBRARY MathLib AS '/tmp/math.so';

CREATE OR REPLACE FUNCTION Interp_func (
/* Find the value of y at x degrees using Lagrange interpolation: */ 
   x    IN FLOAT, 
   y    IN FLOAT) 
RETURN FLOAT AS 
   LANGUAGE C
   NAME "Interp_func"
   LIBRARY MathLib;

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

• Nondefault external data types

• The current or maximum length of a parameter

• NULL/NOT NULL indicators for parameters

• Character set IDs and forms

• The position of parameters in the list

• How IN parameters are passed (by value or by reference)

If you decide to use the PARAMETERS clause, keep in mind:

• For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause.

• If you include the WITH CONTEXT clause, then you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list.

• If the external procedure is a function, then you might specify the RETURN parameter, but it must be in the last position. If RETURN is not specified, the default external type is used.

In some cases, you can use the PARAMETERS clause to override the default data type mappings. For example, you can remap the PL/SQL data type BOOLEAN from external data type INT to external data type CHAR.

You can also use the PARAMETERS clause to pass more information about PL/SQL formal parameters and function results to an external procedure. Do this by specifying one or more of these properties:

INDICATOR [{STRUCT | TDO}]
LENGTH
DURATION
MAXLEN
CHARSETID
CHARSETFORM
SELF

Table 21-3 shows the allowed and the default external data types, PL/SQL data types, and PL/SQL parameter modes allowed for a given property. MAXLEN (used to specify data returned from C back to PL/SQL) cannot be applied to an IN parameter.

INDICATOR

SHORT

SHORT

all scalars

IN
IN OUT
OUT
RETURN

BY VALUE
BY REFERENCE
BY REFERENCE
BY REFERENCE

LENGTH

[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG

INT

CHAR
LONG RAW
RAW
VARCHAR2

IN
IN OUT
OUT
RETURN

BY VALUE
BY REFERENCE
BY REFERENCE
BY REFERENCE

MAXLEN

[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG

INT

CHAR
LONG RAW
RAW
VARCHAR2

IN OUT
OUT
RETURN

BY REFERENCE
BY REFERENCE
BY REFERENCE

CHARSETID
CHARSETFORM

UNSIGNED SHORT
UNSIGNED INT
UNSIGNED LONG

UNSIGNED INT

CHAR
CLOB
VARCHAR2

IN
IN OUT
OUT
RETURN

BY VALUE
BY REFERENCE
BY REFERENCE
BY REFERENCE

In this example, the PARAMETERS clause specifies properties for the PL/SQL formal parameters and function result:

CREATE OR REPLACE FUNCTION plsToCparse_func  (
   x   IN PLS_INTEGER,
   Y   IN OUT CHAR) 
RETURN CHAR AS LANGUAGE C
   LIBRARY c_utils 
   NAME "C_parse" 
   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 (for x), LENGTH (of y), and MAXLEN (of y), and the INDICATOR for the function result in the PARAMETERS clause. The parameter RETURN corresponds to the C function identifier, which stores the result value.

Topics:

• INDICATOR

• LENGTH and MAXLEN

• CHARSETID and CHARSETFORM

• Repositioning Parameters

• SELF

• BY REFERENCE

• WITH CONTEXT

• Interlanguage Parameter Mode Mappings

Topics:

• Privileges of External Procedures

• Managing Permissions

• Creating Synonyms for External Procedures

Call the external procedure through the SQL CALL statement. You can run the CALL statement interactively from SQL*Plus. The syntax is:

CALL [schema.][{object_type_name | package_name}]procedure_name[@dblink_name]
   [(parameter_list)] [INTO :host_variable][INDICATOR][:indicator_variable];

This is equivalent to running a procedure myproc using a SQL statement of the form "SELECT myproc(...) FROM DUAL," except that the overhead associated with performing the SELECT is not incurred.

For example, here is an anonymous PL/SQL block that uses dynamic SQL to call plsToC_demoExternal_proc, which you published. PL/SQL passes three parameters to the external C procedure C_demoExternal_proc.

DECLARE
   xx NUMBER(4);
   yy VARCHAR2(10);
   zz DATE; 
 BEGIN 
 EXECUTE IMMEDIATE
 'CALL plsToC_demoExternal_proc(:xxx, :yyy, :zzz)' USING xx,yy,zz;
 END;

The semantics of the CALL statement are identical to the that of an equivalent BEGIN END block.

To call the J_calcFactorial class method published in Publishing Java Class Methods:

Result:

Y
------
   120

To call an external C procedure, PL/SQL must find the path of the appropriate DLL. The PL/SQL engine retrieves the path from the data dictionary, based on the library alias from the AS LANGUAGE clause of the procedure declaration.

Next, PL/SQL alerts a Listener process which, in turn, spawns a session-specific agent. By default, this agent is named extproc, although you can specify other names in the listener.ora file. The Listener hands over the connection to the agent, and PL/SQL passes to the agent the name of the DLL, the name of the external procedure, and any parameters.

Then, the agent loads the DLL and runs the external procedure. Also, the agent handles service calls (such as raising an exception) and callbacks to Oracle Database. Finally, the agent passes to PL/SQL any values returned by the external procedure.

After the external procedure completes, the agent remains active throughout your Oracle Database session; when you log off, the agent is stopped. Consequently, you incur the cost of launching the agent only once, no matter how many calls you make. Still, call an external procedure only when the computational benefits outweigh the cost.

Here, you call PL/SQL function plsCallsCdivisor_func, which you published in Publishing External C Procedures, from an anonymous block. PL/SQL passes the two integer parameters to external function Cdivisor_func, which returns their greatest common divisor.

DECLARE
   g    PLS_INTEGER;
   a    PLS_INTEGER;
   b    PLS_INTEGER;
CALL plsCallsCdivisor_func(a, b); 
IF g IN (2,4,8) THEN ... 

The OCIExtProcGetEnv service routine enables OCI callbacks to the database during an external procedure call. The environment handles obtained by using this function reuse the existing connection to go back to the database. If you must establish a new connection to the database, you cannot use these handles; instead, you must create your own.

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.

Both external C procedures and Java class methods can call-back to the database to do SQL operations. For a working example, see Example: Calling an External C Procedure.

An external C procedure running on Oracle Database can call a service routine to obtain OCI environment and service handles. With the OCI, you can use callbacks to run SQL statements and PL/SQL subprograms, fetch data, and manipulate LOBs. Callbacks and external procedures operate in the same user session and transaction context, and so have the same user privileges.

In SQL*Plus, suppose you run this script:

CREATE TABLE Emp_tab (empno NUMBER(10))

CREATE PROCEDURE plsToC_insertIntoEmpTab_proc (
   empno PLS_INTEGER)
AS LANGUAGE C
   NAME "C_insertEmpTab"
   LIBRARY insert_lib
   WITH CONTEXT
   PARAMETERS (
      CONTEXT, 
      empno LONG);

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

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

If you do not use callbacks, you need not include oci.h; instead, include ociextp.h.

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

In such cases, you might get:

lost RPC connection to external routine agent 

This error, which means that extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, see the preceding tables.

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 Database Installation or User's Guide.)

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

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 account, which must have CREATE LIBRARY privileges.