14 Calling External Procedures

Overview of Multi-Language Programs

Oracle Database lets you work in different languages:

• PL/SQL, as described in the Oracle Database PL/SQL User's Guide and Reference

• C, by means of the Oracle Call Interface (OCI), as described in the Oracle Call Interface Programmer's Guide

• C or C++, by means of the Pro*C/C++ precompiler, as described in the Pro*C/C++ Programmer's Guide

• COBOL, by means of the Pro*COBOL precompiler, as described in the Pro*COBOL Programmer's Guide

• Visual Basic, by means of Oracle Objects for OLE (OO4O), as described in Oracle Objects for OLE Developer's Guide.

• Java, by means of the JDBC Application Programmers Interface (API). Refer to Oracle Database Java Developer's Guide.

How should you choose between these different implementation possibilities? Each of these languages offers different advantages: ease of use, the availability of programmers with specific expertise, the need for portability, and the existence of legacy code are powerful determinants.

The choice may narrow depending on how your application needs to work with Oracle Database:

• PL/SQL is a powerful development tool, specialized for SQL transaction processing.

• Some computation-intensive tasks are executed most efficiently in a lower level language, such as C.

• The need for portability, together with the need for security, may influence you to select Java.

Most significantly, from the point of view of performance, you should note that only PL/SQL and Java methods run within the address space of the server. C/C++ methods are dispatched as external procedures, and run on the server machine but outside the address space of the database server. Pro*COBOL and Pro*C are precompilers, and Visual Basic accesses Oracle Database through the OCI, which is implemented in C.

Taking all these factors into account suggests that there may be a number of situations in which you may need to implement your application in more than one language. For instance, the introduction of Java running within the address space of the server suggest that you may want to import existing Java applications into the database, and then leverage this technology by calling Java functions from PL/SQL and SQL.

PL/SQL external procedures allow you to write C function calls as PL/SQL bodies. These C functions are callable directly from PL/SQL, and from SQL through PL/SQL procedure calls. The database provides a special-purpose interface, the call specification, that lets you call external procedures from other languages. While this service is designed for intercommunication between SQL, PL/SQL, C, and Java, it is accessible from any base language that can call these languages. For example, your procedure can be written in a language other than Java or C and still be usable by SQL or PL/SQL, as long as your procedure is callable by C. Therefore, if you have a candidate C++ procedure, you would use a C++ extern "C" statement in that procedure to make it callable by C.

This means that the strengths and capabilities of different languages are available to you, regardless of your programmatic environment. You are not restricted to one language with its inherent limitations. External procedures promote reusability and modularity because you can deploy specific languages for specific purposes.

What Is an External Procedure?

An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing.

For instance, when you work in PL/SQL, the language loads the library dynamically at runtime, and then calls the procedure as if it were a PL/SQL subprogram. These procedures participate fully in the current transaction and can call back to the database to perform SQL operations.

The procedures are loaded only when necessary, so memory is conserved. Because the decoupling of the call specification from its implementation body means that the procedures can be enhanced without affecting the calling programs.

External procedures let you:

• Isolate execution of client applications and processes from the database instance to ensure that any problems on the client side do not adversely impact the database.

• Move computation-bound programs from client to server where they execute faster (because they avoid the round-trips of network communication)

• Interface the database server with external systems and data sources

• Extend the functionality of the database server itself

Overview of The Call Specification for External Procedures

You publish external procedures through call specifications, which provide a superset of the AS EXTERNAL function through the AS LANGUAGE clause. AS LANGUAGE call specifications allow the publishing of external C procedures, but also Java class methods.

In general, call specifications enable:

• Dispatching the appropriate C or Java target procedure

• Datatype conversions

• Parameter mode mappings

• Automatic memory allocation and cleanup

• Purity constraints to be specified, where necessary, for packaged functions called from SQL.

• Calling Java methods or C procedures from database triggers

• Location flexibility: you can put AS LANGUAGE call specifications in package or type specifications, or package (or type) bodies to optimize performance and hide implementation details

To use an already-existing program as an external procedure, load, publish, and then call it.

Loading External Procedures

To make your external C procedures or Java methods available to PL/SQL, you must first load them. The manner of doing this depends upon whether the procedure is written in C or Java.

/home/java/bin/Agent.class

CONNECT System/Manager
GRANT CREATE ANY DIRECTORY TO Scott IDENTIFIED BY Tiger;
CONNECT Scott/Tiger
CREATE DIRECTORY Bfile_dir AS '/home/java/bin';

CREATE JAVA CLASS USING BFILE (Bfile_dir, 'Agent.class');

Loading External C Procedures

In order to set up your database configuration to use external procedures that are written in C, or can be called from C applications, you and your database administrator must take the following steps:

Note: This feature is available only on platforms that support dynamically linked libraries (DLLs) or dynamically loadable shared libraries such as Solaris .so libraries. The external procedure agent can call procedures in any library that complies with the calling standard used. The supported calling standard is C. Refer to "CALLING STANDARD" for more information on the calling standard sub clause used with external procedures in PL/SQL.

Step 1 Set Up the Environment

Your database administrator must perform the following tasks to configure your database to use external procedures that are written in C, or can be called from C applications:

• Set configuration parameters for the agent, named extproc by default, in the configuration files tnsnames.ora and listener.ora. This establishes the connection for the external procedure agent when the database is started.

• Start a listener process exclusively for external procedures.

  The Listener sets a few required environment variables (such as ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH) for the external procedure agent. It can also define specific environment variables in the ENVS section of its listener.ora entry, and these variables are passed to the agent process. Otherwise, it provides the agent with a "clean" environment. The environment variables set for the agent are independent of those set for the client and server. Therefore, external procedures, which run in the agent process, cannot read environment variables set for the client or server processes.

  
  

  Note: It is possible for you to set and read environment variables themselves by using the standard C procedures setenv() and getenv(), respectively. Environment variables, set this way, are specific to the agent process, which means that they can be read by all functions executed in that process, but not by any other process running on the same host.

  
  

• Determine whether the agent for your external procedure will run in dedicated mode (the default mode) or multithreaded mode. In dedicated mode, one "dedicated" agent is launched for each user. In multithreaded mode, a single multithreaded agent is launched. The multithreaded agent handles calls using different threads for different users. In a configuration where many users will call the external procedures, using a multithreaded agent is recommended to conserve system resources.

  If the agent will run in dedicated mode, additional configuration of the agent process is not necessary.

  If the agent will run in multithreaded mode, your database administrator must configure the database system to start the agent in multithreaded mode (as a multithreaded agent). This is done using the agent control utility agtctl. For example, start the agent using the agent control utility startup command:

agtctl startup extproc agent_sid 

where agent_sid is the system identifier which this agent will service. An entry for this system identifier is typically added as an entry in the file tnsnames.ora. Details on the agent control utility are in the Oracle Database Heterogeneous Connectivity Administrator's Guide.

Note: If you use a multithreaded agent, the library you call must be thread safe—to avoid errors such as a corrupt call stack. The database server, the agent process, and the listener process that spawns the agent process must all reside on the same host. By default, the agent process runs on the same database instance as your main application. In situations where reliability is critical, you might want to run the agent process for the external procedure on a separate database instance (still on the same host), so that any problems in the agent do not affect the primary database server. To do so, specify the separate database instance using a database link.

Figure 14-1 illustrates the architecture of the multithreaded external procedure agent. User sessions 1 and 2 issue requests for callouts to functions in some DLLs. These requests get serviced through heterogeneous services to the multithreaded extproc agent. These requests get handled by the agent's dispatcher threads, which then pass them on to the task threads. The task thread that is actually handling a request is responsible for loading the respective DLL and calling the function therein.

• All requests from a user session get handled by the same dispatcher thread. For example, dispatcher 1 handles communication with user session 1, and dispatcher 2 handles communication with user session 2. This is the case for the lifetime of the session.

• The individual requests can, however, be serviced by different task threads. For example, task thread 1 could handle the request from user session 1 at one time, and handle the request from user session 2 at another time.

Figure 14-1 Multithreaded External Procedure Agent Architecture

Description of the illustration adfns100.gif

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for details on configuring the agent process for the external procedure to use multithreaded mode, architecture, and for additional details on multithreaded agents Oracle Database Administrator's Guide. for details on managing processes for external procedures

Step 1 Identify the DLL

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 may grant you CREATE ANY LIBRARY privileges, in which case you can create your own alias libraries using the following syntax:

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

It is recommended that you specify the full path to the DLL, rather than 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';

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 the following example, the agent specified by the name agent_link is used to run any external procedure in the library C_Utils. 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.

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

For security reasons, EXTPROC, by default, will only load DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib. Also, only local sessions—that is, Oracle Database client processes that are running on the same machine—are allowed to connect to EXTPROC.

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

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

The preferred method to set this environment variable is through the ENVS parameter in the file listener.ora. Refer to the Oracle Net manual for more information on the EXTPROC feature.

Note the following:

• On a Windows system, you would specify the path using a drive letter and backslash characters (\) in the path.

• This technique is not applicable for VMS systems, where the ENVS section of listener.ora is not supported.

Step 1 Publish the External Procedure

You find or write a new external C procedure, then add it to the DLL. When the procedure is in the DLL, you publish it using the call specification mechanism described in the following section.

Publishing External Procedures

Oracle Database can only use external procedures that are published through a call specification, which maps names, parameter types, and return types for your Java class method or C external procedure to their SQL counterparts. It is written like any other PL/SQL stored subprogram except that, in its body, instead of declarations and a BEGIN ... END block, you code the AS LANGUAGE clause.

The AS LANGUAGE clause specifies:

• Which language the procedure is written in.

• For a Java method:

  • The signature of the Java method.

• For a C procedure:

  • The alias library corresponding to the DLL for a C procedure.

  • The name of the C procedure in a DLL.

  • Various options for specifying how parameters are passed.

  • Which parameter (if any) holds the name of the external procedure agent, for running the procedure on a different machine.

You begin the declaration using the normal CREATE OR REPLACE syntax for a procedure, function, package specification, package body, type specification, or type body.

The call specification follows the name and parameter declarations. Its syntax is:

{IS | AS} LANGUAGE {C | JAVA}

This is then followed by either:

NAME  java_string_literal_name

Where java_string_literal_name is the signature of your Java method, or by:

LIBRARY library_name
[NAME c_string_literal_name]
[WITH CONTEXT]
[PARAMETERS (external_parameter[, external_parameter]...)];

Where library_name is the name of your alias library, c_string_literal_name is the name of your external C procedure, and external_parameter stands for:

{  CONTEXT 
 | SELF [{TDO | property}]
 | {parameter_name | RETURN} [property] [BY REFERENCE] [external_datatype]}

property stands for:

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

Publishing Java Class Methods

Java classes and their methods are stored in RDBMS libunits in which you can load Java sources, binaries and resources using the LOADJAVA utility or the CREATEJAVA SQL statements. Libunits can be considered analogous to DLLs written, for example, in C—although they map one-to-one with Java classes, whereas DLLs can contain more than one procedure.

The NAME-clause string uniquely identifies the Java method. The PL/SQL function or procedure and Java must correspond with regard to parameters. If the Java method takes no parameters, then you must code an empty parameter list for it.

When you load Java classes into the RDBMS, they are not published to SQL automatically. This is because the methods of many Java classes are called only from other Java classes, or take parameters for which there is no appropriate SQL type.

Suppose you want to publish the following Java method named J_calcFactorial, which returns the factorial of its argument:

package myRoutines.math;
public class Factorial {
   public static int J_calcFactorial (int n) {
      if (n == 1) return 1;
      else return n * J_calcFactorial(n - 1);
   }
}

The following call specification publishes Java method J_calcFactorial as PL/SQL stored function plsToJavaFac_func, using SQL*Plus:

CREATE OR REPLACE FUNCTION Plstojavafac_func (N NUMBER) RETURN NUMBER AS
   LANGUAGE JAVA
   NAME 'myRoutines.math.Factorial.J_calcFactorial(int) return int';

Publishing External C Procedures

In the following example, you write a PL/SQL standalone function named plsCallsCdivisor_func that publishes C function Cdivisor_func as an external function:

CREATE OR REPLACE FUNCTION Plscallscdivisor_func (
/* Find greatest common divisor of x and y: */
   x     BINARY_INTEGER, 
   y     BINARY_INTEGER) 
RETURN BINARY_INTEGER 
AS LANGUAGE C
   LIBRARY C_utils
   NAME "Cdivisor_func"; /* Quotation marks preserve case. */

Locations of Call Specifications

For both Java class methods and external C procedures, call specifications can be specified in any of the following locations:

• Standalone PL/SQL Procedures and Functions

• PL/SQL Package Specifications

• PL/SQL Package Bodies

• Object Type Specifications

• Object Type Bodies

  
  

  Note: Under Oracle Database version 8.0, AS EXTERNAL call specifications could not be placed in package or type bodies.

  
  

We have already shown an example of call specification located in a standalone PL/SQL function. Here are some examples showing some of the other locations.

Example: Locating a Call Specification in a PL/SQL Package

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

Example: Locating a Call Specification in a PL/SQL Package Body

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

Example: Locating a Call Specification in an Object Type Specification

CONN SYS/CHANGE_ON_INSTALL AS SYSDBA;
GRANT CREATE ANY LIBRARY TO scott;
CONNECT scott/tiger
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 BINARY_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)
);

Example: Locating a Call Specification in an Object Type Body

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

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

Example: Java with AUTHID

Here is an example of a publishing a Java class method in a standalonePL/SQL subprogram.

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

Example: C with Optional AUTHID

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 BINARY_INTEGER, y VARCHAR2, z DATE) 
AS 
   EXTERNAL
   LANGUAGE C
   NAME "C_demoExternal"
   LIBRARY SomeLib
   WITH CONTEXT
   PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE);

Example: Mixing Call Specifications in a Package

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

   PROCEDURE plsToJ_InSpec_proc (x BINARY_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 BINARY_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 BINARY_INTEGER, y VARCHAR2, z DATE) 
   AS EXTERNAL
      LANGUAGE C
      NAME "C_InBodyOld"
      LIBRARY SomeLib
      WITH CONTEXT
      PARAMETERS(CONTEXT, x INT, y STRING, z OCIDATE); 
PROCEDURE plsToC_demoExternal_proc (x BINARY_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 BINARY_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 BINARY_INTEGER, y VARCHAR2, z DATE)
   IS LANGUAGE JAVA
      NAME 'pkg1.class4.J_InBody_meth(int,java.lang.String,java.sql.Date)';
END;

Passing Parameters to External C Procedures with Call Specifications

Call specifications allows a mapping between PL/SQL and C datatypes. Refer to "Specifying Datatypes" for datatype mappings.

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

• The available set of PL/SQL datatypes does not correspond one-to-one with the set of C datatypes.

• Unlike C, PL/SQL includes the RDBMS concept of nullity. Therefore, PL/SQL parameters can be NULL, whereas C parameters cannot.

• 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.

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 (varrays, nested tables)

OCICOLL

OCICOLL

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 (non-final types)

dvoid*

dvoid*

dvoid**

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

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;

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

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

CREATE OR REPLACE FUNCTION plsToCparse_func  (
   x   IN BINARY_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);

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

CONNECT system/manager
GRANT CONNECT,RESOURCE,CREATE LIBRARY TO scott IDENTIFIED BY tiger;
CONNECT scott/tiger
CREATE OR REPLACE LIBRARY agelib UNTRUSTED IS 
  '/tmp/scott1.so';.

CREATE OR REPLACE TYPE Person1_typ AS OBJECT
( Name      VARCHAR2(30),
  B_date    DATE,
  MEMBER FUNCTION calcAge_func RETURN NUMBER)
);

CREATE OR REPLACE TYPE BODY Person1_typ AS 
  MEMBER FUNCTION calcAge_func RETURN NUMBER 
  AS LANGUAGE C
  NAME "age"
  LIBRARY agelib
  WITH CONTEXT
  PARAMETERS
  ( CONTEXT,
    SELF,
    SELF INDICATOR STRUCT,
    SELF TDO,
    RETURN INDICATOR
  );
END;

CREATE TABLE Person_tab OF Person1_typ;

INSERT INTO Person_tab VALUES
   ('SCOTT', TO_DATE('14-MAY-85'));

INSERT INTO Person_tab VALUES
   ('TIGER', TO_DATE('22-DEC-71'));

SELECT p.name, p.b_date, p.calcAge_func() FROM Person_tab p; 

NAME                           B_DATE    P.CALCAGE_ 
------------------------------ --------- ---------- 
SCOTT                          14-MAY-85          0 
TIGER                          22-DEC-71          0
 

#include <oci.h>

struct PERSON 
{ 
    OCIString   *NAME; 
    OCIDate      B_DATE; 
}; 
typedef struct PERSON PERSON; 
 
struct PERSON_ind 
{ 
    OCIInd    _atomic; 
    OCIInd    NAME; 
    OCIInd    B_DATE; 
}; 
typedef struct PERSON_ind PERSON_ind; 
 
OCINumber *age (ctx, person_obj, person_obj_ind, tdo, ret_ind) 
OCIExtProcContext *ctx; 
PERSON         *person_obj; 
PERSON_ind     *person_obj_ind; 
OCIType        *tdo; 
OCIInd         *ret_ind; 
{ 
    sword      err; 
    text       errbuf[512]; 
    OCIEnv    *envh; 
    OCISvcCtx *svch; 
    OCIError  *errh; 
    OCINumber *age; 
    int        inum = 0;
    sword      status;
  
    /* get OCI Environment */
    err = OCIExtProcGetEnv( ctx, &envh, &svch, &errh ); 

    /* initialize return age to 0 */
    age = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
    status = OCINumberFromInt(errh, &inum, sizeof(inum), OCI_NUMBER_SIGNED,
                              age);
    if (status != OCI_SUCCESS)
    {
      OCIExtProcRaiseExcp(ctx, (int)1476);
      return (age);
    }

    /* return NULL if the person object is null or the birthdate is null */
    if ( person_obj_ind->_atomic == OCI_IND_NULL || 
         person_obj_ind->B_DATE  == OCI_IND_NULL ) 
    { 
        *ret_ind = OCI_IND_NULL;
        return (age); 
    } 

    /* The actual implementation to calculate the age is left to the reader,
       but an easy way of doing this is a callback of the form: 
            select trunc(months_between(sysdate, person_obj->b_date) / 12) 
            from dual;   
    */ 
    *ret_ind = OCI_IND_NOTNULL;
    return (age);
} 

CREATE OR REPLACE PROCEDURE findRoot_proc (
   x IN REAL)
AS LANGUAGE C
   LIBRARY c_utils
   NAME "C_findRoot"
   PARAMETERS (
      x BY REFERENCE);

void C_findRoot(float *x);

void C_findRoot(float x);

CREATE OR REPLACE FUNCTION getNum_func (
   x IN REAL) 
RETURN BINARY_INTEGER AS LANGUAGE C
   LIBRARY c_utils
   NAME "C_getNum"
   WITH CONTEXT
   PARAMETERS (
      CONTEXT,
      x BY REFERENCE,
      RETURN INDICATOR);

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

Executing External Procedures with the CALL Statement

Now that you have published your Java class method or external C procedure, you are ready to invoke it.

Do not call an external procedure directly. Instead, use the CALL statement to call the PL/SQL subprogram that published the external procedure. Refer to "CALL Statement Syntax".

Such calls, which you code in the same manner as a call to a regular PL/SQL procedure or function, can appear in the following:

• Anonymous blocks

• Standalone and packaged subprograms

• Methods of an object type

• Database triggers

• SQL statements (calls to packaged functions only).

Any PL/SQL block or subprogram executing on the server side, or on the client side, (for example, in a tool such as Oracle Forms) can call an external procedure. On the server side, the external procedure runs in a separate process address space, which safeguards your database. Figure 14-2 shows how Oracle Database and external procedures interact.

Figure 14-2 Oracle Database and External Procedures

Description of the illustration adfns063.gif

CONNECT system/manager
GRANT CREATE ANY DIRECTORY to scott; 
CONNECT scott/tiger
CREATE OR REPLACE DIRECTORY bfile_dir AS '/tmp';
CREATE OR REPLACE JAVA RESOURCE NAMED "appImages" USING BFILE (bfile_dir,'bfile_audio');

GRANT EXECUTE ON plsToJ_demoExternal_proc TO Public;
REVOKE EXECUTE ON plsToJ_demoExternal_proc FROM Public;
GRANT EXECUTE ON JAVA RESOURCE "appImages" TO Public;
GRANT EXECUTE ON plsToJ_demoExternal_proc TO Scott;
REVOKE EXECUTE ON plsToJ_demoExternal_proc FROM Scott;

CREATE PUBLIC SYNONYM Rfac FOR Scott.RecursiveFactorial;

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

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

VARIABLE x NUMBER
VARIABLE y NUMBER
EXECUTE :x := 5;

CALL J_calcFactorial(:x) INTO :y;
PRINT y

Y
------
   120

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

Handling Errors and Exceptions in Multi-Language Programs

The PL/SQL compiler raises compile time errors if an AS EXTERNAL call specification is found in a TYPE or PACKAGE specification.

C programs can raise exceptions through the OCIExtproc... functions.

Using Service Procedures with External C Procedures

When called from an external procedure, a service routine can raise exceptions, allocate memory, and invoke OCI handles for callbacks to the server. To use a service routine, 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;

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.

The C prototype for this function is as 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 publish external function plsToC_concat_func, as follows:

CONNECT system/manager
DROP USER y CASCADE;
GRANT CONNECT,RESOURCE,CREATE LIBRARY TO y IDENTIFIED BY y;
CONNECT y/y
CREATE LIBRARY stringlib AS  '/private/varora/ilmswork/Cexamples/john2.so';

CREATE OR REPLACE FUNCTION plsToC_concat_func ( 
   str1 IN VARCHAR2,  
   str2 IN VARCHAR2)  
RETURN VARCHAR2 AS LANGUAGE C 
NAME "concat" 
LIBRARY stringlib 
WITH CONTEXT 
PARAMETERS ( 
CONTEXT,  
str1   STRING,  
str1   INDICATOR short,  
str2   STRING,  
str2   INDICATOR short,  
RETURN INDICATOR short,  
RETURN LENGTH short,  
RETURN STRING); 

When called, C_concat concatenates two strings, then returns the result:

select plsToC_concat_func('hello ', 'world') from dual; 
PLSTOC_CONCAT_FUNC('HELLO','WORLD') 
-----------------------------------------------------------------------------
hello world

If either string is NULL, the result is also NULL. As the following example shows, C_concat uses OCIExtProcAllocCallMemory to allocate memory for the result string:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
#include <ociextp.h>

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); 
} 

#ifdef LATER
static void checkerr (/*_ OCIError *errhp, sword status _*/);

void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

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);
}

/*======================================================================*/
int main(char *argv, int argc)
{
  OCIExtProcContext *ctx;
  char           *str1;
  short          str1_i;
  char           *str2;
  short          str2_i;
  short          *ret_i;
  short          *ret_l;
  /* OCI Handles */
  OCIEnv        *envhp;
  OCIServer     *srvhp;
  OCISvcCtx     *svchp;
  OCIError      *errhp;
  OCISession    *authp;
  OCIStmt       *stmthp;
  OCILobLocator *clob, *blob;
  OCILobLocator *Lob_loc;

  /* Initialize and Logon */
  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

  (void) OCIEnvInit( (OCIEnv **) &envhp, 
                    OCI_DEFAULT, (size_t) 0, 
                    (dvoid **) 0 );

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 
                   (size_t) 0, (dvoid **) 0);

  /* Server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  /* Service context */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  /* Attach to Oracle Database */
  (void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);

  /* Set attribute server context in the service context */
  (void) OCIAttrSet ((dvoid *) svchp, OCI_HTYPE_SVCCTX, 
                     (dvoid *)srvhp, (ub4) 0,
                    OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, 
                        (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION,
                        (size_t) 0, (dvoid **) 0);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) "samp", (ub4)4,
                 (ub4) OCI_ATTR_USERNAME, errhp);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) "samp", (ub4) 4,
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  /* Begin a User Session */
  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS, 
                          (ub4) OCI_DEFAULT));

  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

  /* -----------------------User Logged In------------------------------*/
  printf ("user logged in \n");

  /* allocate a statement handle */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, 
                                     (ub4) OCI_DTYPE_LOB, 
                                     (size_t) 0, (dvoid **) 0)); 

  /* ------- subroutine called  here-----------------------*/ 
  printf ("calling concat...\n");
  concat(ctx, str1, str1_i, str2, str2_i, ret_i, ret_l);

  return 0;
}

#endif

OCIExtProcRaiseExcp

This service routine raises a predefined exception, which must have a valid Oracle Database error number in the range 1..32767. After doing any necessary cleanup, your 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 errnum);

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

In SQL*Plus, suppose you publish external procedure plsTo_divide_proc, as follows:

CREATE OR REPLACE PROCEDURE plsTo_divide_proc (
   dividend IN BINARY_INTEGER, 
   divisor  IN BINARY_INTEGER, 
   result   OUT FLOAT) 
AS LANGUAGE C
   NAME "C_divide"
   LIBRARY MathLib
   WITH CONTEXT
   PARAMETERS (
      CONTEXT, 
      dividend INT, 
      divisor  INT, 
      result   FLOAT);

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

void C_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 Database error number, and error message text. The parameter len stores the length of the error message. If the message is a null-terminated string, then len is zero. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.

In the previous example, we published external procedure plsTo_divide_proc. In the following example, you use a different implementation. With this version, if the divisor is zero, then C_divide uses OCIExtProcRaiseExcpWithMsg to raise a user-defined exception:

void C_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;

}

Doing Callbacks with External C Procedures

Here is a function that is used to enable callbacks.

OCIExtProcGetEnv()

This 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 need to 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, refer to "Demo Program" .

An external C procedure executing on Oracle Database 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. 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 the following script:

CREATE TABLE Emp_tab (empno NUMBER(10))

CREATE PROCEDURE plsToC_insertIntoEmpTab_proc (
   empno BINARY_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 do not need to include oci.h; instead, just include ociextp.h.

OCI_HTYPE_SERVER 
OCI_HTYPE_SESSION 
OCI_HTYPE_SVCCTX 
OCI_HTYPE_TRANS

lost RPC connection to external routine agent