Managing Processes for External Procedures in Oracle 8i/9i - Page 2

November 27, 2002


Registering an External Routine

Before you call an External Routine, you must register it. Registration means, tell PLSQL:
  1. Where to find the Routine
  2. How to call the Routine
  3. What to pass to it
EXTERNAL LIBRARY library_name
   [NAME external_Routine_name]
   [LANGUAGE language_name]
   [CALLING STANDARD {C | PASCAL}]
   [WITH CONTEXT]
   [PARAMETERS (external_parameter[, external_prameter]...)];
where external_parameter stands for
{CONTEXT | {parameter_name | RETURN}[property][BY REF] [external_datatype]}

and property stands for
{INDICATOR | LENGTH | MAXLEN | CHARSETID | CHARSETFORM}


Using the WITH CONTEXT Clause

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 FUNCTION get_num ( x IN REAL) 
RETURN BINARY_INTEGER AS EXTERNAL
   LIBRARY c_utils
   NAME "c_get_num"
   LANGUAGE C 
   WITH CONTEXT
   PARAMETERS (
      CONTEXT,
      x BY REF,
      RETURN INDICATOR);
then the C prototype would be
int c_get_num(
   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, 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 Routine.


Using the PARAMETERS Clause

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

CREATE or REPLACE FUNCTION find_max( x IN BINARY_INTEGER,  y IN BINARY_INTEGER) 
RETURN BINARY_INTEGER AS
   EXTERNAL LIBRARY TEST_EP
   NAME "find_max"
   LANGUAGE C;
/
show errors;
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, you can provide more information using the PARAMETERS clause, which lets you specify:

  1. Non-default external datatypes
  2. The current and/or maximum length of a parameter
  3. Null/not null indicators for parameters
  4. Character set IDs and forms
  5. The positions of parameters in the list
  6. How IN parameters are passed (by value or by reference)
For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause. If you include the WITH CONTEXT clause, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. Also, if the external routine is a function, you must specify the parameter RETURN in the last position.

Example 1

The following statement creates PL/SQL standalone function GET_VAL that registers the C routine C_GET_VAL as an external function:

CREATE FUNCTION get_val
( x_val IN BINARY_INTEGER,
y_val IN BINARY_INTEGER,
image IN LONG RAW )
RETURN BINARY_INTEGER AS EXTERNAL LIBRARY c_utils
NAME "c_get_val"
LANGUAGE C;
Example 2

In the following example, external Routine C_FIND_ROOT expects a pointer as a parameter. Routine FIND_ROOT passes the parameter by reference using the BY REF phrase:

CREATE PROCEDURE FIND_ROOT ( x IN REAL ) AS
EXTERNAL LIBRARY c_utils
NAME "c_find_root"
PARAMETERS ( x BY REF );

How PL/SQL Calls an External Routine

  1. PL/SQL alerts the Listener process
  2. PLSQL in turn starts a session-specific agent named extproc
  3. Listener hands over control to extproc
  4. PLSQL passes name of the DLL or SO, the name of external Routine and any parameters to extproc
  5. Extproc invokes the disk
  6. It loads the specific DLL/SO in the memory for execution
  7. Extproc returns the results to PLSQL

Once the External Routine is complete, the extproc does not terminate, it remains active in the memory. The extproc is killed when the Oracle Session is terminated.

You should call an External Routine only when the computational benefits outweigh the cost.


Page 3: Advantages of External Routines









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers