Oracle8i Application Developer's Guide - Fundamentals
Release 8.1.5

A68003-01

Library

Product

Contents

Index

Prev Next

11
External Routines

The Need to Work with Multiple Languages

Oracle offers you the possibility of working in different languages:

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.

However, the choice may narrow depending on how your application needs to work with the Oracle ORDBMS:

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 but outside the address space of the server. Pro*COBOL and Pro*C are precompilers, and Visual Basic accesses Oracle via the OCI, which is implemented in C.


Note:

Java functionality is not available with this release.  


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.

Until Oracle 8.0, the Oracle RDBMS supported SQL and the stored procedure language PL/SQL. In Oracle 8.0, PL/SQL introduced external procedures, which allowed the capability of writing C functions as PL/SQL bodies. These C functions are callable from PL/SQL and SQL (via PL/SQL). With 8.1, Oracle provides a special-purpose interface, the call specification, that lets you call external routines 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 routine can be written in a language other than Java or C and still be usable by SQL or PL/SQL, provided that is callable by C. Therefore, if you have a candidate C++ routine, you would use a C++ extern "C" statement in that routine to make it callable by C.

This means that the strengths and capabilities of different languages are available to you, irrespective of your programmatic environment: You are not restricted to one language with its inherent limitations. The use of external routines promotes reusability and modularity because you can deploy specific languages for specific purposes.

What is an External Routine?

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

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

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

External routines let you:

The Call Specification

Until now, you published an external routine to Oracle via an AS EXTERNAL clause in a PL/SQL wrapper. This wrapper defined the mapping to, and allowed the calling of, external C routines. Oracle 8.1 introduces call specifications, which include the AS EXTERNAL wrapper as a subset of the new AS LANGUAGE clause. AS LANGUAGE call specifications allow the publishing of external C routines, as before, but also Java class methods.


Note:

Call specifications also allow you to publish with the AS EXTERNAL clause, introduced in Oracle 8.0. For new applications, however, you should use the AS LANGUAGE clause.  


In general, call specifications enable:

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

Loading External Routines

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

See Also:

Oracle8i Java Stored Procedures Developer's Guide

For help in creating a DLL, look in the RDBMS subdirectory /public, where a template makefile can be found.  

Loading Java Class Methods

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

Suppose a compiled Java class is stored in the following OS file:

/home/java/bin/Agent.class

Creating a class libunit in schema scott from file Agent.class requires two steps: First, create a directory object on the server's file system. The name of the directory object is an alias for the directory path leading to Agent.class.

To create the directory object, you must grant user scott the CREATE ANY DIRECTORY privilege, then execute the CREATE DIRECTORY statement, as follows:

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

Now, you are ready to create the class libunit, as follows:

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 OS file systems, Java IDEs, intranets, or the Internet.

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

Loading External C Routines

In order to set up to use external routines written in C, or callable by C, you and your DBA take the following steps:


Note:

This feature is available only on platforms that support DLLs or dynamically loadable shared libraries such as Solaris .so libraries.  


1. Set Up the Environment

Your DBA sets up the environment for calling external routines by adding entries to the files tnsname.ora and listener.ora and by starting a Listener process exclusively for external routines.

See Also:

Oracle8i Administrator's Guide.  

The Listener sets a few required environment variables (such as ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH) for extproc. Otherwise, it provides extproc with a "clean" environment. The environment variables set for extproc are independent of those set for the client, server, and Listener. Therefore, external routines, which run in the extproc process, cannot read environment variables set for the client, server, or Listener process.


Note:

It is possible for you to set and read environment variables themselves by using the standard C routines setenv() and getenv(), respectively. Environment variables, set this way, are specific to the extproc 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 machine.  


2. Identify the DLL

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

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

CREATE LIBRARY library_name {IS | AS} 'file_path';

You must specify the full path to the DLL, because the linker cannot resolve references to 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';

3. Designate the External Routine

You find or write a new external C routine, then add it to the DLL, or simply designate a routine already in the DLL.

External C routines are loaded into DLLs. After creating and including your external routine within a DLL, you create the alias library which represents the DLL, like this:

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

Publishing an External Routine

Oracle can only use external routines that have been published. Publishing is accomplished with a call specification, which maps names, parameter types, and return types for your Java class method or C external routine 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 call specification syntax, which follows the normal CREATE OR REPLACE syntax for a procedure, function, package specification, package body, type specification, or type body, is:

{IS | AS} LANGUAGE {C | JAVA}


Note:

Oracle uses a PL/SQL variant of the ANSI SQL92 External Procedure, but replaces the ANSI keyword AS EXTERNAL with this call specification syntax. This new syntax, introduced for Java class methods, has now been extended to C routines.

Oracle8i Java Stored Procedures Developer's Guide  


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 routine, and external_parameter stands for:

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

property stands for:

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


Note:

Unlike Java, C doesn't understand SQL types; therefore, the syntax is more intricate  


The AS LANGUAGE Clause for Java Class Methods

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

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

The AS LANGUAGE Clause for External C Routines

The following subclauses tell PL/SQL where to locate the external C routine, how to call it, and what to pass to it. Only the LIBRARY subclause is required.

LIBRARY

Specifies a local alias library. (You cannot use a database link to specify a remote library.) The library name is a PL/SQL identifier. Therefore, if you enclose the name in double quotes, then it becomes case sensitive. (By default, the name is stored in upper case.) You must have EXECUTE privileges on the alias library.

NAME

Specifies the external C routine to be called. If you enclose the routine name in double quotes, then it becomes case sensitive. (By default, the name is stored in upper case.) If you omit this subclause, then the routine name defaults to the upper-case name of the PL/SQL subprogram.


Note:

The terms LANGUAGE and CALLING STANDARD apply only to the superseded AS EXTERNAL clause.  


LANGUAGE

Specifies the third-generation language in which the external routine was written. If you omit this subclause, then the language name defaults to C.

CALLING STANDARD

Specifies the Windows NT calling standard (C or Pascal) under which the external routine was compiled. (Under the Pascal Calling Standard, arguments are reversed on the stack, and the called function must pop the stack.) If you omit this subclause, then the calling standard defaults to C.

WITH CONTEXT

Specifies that a context pointer will be passed to the external routine. The context data structure is opaque to the external routine but is available to service routines called by the external routine.

PARAMETERS

Specifies the positions and datatypes of parameters passed to the external routine. It can also specify parameter properties, such as current length and maximum length, and the preferred parameter passing method (by value or by reference).

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

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

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 Routines

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 routines, call specifications can be specified in any of the following locations:

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.


Note:

In the following examples, the AUTHID and SQL_NAME_RESOLVE clauses may or may not be required to fully stipulate a call specification. See the Invoker-rights section of this manual for rules on their placement and defaults.  


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


Note:

You may need to set up the following data structures for certain examples to work:

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 standalone PL/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 routine in a standalone PL/SQL program, in which the AUTHID clause is optional. This maintains compatibility with the external procedures of Oracle 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 Java Class Methods with Call Specifications

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

Passing Parameters to External C Routines with Call Specifications

Call specifications allows a mapping between PL/SQL and C datatypes. Datatype mappings are shown below.

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

In the following sections, you learn how to specify a parameter list that deals with these circumstances.


Note:

The maximum number of parameters that you can pass to a C external routine 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.  


Specifying Datatypes

Do not pass parameters to an external routine directly. Instead, pass them to the PL/SQL subprogram that published the external routine. Therefore, you must specify PL/SQL datatypes for the parameters. Each PL/SQL datatype maps to a default external datatype, as shown in Table 11-1.

Table 11-1 Parameter Datatype Mappings
PL/SQL Type   Supported External Types   Default External Type  
BINARY_INTEGER
BOOLEAN
PLS_INTEGER 
 
[UNSIGNED] CHAR
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
SB1, SB2, SB4
UB1, UB2, UB4
SIZE_T
 
INT
 
NATURAL 
NATURALN 
POSITIVE 
POSITIVEN 
SIGNTYPE
 
[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
DEC
DECIMAL
INT
INTEGER
NUMERIC
SMALLINT
 
[UNSIGNED] CHAR
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
SB1, SB2, SB4
UB1, UB2, UB4
SIZE_T
OCINUMBER
 
OCINUMBER
 
DATE
 
OCIDATE
 
OCIDATE
 
composite object types: 
ADTs
 
dvoid
 
dvoid
 
composite object types: 
collections (varrays, 
nested tables, index-by 
tables
 
OCICOLL
 
OCICOLL
 

External Datatype Mappings

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

Table 11-2 External Datatype Mappings
  Datatypes Used in C Prototype  
External Datatype   IN, RETURN   IN by REFERENCE, RETURN by REFERENCE   IN OUT, OUT  
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
 
dvoid*
 
dvoid*
 
dvoid*
 

Composite object 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 datatype, but must use the datatypes generated by Oracle's Object Type Translator (OTT). The optional TDO argument for INDICATOR, and for composite objects, in general, has the C datatype, OCIType *.

OCICOLL for REF and collection arguments is optional and only exists for the sake of completeness. You can not map REFs or collections onto any other datatype and vice versa.

BY VALUE/REFERENCE for IN and IN OUT Parameter Modes

If you specify BY VALUE, then scalar IN and RETURN arguments are passed by value (which is also the default). Alternatively, you may 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 datatypes that are, by default, passed by value. This is true for IN, and RETURN arguments of the following 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 the above list, all IN OUT arguments, and all OUT arguments are passed by reference.

The PARAMETERS Clause

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


Note:

You may need to set up the following data structures for certain examples to work:

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 datatype (which maps to the default external datatype). That might be all the information the external routine needs. If not, then you can provide more information using the PARAMETERS clause, which lets you specify the following:

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

Overriding Default Datatype Mapping

In some cases, you can use the PARAMETERS clause to override the default datatype mappings. For example, you can re-map the PL/SQL datatype BOOLEAN from external datatype INT to external datatype CHAR.

Specifying Properties

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

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

The following table shows the allowed and the default external datatypes, PL/SQL datatypes, and PL/SQL parameter modes allowed for a given property. Notice that MAXLEN (used to specify data returned from C back to PL/SQL) cannot be applied to an IN parameter.

Table 11-3 Property Datatype Mappings
Property   C Parameter   PL/SQL Parameter  
  Allowed External Types   Default External Type   Allowed Types   Allowed Modes   Default Passing Method  
INDICATOR
 
SHORT
INT
LONG
 
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 the following example, the PARAMETERS clause specifies properties for the PL/SQL formal parameters and function result:

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

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], as well as the INDICATOR for the function result in the PARAMETERS clause. The parameter RETURN corresponds to the C function identifier, which stores the result value.

INDICATOR

An INDICATOR is a parameter whose value indicates whether or not another parameter is NULL. PL/SQL does not need indicators, because the RDBMS concept of nullity is built into the language. However, an external routine might need to know if a parameter or function result is NULL. Also, an external routine might need to signal the server that a returned value is actually a NULL, and should be treated accordingly.

In such cases, you can use the property INDICATOR to associate an indicator with a formal parameter. If the PL/SQL subprogram is a function, then you can also associate an indicator with the function result, as shown above.

To check the value of an indicator, you can use the constants OCI_IND_NULL and OCI_IND_NOTNULL. If the indicator equals OCI_IND_NULL, then the associated parameter or function result is NULL. If the indicator equals OCI_IND_NOTNULL, then the parameter or function result is not NULL.

For IN parameters, which are inherently read-only, INDICATOR is passed by value (unless you specify BY REFERENCE) and is read-only (even if you specify BY REFERENCE). For OUT, IN OUT, and RETURN parameters, INDICATOR is passed by reference by default.

The INDICATOR can also have a STRUCT or TDO option. Because specifying INDICATOR as a property of an object is not supported, and because arguments of objects have complete indicator structs instead of INDICATOR scalars, you must specify this by using the STRUCT option. You must use the type descriptor object (TDO) option for composite objects and collections,

LENGTH and MAXLEN

In PL/SQL, there is no standard way to indicate the length of a RAW or string parameter. However, in many cases, you want to pass the length of such a parameter to and from an external routine. Using the properties LENGTH and MAXLEN, you can specify parameters that store the current length and maximum length of a formal parameter.


Note:

With a parameter of type RAW or LONG RAW, you must use the property LENGTH. Also, if that parameter is IN OUT and NULL or OUT and NULL, then you must set the length of the corresponding C parameter to zero.  


For IN parameters, LENGTH is passed by value (unless you specify BY REFERENCE) and is read-only. For OUT, IN OUT, and RETURN parameters, LENGTH is passed by reference.

As mentioned above, MAXLEN does not apply to IN parameters. For OUT, IN OUT, and RETURN parameters, MAXLEN is passed by reference and is read-only.

CHARSETID and CHARSETFORM

Oracle provides national language support, which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments.

The properties CHARSETID and CHARSETFORM identify the non-default character set from which the character data being passed was formed. With CHAR, CLOB, and VARCHAR2 parameters, you can use CHARSETID and CHARSETFORM to pass the character set ID and form to the external routine.

For IN parameters, CHARSETID and CHARSETFORM are passed by value (unless you specify BY REFERENCE) and are read-only (even if you specify BY REFERENCE). For OUT, IN OUT, and RETURN parameters, CHARSETID and CHARSETFORM are passed by reference and are read-only.

The OCI attribute names for these properties are OCI_ATTR_CHARSET_ID and OCI_ATTR_CHARSET_FORM.

See Also:

For more information about using NLS data with the OCI, see Oracle Call Interface Programmer's Guide and the Oracle8i National Language Support Guide.  

Repositioning Parameters

Remember, each formal parameter of the external routine must have a corresponding parameter in the PARAMETERS clause. Their positions can differ, because PL/SQL associates them by name, not by position. However, the PARAMETERS clause and the C prototype for the external routine must have the same number of parameters, and they must be in the same order.

Using SELF

SELF is the always-present argument of an object type's member function or procedure, namely the object instance itself. In most cases, this argument is implicit and is not listed in the argument list of the PL/SQL procedure. However, SELF must be explicitly specified as an argument of the PARAMETERS clause.

For example, assume that a user wants to create a Person object, consisting of a person's name and date of birth, and then further a table of this object type. The user would eventually like to determine the age of each Person in this table.


Note:

You may need to set up data structures similar to the following for certain examples to work:

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

This example is only for Solaris; other libraries and include paths might be needed for other platforms.  


In SQL*Plus, the Person object type can be created by:

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

Normally, the member function would be implemented in PL/SQL, but for this example, we make it an external procedure. To realize this, the body of the member function is declared as follows:

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;

Notice that the calcAge_func member function doesn't take any arguments, but only returns a number. A member function is always invoked on an instance of the associated object type. The object instance itself always is an implicit argument of the member function. To refer to the implicit argument, the SELF keyword is used. This is incorporated into the external procedure syntax by supporting references to SELF in the parameters clause.

Now the matching table is created and populated.

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

Finally, we retrieve the information of interest from the table.

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
 

Sample C code, implementing the "external" member function, and the Object-Type-Translator (OTT)-generated struct definitions are included below.

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

Passing Parameters by Reference

In C, you can pass IN scalar parameters by value (the value of the parameter is passed) or by reference (a pointer to the value is passed). When an external routine expects a pointer to a scalar, specify BY REFERENCE phrase to pass the parameter by reference:

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

In this case, the C prototype would be:

void C_findRoot(float *x);

This is rather than the default, which would be used when there is no PARAMETERS clause:

void C_findRoot(float x);

WITH CONTEXT

By including the WITH CONTEXT clause, you can give an external routine access to information about parameters, exceptions, memory allocation, and the user environment. The WITH CONTEXT clause specifies that a context pointer will be passed to the external routine. For example, if you write the following PL/SQL function:

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

Then, the C prototype would be:

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

The context data structure is opaque to the external routine; but, is available to service routines called by the external routine.

If you also include the PARAMETERS clause, then you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, then the context pointer is the first parameter passed to the external routine.

Inter-Language Parameter Mode Mappings

PL/SQL supports the IN, IN OUT, and OUT parameter modes, as well as the RETURN clause for routines returning values.

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

Rules for PL/SQL and C Parameter Modes are listed above.

Executing External Routines: the CALL Statement

Now that your Java class method, or external C routine, has been published, you are ready to invoke it.

Do not call an external routine directly. Instead, call the PL/SQL subprogram that published the external routine. Such calls, which you code in the usual way, can appear in the following:

Although the CALL statement, described below, is confined to SELECTs, it can appear in either the WHERE clause or the SELECT list.


Note:

To call a packaged function from SQL statements, you must use the pragma RESTRICT_REFERENCES, which asserts the purity level of the function (the extent to which the function is free of side effects). PL/SQL cannot check the purity level of the corresponding external routine. Therefore, make sure that the routine does not violate the pragma. Otherwise, you might get unexpected results.  


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 11-1 shows how Oracle8 and external routines interact.

Figure 11-1 Oracle8 and External Routines


Preliminaries

Before you call your external routine, you might want to make sure you understand the execution environment. Specifically, you might be interested in privileges, permissions, and synonyms.

Privileges

When external routines are called via CALL specification's, they execute with definer's privileges, rather than with the privileges of their invoker.

An invoker's-privileges program is not bound to a particular schema. It executes at the calling site and accesses database items (such as tables and views) with the caller's visibility and permissions. However, a definer's privileges program is bound to the schema in which it is defined. It executes at the defining site, in the definer's schema, and accesses database items with the definer's visibility and permissions.

Managing Permissions


Note:

You may need to set up the following data structures for certain examples to work:

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

To call external routines, a user must have the EXECUTE privilege on the call specification and on any resources used by the routine.

In SQL*Plus, you can use the GRANT and REVOKE data control statements to manage permissions. For example:

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;


See Also:

 

Creating Synonyms

For convenience, you or your DBA can create synonyms for external routines using the CREATE [PUBLIC] SYNONYM statement. In the example below, your DBA creates a public synonym, which is accessible to all users. If PUBLIC is not specified, then the synonym is private and accessible only within its schema.

CREATE PUBLIC SYNONYM Rfac FOR Scott.RecursiveFactorial;

CALL Statement Syntax

Invoke the external routine by means of the SQL CALL statement. You can execute the CALL statement interactively from SQL*Plus. The syntax is:

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

This is essentially the same as executing a routine foo() using a SQL statement of the form "SELECT foo(...) FROM dual," except that the overhead associated with performing the SELECT is not incurred.

For example, here is an anonymous PL/SQL block which uses dynamic SQL to call plsToC_demoExternal_proc, which we published above. PL/SQL passes three parameters to the external C routine 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.


Note:

CALL is the only SQL statement that cannot be put, by itself, in a PL/SQL BEGIN...END block. It can be part of an EXECUTE IMMEDIATE statement within a BEGIN...END block.  


Calling Java Class Methods

Here is how you would call the J_calcFactorial class method published earlier. First, declare and initialize two SQL*Plus host variables, as follows:

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

Now, call J_calcFactorial:

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

The result:

Y
------
   120

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

Calling External C Routines

To call an external C routine, PL/SQL must know in which DLL it resides. To do this, the PL/SQL engine looks in the data dictionary for the library alias mentioned in the AS LANGUAGE clause. Oracle looks for the filename associated with the DLL contained in that library.

Next, PL/SQL alerts a Listener process which, in turn, spawns a session-specific agent named extproc. The Listener hands over the connection to extproc, and PL/SQL passes to extproc the name of the DLL, the name of the external routine, and any parameters.

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


Note:

Although some DLL caching takes place, there is no guarantee that your DLL will remain in the cache; therefore, do not store global variables in your DLL.  


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


Note:

The Listener, using the information in the tnsnames.ora and listener.ora files, must start extproc on the machine that runs the Oracle server. Starting extproc on a different machine is not supported.  


Here, we call PL/SQL function plsCallsCdivisor_func, which we published above, from an anonymous block. PL/SQL passes the two integer parameters to external function Cdivisor_func, which returns their greatest common divisor.

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

Errors and Exceptions

Generic Compile Time Call specification Errors

The PL/SQL compiler raises compile time errors if the following conditions are detected in the syntax:

Java Exception Handling

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

C Exception Handling

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

Using Service Routines with External C Routines

When called from an external routine, 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 routine. The context structure is declared in header file ociextp.h as follows:

typedef struct OCIExtProcContext OCIExtProcContext;


Note:

ociextp.h is located in $ORACLE_HOME/plsql/public on UNIX.  


OCIExtProcAllocCallMemory

This service routine allocates n bytes of memory for the duration of the external routine call. Any memory allocated by the function is freed automatically as soon as control returns to PL/SQL.


Note:

The external routine does not need to (and should not) call the C function free() to free memory allocated by this service routine as this is handled automatically.  


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:


Note:

You may need to set up data structures similar to the following for certain examples to work:

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, then 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 */
  (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 error number in the range 1...32767. After doing any necessary cleanup, your external 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 error number. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.

In SQL*Plus, suppose you publish external routine 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 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 routine plsTo_divide_proc. In the example below, 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 Routines

OCIExtProcGetEnv

This service routine enables OCI callbacks to the database during an external routine call. It is only used for callbacks, and, furthermore, it is the only callback routine used. If you use the OCI handles obtained by this function for standard OCI calls, then the handles establish a new connection to the database and cannot be used for callbacks in the same transaction. In other words, during an external routine call, you can use OCI handles for callbacks or a new connection but not for both.

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 routines and Java class methods can call-back to the database to do SQL operations. For a working example, see "Demo Program" .

Java exceptions:

See Also:

Oracle8i Java Stored Procedures Developer's Guide  


Note:

Callbacks are not necessarily a same-session phenomenon; you may execute an SQL statement in a different session via OCIlogon.  


An external C routine executing on the Oracle server 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. Moreover, callbacks and external routines 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 routine 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.

Object Support for OCI Callbacks

To execute object-related callbacks from your external routines, the OCI environment in the extproc agent is now fully initialized in object mode. You retrieve handles to this environment with the OCIExtProcGetEnv() routine.

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

For those objects whose types are unknown at external procedure creation time, an alternative, dynamic, way of accessing objects is first to invoke 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 routine model is stateless, OCIExtProcGetEnv() must be called in every external routine that wants to execute callbacks, or invoke OCIExtProc...() service routines. After every external routine invocation, the callback mechanism is cleaned up and all OCI handles are freed.

Restrictions on Callbacks

With callbacks, the following SQL commands and OCI routines are not supported:

Also, with OCI routine OCIHandleAlloc, the following handle types are not supported:

OCI_HTYPE_SERVER 
OCI_HTYPE_SESSION 
OCI_HTYPE_SVCCTX 
OCI_HTYPE_TRANS

Debugging External Routines

See Also:

Oracle8i Java Stored Procedures Developer's Guide  

Usually, when an external routine 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 datatype. For example, to pass an OUT parameter of type REAL, you must specify float *. Specifying float, double *, or any other C datatype will result in a mismatch.

In such cases, you might get:

lost RPC connection to external routine agent

This error, which means that agent extproc terminated abnormally because the external routine caused a core dump. To avoid errors when declaring C prototype parameters, refer to the tables above.

Using Package DEBUG_EXTPROC

To help you debug external routines, 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 Installation or User's Guide.)

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


Note:

DEBUG_EXTPROC works only on platforms with debuggers that can attach to a running process.  


Demo Program

Also in the PL/SQL demo directory is the script extproc.sql, which demonstrates the calling of an external routine. The companion file extproc.c contains the C source code for the external routine.

To run the demo, follow the instructions in extproc.sql. You must use the SCOTT/TIGER account, which must have CREATE LIBRARY privileges.

Guidelines for External C Routines

Handling Global And Static Variables

Global Variables

A global variable is declared outside of a function, and its value is shared by all functions of a program. In case of external routines, this means that all functions in a DLL share the value of the global. The usage of global variables is discouraged for two reasons:

Static Variables

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 for the above two reasons. 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 invocation of the same function. But, because of the DLL caching feature mentioned above, the DLL might be unloaded and reloaded between invocations, which means that the internal static variable would lose its value.

See Also:

For help in creating a dynamic link library, look in the RDBMS subdirectory /public, where a template makefile can be found.  

Call specification and CALLing Guidelines

When calling external routines:

Restrictions on External C Routines

Currently, the following restrictions apply to external routines:




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index