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

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles