4
Methods: Using C/C++ and Java

This chapter describes how to use C to implement the methods of a data cartridge. Methods are procedures and functions that define the operations permitted on data defined using the data cartridge.

This chapter focuses on issues related to developing and debugging external procedures, including:

External Procedures

PL/SQL is powerful language for database programming. However, because some methods can be complex, it may not be possible to code such a method optimally using PL/SQL. For example, a routine to perform numerical integration will probably run faster if it is implemented in C than if it is implemented in 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, called an external procedure, is stored in a shared library, registered with PL/SQL, and called from PL/SQL at runtime to perform special-purpose processing. Details on external procedures and their use can be found in the PL/SQL User's Guide and Reference.

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. Shared libraries already written and available in 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 numerical data.

Using Shared Libraries

A shared library is an operating system file, such as a Windows DLL or a Solaris shared object, that stores the coded implementation of external procedures. Access to the shared library from Oracle occurs by using an alias library, which is a schema object that represents the library within PL/SQL. For security, creation of an alias library requires DBA privileges. To create the alias library (such as DS_Lib in the following example), 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 a statement such as the following:

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

Note:
In Oracle8i, the absolute path to the shared library must be specified, because Oracle does not search operating system environment variables (such as LD_LIBRARY_PATH) to resolve the location of shared libraries from the alias library name.

This example creates the alias library schema object in the database, and hereafter you can refer to the shared library by the name DS_Lib from within PL/SQL.

Registering an External Procedure

To call an external procedure, you must not only tell PL/SQL the alias library in which to find the external procedure, but also how to call the procedure and what arguments to pass to it.

Earlier, the type DataStream was defined, and certain methods of type DataStream were defined by calling functions from a package DS_Package. Also, this package was specified. The following statement defines the body of this package (DS_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;

In the PACKAGE BODY declaration clause of this example, 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. For a description of the parameters that can accompany an EXTERNAL clause, see the PL/SQL User's Guide and Reference.

Note:
In Oracle8i, only C is supported as a language for external procedures. However, it is easy to call routines in other languages, such as C++, by dispatching from the server as C but coding the external procedures as extern "C". Thus, by creating C wrappers, you can easily call external procedures written in other languages.

The final part of the EXTERNAL clause in the example is the WITH CONTEXT specification. This means that 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 Oracle8i server, to potentially access more data in the same transaction context. The WITH CONTEXT clause is discussed in "Using the WITH CONTEXT Clause".

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 spawns (launches) a session-specific agent named extproc. The Listener hands over the connection to extproc. PL/SQL passes to extproc the name of the DLL, the name of the external procedure, and any parameters passed in by the caller.

Then, 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 4-1 shows the flow of control.

Figure 4-1 How an External Procedure is Called

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

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

See Also:
For information about administering extproc and external procedure calls, see the Oracle8i Administrator's Guide.

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. For example:

# Listener configuration file  
# This file is generated by stkconf.tsc  
 
CONNECT_TIMEOUT_LISTENER = 0  
 
LISTENER = (ADDRESS_LIST=  
 (ADDRESS=(PROTOCOL=ipc)(KEY=o8))  
 (ADDRESS=(PROTOCOL=tcp)(HOST=unix123)(PORT=1521))  
)  
 
SID_LIST_LISTENER = (SID_LIST=   
(SID_DESC=(SID_NAME=o8)(ORACLE_HOME=/rdbms/u01/app/oracle/product/8.0
.3))  
 
(SID_DESC=(SID_NAME=extproc)(ORACLE_HOME=/rdbms/u01/app/oracle/product/
8.0.3)(PROGRAM=extproc))  
)

This listener.ora example assumes the following:

The Oracle instance is called o8.
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 (network substrate configuration file) must also be updated to refer to the external procedure. For example:

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

This tnsnames.ora example 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.

For more information about configuring the listener.ora and tnsnames.ora files, see the Oracle8i Administrator's Guide.

Passing Parameters to an External Procedure

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

The set of PL/SQL datatypes does not correspond one-to-one with the set of C datatypes.
PL/SQL parameters can be null, whereas C parameters cannot. (Unlike C, PL/SQL includes the RDBMS concept of nullity.)
The external procedure might need the current length or maximum length of CHAR, LONG RAW, RAW, and VARCHAR2 parameters.
The external procedure might need character set information about CHAR, VARCHAR2, and CLOB parameters.
PL/SQL might need the current length, maximum length, or null status of values returned by the external procedure.

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

An example of parameter passing is shown in "Doing Callbacks", where the package function DS_Findmin(data CLOB) calls the C routine c_findmin and the CLOB argument is passed to the C routine as an OCILobLocator *.

Specifying Datatypes

You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. For guidance, see Table 4-1. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)

Table 4-1 Parameter Datatype Mappings

PL/SQL Type	Supported External Types	Default External Type
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

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.

To avoid errors when declaring C prototype parameters, refer to Table 4-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 CHAR, specify the datatype char * in your C prototype.

Table 4-2 External Datatype Mappings

External Datatype	IN, RETURN	IN by Ref, RETURN by Ref	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 **

Using the Parameters Clause

You can optionally use the PARAMETERS clause to pass additional information about PL/SQL formal parameters and function return values to an external procedure. You can also use this clause to reposition parameters.

See Also:
For more information about the PARAMETERS clause, see the PL/SQL User's Guide and Reference.

Using the WITH CONTEXT Clause

Once launched, an external procedure may need to access the database. For example, DS_Findmin does not copy the entire CLOB data over to c_findmin, because doing so would vastly increase the amount of stack that the C routine needs. Instead, the PL/SQL function just passes a LOB locator to the C routine, with the intent that the database will be re-accessed from C to read the actual LOB data.

When the C routine reads the data, it can use the OCI buffering and streaming interfaces associated with LOBs (see the Oracle Call Interface Programmer's Guide for details), so that only incremental amounts of stack are needed. Such re-access of the database from an external procedure is known as a callback.

To be able to call back to a database, you need to use the WITH CONTEXT clause to give the external procedure access to the database environment, service, and error handles. When an external procedure is called using WITH CONTEXT, the corresponding C routine automatically gets as its first parameter an argument of type OCIExtProcContext *. (The order of the parameters can be changed using the PARAMETERS clause.) You can use this context pointer to fetch the handles using the OCIExtProcGetEnv call, and then call back to the database. This procedure is shown in "Doing Callbacks".

OCIExtProcGetEnv

This service routine enables OCI callbacks to the database during an external procedure call. Use the OCI handles obtained by this function only for callbacks. If you use them for standard OCI calls, 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 procedure 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.

"Doing Callbacks" shows how OCIExtProcGetEnv might be used in callbacks. For a working example, see the script extproc.sql in the PL/SQL demo directory. (For the location of this directory, see your Oracle installation or user's guide.) This script 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/TIGER account, which must have CREATE LIBRARY privileges.

Doing Callbacks

An external procedure executing on the Oracle8i server can call the access function OCIExtProcGetEnv 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 procedures operate in the same user session and transaction context, so they have the same user privileges.

The following example is a version of c_findmin that is simplified to illustrate callbacks. The complete listing is available on the disk that is included with this kit.

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

Restrictions on Callbacks

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

Transaction control statements such as COMMIT
Data definition statements such as CREATE
Object-oriented OCI routines such as OCIRefClear
Polling-mode OCI routines such as OCIGetPieceInfo

All these OCI routines:

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

Also, with OCI routine OCIHandleAlloc, the following handle types are not supported:
```
OCI_HTYPE_SERVER 
OCI_HTYPE_SESSION 
OCI_HTYPE_SVCCTX 
OCI_HTYPE_TRANS
```

OCI Access Functions for External Procedures

When called from an external procedure, a service routine can raise exceptions, allocate memory, and get OCI handles for callbacks to the server. To use the functions, you must specify the WITH CONTEXT clause, which lets you pass a context structure to the external procedure. The context structure is declared in header file ociextp.h as follows:

typedef struct OCIExtProcContext OCIExtProcContext;

This section describes how service routines use the context information. For more information and examples of usage, see the chapter on external procedures in theOracle8i Application Developer's Guide - Fundamentals.

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 as soon as control returns to PL/SQL.

Note:
Do not use any other function to allocate or free memory.

The C prototype for this function 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.

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, the 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 error_number);

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.

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, len is zero. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.

Common Potential Errors

This section presents several kinds of errors you might make in running external procedures.

Calls to External Functions

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>

You may have specified the wrong path or wrong name for the DLL file, or you may have tried to use a DLL on a network mounted drive (a remote drive).

RPC Time Out

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>

This error might occur after you exit a debugger while debugging a shared library or DLL. Simply disconnect your client and reconnect to the database.

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 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 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 4-2

Using Package DEBUG_EXTPROC

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.

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.

Debugging C Code in DLLs on Windows NT Systems

If you are developing on a Windows NT system, you may perform the following additional actions to debug external procedures:

Invoke the Windows NT Task Manager (press Ctrl+Alt+Del.and select Task Manager).
In the Processes display, select ExtProc.exe.
Right click, and select Debug.
Select OK in the message box.

At this point, if you have built your DLL in a debug fashion with Microsoft Visual C++, Visual C++ is activated.
In the Visual C++ window, select Edit > Breakpoints.
Use the breakpoint identified in dbgextp.sql in the PL/SQL demo directory.

Guidelines for Using External Procedures with Data Cartridges

In future releases, extproc might be a multithreaded process. So, be sure to write thread-safe external procedures. That way, they will continue to run properly if extproc becomes multithreaded. In particular, avoid using static variables, which can be shared by routines running in separate threads. Otherwise, you might get unexpected results.

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 runtime 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 will 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 routine 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, make sure that the datatypes 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 REF.

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.

4 Methods: Using C/C++ and Java