5 Implementing Data Cartridges in C, C++, and Java

Using External Procedures

PL/SQL is a powerful language for database programming, but some methods are too complex to code optimally in PL/SQL. For example, a routine to perform numeric integration probably runs faster if it is implemented in C rather than PL/SQL.

To support such special-purpose processing, PL/SQL provides an interface for calling routines written in other languages. This makes the strengths and capabilities of 3GLs, like C, available through calls from a database server. Such a 3GL routine is called an external procedure; it is stored in a shared library, registered with PL/SQL, and called from PL/SQL at run time.

External procedures are an important tool for data cartridge developers. They can be used not only to write fast, efficient, computation-intensive routines for cartridge types, but also to integrate existing code with the database as data cartridges. Existing shared libraries from other languages, such as a Windows NT DLL with C routines to perform format conversions for audio files, can be called directly from a method in a type implemented by an audio cartridge. Similarly, you can use external procedures to process signals, drive devices, analyze data streams, render graphics, or process numeric data.

Using Shared Libraries

A shared library is an operating system file, such as a Windows DLL or a Solaris shared object, which stores the coded implementation of external procedures. You can access the shared library from Oracle by using an alias library, which is a schema object that represents the library within PL/SQL. For security reasons, you need DBA privileges to create an alias library.

To create the alias library, you must decide on the operating system location for the library, log in as a DBA or as a user with the CREATE LIBRARY privilege, and then enter the statement in Example 5-1. This creates the alias library schema object in the database. After the alias library is created, you can refer to the shared library by the name DS_Lib from PL/SQL. Example 5-1 specifies an absolute path for the library.

CREATE OR REPLACE LIBRARY DS_Lib AS  
     '/data_cartridge_dir/libdatastream.so';

If you have copies of the library on multiple systems, to support distributed execution of external procedures by designated or dedicated agents, use an environment variable to specify the location of the libraries more generally, as in Example 5-2. This statement uses the environment variable ${DS_LIB_HOME} to specify a common point of reference or root directory from which the library can be found on all systems. The string following the AGENT keyword specifies the agent (actually, a database link) that is used to run any external procedure declared to be in library DS_Lib.

CREATE OR REPLACE LIBRARY DS_Lib AS 
  '${DS_LIB_HOME}/libdatastream.so' AGENT 'agent_link';

Oracle Database 12c Release 1 (12.1) introduces two extensions to the CREATE LIBRARY syntax: an additional optional CREDENTIAL option and a DIRECTORY object option. The credential option specifies the credentials of the operating system user that the extproc agent impersonates when running an external subprogram that specifies the library. The directory object option specifies the directory where the shared library can be found.

Example 5-3 specifies a directory object option instead of a full path string; it also specifies an optional credential argument. When an external procedure call through the extproc process loads the PL/SQL library, extproc can authenticate and 'impersonate' on behalf of the defined smith_credential credential.

CREATE OR REPLACE LIBRARY DS_Lib AS 
  'libdatastream.so' IN data_cartridge_dir AGENT 'sales.hq.example.com' 
  CREDENTIAL smith_credential;

Registering an External Procedure

To call an external procedure, you must not only instruct PL/SQL regarding the alias library where the external procedure is defined, but also how to call this procedure and what arguments to pass to it.

The DataStream type was defined in Example 3-1, and Example 3-2 defined methods o f DataStream by calling functions from the DS_Package package, which is specified in Example 4-9. Example 5-4 defines the body of this package.

CREATE OR REPLACE PACKAGE BODY DS_Package AS 
     FUNCTION DS_Findmin(data  CLOB) RETURN PLS_INTEGER IS EXTERNAL 
     NAME "c_findmin" LIBRARY DS_Lib LANGUAGE C WITH CONTEXT; 
     FUNCTION DS_Findmax(data CLOB) RETURN PLS_INTEGER IS EXTERNAL 
     NAME "c_findmax" LIBRARY DS_Lib LANGUAGE C WITH CONTEXT; 
   END;

Note that in the PACKAGE BODY declaration clause, the package functions are tied to external procedures in a shared library. The EXTERNAL clause in the function declaration registers information about the external procedure, such as its name (found after the NAME keyword), its location (which must be an alias library, following the LIBRARY keyword), the language in which the external procedure is written (following the LANGUAGE keyword), and so on.

The final part of the EXTERNAL clause in the example is the WITH CONTEXT specification. Here, a context pointer is passed to the external procedure. The context pointer is opaque to the external procedure, but is available so that the external procedure can call back to the Oracle server, to potentially access more data in the same transaction context.

Although the example describes external procedure calls from object type methods, a data cartridge can use external procedures from a variety of other places in PL/SQL. External procedure calls can appear in:

• Anonymous blocks

• Standalone and packaged subprograms

• Methods of an object type

• Database triggers

• SQL statements (calls to packaged functions only)

How PL/SQL Calls an External Procedure

To call an external procedure, PL/SQL must know the DLL or shared library in which the procedure resides. PL/SQL looks up the alias library in the EXTERNAL clause of the subprogram that registered the external procedure. The data dictionary is used to determine the actual path to the operating system shared library or DLL.

PL/SQL alerts a Listener process, which in turn starts a session-specific agent. Unless some other particular agent has been designated either in the CREATE LIBRARY statement for the procedure's specified library or in the agent argument of the CREATE PROCEDURE statement, the default agent extproc is launched. The Listener hands over the connection to the agent. PL/SQL passes the agent the name of the DLL, the name of the external procedure, and any parameters passed in by the caller. The rest of this account assumes that the agent launched is the default agent extproc.

After receiving the name of the DLL and the external procedure, extproc loads the DLL and runs the external procedure. 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 procedure. Figure 5-1 shows the flow of control.

Figure 5-1 Calling an External Procedure

Description of "Figure 5-1 Calling an External Procedure"

After the external procedure completes, extproc remains active throughout your Oracle session. Thus, you incur the cost of spawning extproc only one time, no matter how many calls you make. Still, you should call an external procedure only when the computational benefits outweigh the cost. When you log off, extproc is killed.

Note that the Listener must start extproc on the system that runs the Oracle server. Starting extproc on a different system is not supported.

Configuration Files for External Procedures

The configuration files listener.ora and tnsnames.ora must have appropriate entries, so that the Listener can dispatch the external procedures.

The Listener configuration file listener.ora must have a SID_DESC entry for the external procedure, as demonstrated in Example 5-5.

# Listener configuration file  
# This file is generated by stkconf.tsc  
 
CONNECT_TIMEOUT_LISTENER = 0  
 
LISTENER = (ADDRESS_LIST=  
  (ADDRESS=(PROTOCOL=ipc)(KEY=o10))  
  (ADDRESS=(PROTOCOL=tcp)(HOST=unix123)(PORT=1521))  
)

SID_LIST_LISTENER = (SID_LIST=   
  SID_DESC=(SID_NAME=o10)(ORACLE_HOME=/rdbms/u01/app/oracle/product/11.2.0.1.0) 
  SID_DESC=(SID_NAME=extproc)
           (ORACLE_HOME=/rdbms/u01/app/oracle/product/11.2.0.1.0)
  (PROGRAM=extproc))  

Example 5-5 assumes the following:

• The Oracle instance is called o10.

• The system or node on which the Oracle server runs is named unix123.

• The installation directory for the Oracle server is /rdbms/u01.

• The port number for Oracle TCP/IP communication is the default Listener port 1521.

The tnsnames.ora file is the network substrate configuration file, and it must also be updated to refer to the external procedure, as demonstrated in Example 5-6.

o10 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unix123)(PORT=1521))
  (CONNECT_DATA=(SID=o10)))
extproc_connection_data = (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=o10))
  CONNECT_DATA=(SID=extproc)))

Example 5-6 assumes that IPC mechanisms are used to communicate with the external procedure. You can also use, for example, TCP/IP for communication, in which case the PROTOCOL parameter must be set to tcp.

BINARY_INTEGER,
BOOLEAN,
PLS_INTEGER 

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

INT

NATURAL, NATURALN, 
POSITIVE,
POSITIVEN, 
SIGNTYPE

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

UNSIGNED INT

FLOAT, REAL

FLOAT

FLOAT

DOUBLE PRECISION

DOUBLE

DOUBLE

CHAR, CHARACTER,
LONG, ROWID, VARCHAR, VARCHAR2 

STRING

STRING

LONG RAW, RAW

RAW

RAW

BFILE, BLOB, CLOB

OCILOBLOCATOR

OCILOBLOCATOR

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

Doing Callbacks

An external procedure that runs on the Oracle server can call the access function OCIExtProcGetEnv() to obtain the 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 procedures operate in the same user session and transaction context, so they have the same user privileges.

Example 5-7 is a version of c_findmin that is simplified to illustrate callbacks.

Static  OCIEnv   *envhp;
Static  OCISvcCtx  *svchp;
Static OCIError   *errhp;
Int   c_findmin (OCIExtProcContext *ctx, OCILobLocator  *lobl) {
sword  retval;
retval = OCIExtProcGetEnv (ctx, &envhp, &svchp, &errhp);
if ((retval != OCI_SUCCESS) && (retval !=  OCI_SUCCESS_WITH_INFO))
   exit(-1);
   /* Use lobl to read the CLOB, compute the minimum, and store the value
       in retval. */
return retval;
}

Common Potential Errors

This section presents several kinds of errors you might encounter when running external procedures.

Can't Find DLL
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "<name>", line <number>
ORA-06512: at "<name>", line <number>
ORA-06512: at line <number>

ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "<name>", line <number>
ORA-06512: at "<name>", line <number>
ORA-06512: at line <number>

Debugging External Procedures

Usually, when an external procedure fails, its C prototype is faulty. That is, 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 a lost RPC connection to external procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, refer to Table 5-2.

Guidelines for Using External Procedures with Data Cartridges

Make sure to write thread-safe external procedures. In particular, avoid using static variables, which can be shared by routines running in separate threads.

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

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. Likewise, never read an OUT parameter or a function result. Also, always assign a value to IN OUT and OUT parameters and to function results. Otherwise, your external procedure does not return successfully.

If you include the WITH CONTEXT and PARAMETERS clauses, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, the context pointer is the first parameter passed to the external procedure.

If you include the PARAMETERS clause and the external procedure is a function, you must specify the parameter RETURN (not RETURN property) 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.

A parameter for which you specify INDICATOR or LENGTH has the same parameter mode as the corresponding formal parameter. However, a parameter for which you specify MAXLEN, CHARSETID, or CHARSETFORM is always treated like an IN parameter, even if you also specify BY REFERENCE.

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

Java Methods

To use Java Data Cartridges, it is important that you know how to load Java class definitions, about how to call stored procedures, and about context management. Information on ODCI classes can also be found in Chapter 18, "Cartridge Services Using C, C++ and Java" of this manual.