Managing Processes for External Procedures in Oracle 8i/9i

Wednesday Nov 27th 2002 by DatabaseJournal.com Staff
Share:

In his first article, Ajay Gurshahani details the use of External Routines in Oracle 8i/9i, which are helpful in eliminating the need for using CGIs and also make it possible to create dynamic libraries and call them from PL/SQL.

by Ajay Gursahani


Overview

This article describes the processes and procedures followed for writing external routines and accessing the same from Oracle SQL/PLSQL Blocks. Specifically, the articles covers:

  1. What is an External Routine (ER)
  2. How to Create an External Routine
  3. How to Register an External Routine
  4. How to interface PL/SQL with External Routines
  5. Advantages of External Routines
  6. Disadvantages of External Routines
This article is targeted to people who have an understanding of 'C'.


What is an External Routine?

An external routine is a third-generation language procedure stored in a dynamic link library (DLL), registered with PL/SQL, and called by the DBA to perform special-purpose processing.

In Unix a dynamic link library is known as a shared object (so).

At run time, PL/SQL loads the library dynamically, then calls the routine as if it were a PL/SQL subprogram. To safeguard our database, the routine runs in a separate address space, but it participates fully in the current transaction. Furthermore, the routine can make a call back to the database to perform SQL operations.


Creating an External Routine

Set up Oracle Environment for External Routines

  1. Edit the "tnsnames.ora" file by adding an entry that enables you to connect to the listener process (and subsequently, the extproc process).

    extproc_connection_data =
      (description =
        (address_list =
          (address = (protocol = IPC)(key = sjm))
        )
        (connect_data =
          (SID = extproc_agent_sjm)
        )
      )
    
    In the above entry, and all callouts for external routines, the entry name extproc_connection_data cannot be changed; it must be entered exactly as it appears here. The key you specify, in this case "sjm", must match the KEY you specify in the listener.ora file. Additionally, the SID name you specify, in this case "extproc_agent_sjm", must match the SID_NAME entry in the listener.ora file.

  2. Edit the listener.ora file by adding an entry for the "external procedure listener."
     
    sid_list_ext_proc_sjm =
      (sid_list =
        (sid_desc =
          (program = extproc)
          (sid_name = extproc_agent_sjm)
          (oracle_home = /u01/app/oracle/product/8.1.7)
        )
      )
     
    ext_proc_sjm =
      (description =
        (address = (protocol = IPC)(key = sjm))
      )
    
    In this entry, the PROGRAM must be "extproc" and cannot be changed; it must be entered exactly as it appears above. The SID_NAME must match the SID name in the "tnsnames.ora" file. ORACLE_HOME must be set to the directory where your Oracle software is installed. The extproc executable must reside in $ORACLE_HOME/bin.

  3. Start a separate listener process to exclusively handle external Routines.

  4. The extproc process spawned by the listener inherits the operating system privileges of the listener, so Oracle strongly recommends that you make sure that the privileges for the separate listener process are restrictive. The process should not have permission to read or write to database files, or to the Oracle server address space. Also, the owner of this separate listener process should not be "oracle" (which is the default owner of the server executable and database files).

  5. Place the extproc executable in $ORACLE_HOME/bin.

Identify the DLL

To identify a DLL we have to use CREATE LIBRARY command.

The CREATE LIBRARY command is used to create a schema object, library, which represents an operating-system shared library, from which SQL and PL/SQL can call external third-generation-language (3GL) functions and procedures.

The CREATE LIBRARY command is valid only on platforms that support shared libraries and dynamic linking.

Syntax: CREATE [OR REPLACE] LIBRARY libname {IS | AS} 'file_path';

Where:

    libname  is the name of the library (schema object) from which SQL and PL/SQL will call external 3GL functions and procedures.

    'filepath'  is the directory and filename where the .so or .dll file is stored. The path specified in 'filepath' is not interpreted by PL/SQL until run time.

Example 1

The following statement creates library test_ep:

CREATE LIBRARY test_ep AS '/appl/sjm/d/dlls/test_ep.so';

Example 2

The following example re-creates library test_ep:

CREATE OR REPLACE test_ep AS '/appl/sjm/d/dlls/ext_lib.so';


Writing functions

You can write functions and create a run time library or a shared object using C, C++ , COBOL or any third generation language.

Example - test_ep.c

#include 
/*  This function simply returns the larger of x and y*/
long find_max(int x,int y) 
{
      if (x >= y) return x;
      else return y;
}

How to generate a shared object

To generate a shared object or a run time library use the following script:

 
$/appl/sjm/d/dlls/createso 
 
"createso" is a user-script which is as below:
#----------------------------------------------------------------------------------------------------
# Directory where the shared object will reside
SO_DIR='/home/gursaha2/extproc/so'
echo 'Creating a Shared Object '$1'...'
echo '........'
cc  -xO2  -Xa  -xstrconst -xF  -mr  -xarch=v8 
-xcache=16/32/1:1024/64/1 -xchip=ultra -D_REENTRANT -K PIC 
-I/u01/app/oracle/product/8.0.6.3.0/rdbms/demo 
-I/u01/app/oracle/product/8.0.6.3.0/rdbms/public 
-I/u01/app/oracle/product/8.0.6.3.0/plsql/public 
-I/u01/app/oracle/product/8.0.6.3.0/network/public 
-DSLMXMX_ENABLE -DSLTS_ENABLE -c  $1.c 
ld -G -L/u01/app/oracle/product/8.0.6.3.0/lib 
-R/u01/app/oracle/product/8.0.6.3.0/lib -o $SO_DIR/$1.so $1.o
echo 'Object Created'
#----------------------------------------------------------------------------------------------------
Example

If you have a routine written in test_ep.c then give the following on the command prompt:

$/appl/sjm/d/dlls/createso test_ep

This will give you the shared object test_ep.so and the object file test_ep.o.


Grant Execute Privileges

You need to have EXECUTE privileges on LIBRARY to access the library and the functions in the library.

GRANT EXECUTE on <LIBRARY_NAME> to <USER/PUBLIC>;


Page 2: Registering an External Routine



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



Advantages of External Routines

External Routines offer the following advantages:

  1. Reusability, efficiency, and modularity.

  2. DLLs already written and available in other languages can be called from PL/SQL programs.

  3. The DLLs are loaded only when needed, so memory is conserved.

  4. The DLLs can be enhanced without affecting the calling programs.

  5. External Routines are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes.

  6. External Routines enable us to move computation-bound programs from client to server, where they will execute faster thanks to more computing power and less across-network communication.

  7. Interface the database server with external systems and data sources.

  8. Extend the functionality of the database server itself.


Disadvantages of External Routines

The disadvantages of using the External Routines are:

  1. The EP feature is only available on platforms that support DLLs or Shared Object libraries.

  2. Routines are only callable from C code (not C++).

  3. You cannot pass PL/SQL cursor variables, records, collections, or instances of an object type to an external Routine.

  4. You cannot use Remote Server/Distributed processing using the CREATE LIBRARY clause.

  5. The extproc must be started on the same server where the extprocs are being used.

  6. The maximum number of parameters that you can pass to a C external Routine is 128. Limit on the same depends on the OS you use.


Ajay Gursahani, Mahindra-British Telecom Limited
E-mail: ajay.gursahani@mahindrabt.com


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved