14 Developing Applications with Multiple Programming Languages

Loading Java Class Methods

One way to load Java programs is to use the CREATE JAVA statement, which you can run interactively from SQL*Plus. When implicitly called by the CREATE JAVA statement, the Java Virtual Machine (JVM)] library manager loads Java binaries (.class files) and resources from local BFILE or LOB columns into RDBMS libunits.

Suppose a compiled Java class is stored in the operating system file /home/java/bin/Agent.class.

Create a class libunit in schema username from file Agent.class as follows:

1. Connect to the database as SYSTEM and grant the user username the CREATE ANY DIRECTORY privilege.

2. Connect to the database as username and create a directory object on the server's file system:

   CREATE DIRECTORY Bfile_dir AS '/home/java/bin';
   

   The name of the directory object is an alias for the directory path leading to Agent.class.

3. Create the class libunit:

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

   The name of the libunit is derived from the name of the class.

Alternatively, you can use the command-line utility LoadJava. This uploads Java binaries and resources into a system-generated database table, then uses the CREATE JAVA statement to load the Java files into RDBMS libunits. You can upload Java files from file systems, Java IDEs, intranets, or the Internet.

Loading External C Procedures

When an application calls an external C procedure, Oracle Database or Oracle Listener starts the external procedure agent, extproc. Using the network connection established by Oracle Database or Oracle Listener, the application passes this information to extproc:

• Name of DLL or shared library

• Name of external procedure

• Any parameters for the external procedure

Then extproc loads the DLL or the shared library, runs the external procedure, and passes any values that the external procedure returns back to the application. The application and extproc must reside on the same computer.

extproc can call procedures in any library that complies with the calling standard used. For more information about the calling standard, see "CALLING STANDARD".

To configure your database to use external procedures that are written in C, or that can be called from C applications, you or your database administrator must follow these steps:

1. Define the C Procedures

2. Set Up the Environment

3. Identify the DLL

4. Publish the External Procedures

SET name=value (environment_variable_name value)

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

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

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';

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

AS LANGUAGE Clause for Java Class Methods

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

AS LANGUAGE Clause for External C Procedures

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.

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

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;

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

Example: Locating a Call Specification in an ADT Specification

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

Example: Locating a Call Specification in an ADT Body

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;

Example: Java with AUTHID

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

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 PLS_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 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
      LANGUAGE C
      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;

Specifying Data Types

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

OCICOLL

OCICOLL

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

External Data Type Mappings

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

Passing Parameters BY VALUE or BY REFERENCE

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.

Declaring Formal Parameters

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.

Overriding Default Data Type Mapping

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.

Specifying Properties

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

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 ('BOB', TO_DATE('14-MAY-85'));

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

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

NAME                           B_DATE    P.CALCAGE_ 
------------------------------ --------- ---------- 
BOB                            14-MAY-85          0 
JOHN                           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 DOUBLE PRECISION)
AS LANGUAGE C
   LIBRARY c_utils
   NAME "C_findRoot"
   PARAMETERS (
      x BY REFERENCE);

void C_findRoot(double *x);

void C_findRoot(double x);

CREATE OR REPLACE FUNCTION getNum_func (
   x IN REAL) 
RETURN PLS_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);

Preconditions for External Procedures

Before calling external procedures, consider the privileges, permissions, and synonyms that exist in the execution environment.

Topics:

• Privileges of External Procedures

• Managing Permissions

• Creating Synonyms for External Procedures

GRANT EXECUTE ON plsToJ_demoExternal_proc TO johndoe;

CREATE PUBLIC SYNONYM Rfac FOR johndoe.RecursiveFactorial;

CALL Statement Syntax

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 is identical to the that of an equivalent BEGIN END block.

Calling Java Class Methods

To call the J_calcFactorial class method published earlier:

1. Declare and initialize two SQL*Plus host variables:

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

2. Call J_calcFactorial:

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

Result:

Y
------
   120

