Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 27, 2002

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

By DatabaseJournal.com Staff


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




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date