Calling External C Procedures

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 previously, 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 ... 

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:

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 this 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 *) "password", (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)); 

  /* ------- subprogram 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..32,767. 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 PLS_INTEGER, 
   divisor  IN PLS_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 this 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 Database 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, you published external procedure plsTo_divide_proc. In this 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 Database 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. 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 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, just include ociextp.h.

Object Support for OCI Callbacks

To run object-related callbacks from your external procedures, the OCI environment in the extproc agent is fully initialized in object mode. You retrieve handles to this environment with the OCIExtProcGetEnv procedure.

The object runtime environment lets you use static and dynamic object support provided by OCI. To use static support, use the OTT to generate C structs for the appropriate object types, and then use conventional C code to access the object attributes.

For those objects whose types are unknown at external procedure creation time, an alternative, dynamic, way of accessing objects is first to call OCIDescribeAny to obtain attribute and method information about the type. Then, OCIObjectGetAttr and OCIObjectSetAttr can be called to retrieve and set attribute values.

Because the current external procedure model is stateless, OCIExtProcGetEnv must be called in every external procedure that wants to run callbacks, or call OCIExtProc. service routines. After every external procedure call, the callback mechanism is cleaned up and all OCI handles are freed.

Restrictions on Callbacks

With callbacks, this SQL statements and OCI subprograms are not supported:

• Transaction control statements such as COMMIT

• Data definition statements such as CREATE

• These object-oriented OCI subprograms:

  OCIObjectNew 
  OCIObjectPin 
  OCIObjectUnpin 
  OCIObjectPinCountReset 
  OCIObjectLock 
  OCIObjectMarkUpdate 
  OCIObjectUnmark 
  OCIObjectUnmarkByRef 
  OCIObjectAlwaysLatest 
  OCIObjectNotAlwaysLatest 
  OCIObjectMarkDeleteByRef 
  OCIObjectMarkDelete 
  OCIObjectFlush 
  OCIObjectFlushRefresh 
  OCIObjectGetTypeRef 
  OCIObjectGetObjectRef 
  OCIObjectExists 
  OCIObjectIsLocked 
  OCIObjectIsDirtied 
  OCIObjectIsLoaded 
  OCIObjectRefresh 
  OCIObjectPinTable 
  OCIObjectArrayPin 
  OCICacheFlush, 
  OCICacheFlushRefresh, 
  OCICacheRefresh 
  OCICacheUnpin 
  OCICacheFree 
  OCICacheUnmark 
  OCICacheGetObjects 
  OCICacheRegister 
  

• Polling-mode OCI subprograms such as OCIGetPieceInfo

• These OCI subprograms:

  OCIEnvInit
  OCIInitialize
  OCIPasswordChange
  OCIServerAttach
  OCIServerDetach
  OCISessionBegin
  OCISessionEnd
  OCISvcCtxToLda
  OCITransCommit
  OCITransDetach
  OCITransRollback
  OCITransStart
  

Also, with OCI subprogram OCIHandleAlloc, these 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 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.

Example: Calling an External Procedure

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.

Global Variables in External C Procedures

A global variable is declared outside of a function, and its value is shared by all functions of a program. Therefore, in external procedures, all functions in a DLL share the value of the global variable. Global variables are also used to store data that is intended to persist beyond the lifetime of a function. However, Oracle discourages the use of global variables for two reasons:

• Threading

  In the nonthreaded configuration of the agent process, one function is active at a time. For the multithreaded extproc agent, multiple functions can be active at the same time, and they might try to access the global variable concurrently, with unsuccessful results.

• DLL caching

  Suppose that function func1 tries to pass data to function func2 by storing the data in a global variable. After func1 completes, the DLL cache might be unloaded, causing all global variables to lose their values. Then, when func2 runs, the DLL is reloaded, and all global variables are initialized to 0.

Static Variables in External C Procedures

There are two types of static variables: external and internal. An external static variable is a special case of a global variable, so its usage is discouraged. Internal static variables are local to a particular function, but remain in existence rather than coming and going each time the function is activated. Therefore, they provide private, permanent storage within a single function. These variables are used to pass on data to subsequent calls to the same function. But, because of the DLL caching feature mentioned previously, the DLL might be unloaded and reloaded between calls, which means that the internal static variable loses its value.

When calling external procedures:

• Never write to IN parameters or overflow the capacity of OUT parameters. (PL/SQL does no run time checks for these error conditions.)

• Never read an OUT parameter or a function result.

• 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, then you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list.

• If you include the PARAMETERS clause, and if the external procedure is a function, then you must specify the parameter RETURN in the last position.

• For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause. Also, ensure that the data types 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, then you must set the length of the corresponding C parameter to zero.

Restrictions on External C Procedures

These restrictions apply to external procedures:

• This feature is available only on platforms that support DLLs.

• Only C procedures and procedures callable from C code are supported.

• External procedure callouts combined with distributed transactions is not supported.

• You cannot pass PL/SQL cursor variables or records to an external procedure. For records, use instances of object types instead.

• In the LIBRARY subclause, you cannot use a database link to specify a remote library.

• The maximum number of parameters that you can pass to a external procedure is 128. However, if you pass float or double parameters by value, then the maximum is less than 128. How much less depends on the number of such parameters and your operating system. To get a rough estimate, count each float or double passed by value as two parameters.