Configuring SQL*Net for External Procedures

External procedures are functions written in a third-generation language  
(3GL) such as C, and callable from within PL/SQL or SQL as if they were a 
PL/SQL procedure or function.  External procedures enable you to take  
advantage of the strengths and capabilities of a 3GL programming language  
in a PL/SQL environment.

To enable external procedure calls, you must modify the Oracle Net Configuration 
files on the server.

A list of the essential components for executing an external procedure 
is included below, along with a brief description of the role of each. 

Database:

• Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.)
• Initiates the call to the procedure via the listener and external procedure agent
• Both the library alias and call specification are stored in the database.

Llistener:

• Listens for external procedure requests from databases and starts external procedure agent processes (one per session)

External procedure agent:

• Accepts information (arguments, shared library location, etc.) from the database, loads the shared library as required, and executes the corresponding function in some other language
• Corresponds to the extproc executable which resides in $ORACLE_HOME/bin

User-provided shared library:

• Contains the user-written function that implements the desired functionality

Library alias:

• Records the location of the shared library within the database
• Used in the PL/SQL call specification

External procedure call specification:

• Provides a description of the procedure, including its PL/SQL name, arguments and types, and the name of the library alias and the function (written in another language) implementing the procedure, or libunit in the case of a Java class method

 A Typical Sequence of Events 


The following is a typical sequence of events: 

1. The user invokes the call specification corresponding to an external procedure via PL/SQL or some other programmatic interface.
   
2. The database examines the call specification to find the name of the shared library implementing the function, as well as the name of the function and the number and type of the arguments.
   
3. The database uses the extproc_connection_data service name in the tnsnames.ora file to contact the external procedure listener.
   
   
   Note: This step only occurs the first time the external procedure is invoked within a given session. After that, the existing external procedure agent can be contacted directly
   
   
4. The listener starts an external procedure agent process to service the external procedure execution request and returns the address of the new extproc agent to the requesting database.
   

   Once the external procedure completes, extproc remains active throughout your Oracle session and

    is terminated once you log off.
    

5. The database sends the external procedure agent the name of the shared library, the name of the function to invoke, and after performing the required conversions from PL/SQL to another language datatypes, the actual arguments.
   
6. The external procedure agent loads the shared library, invokes the appropriate function, converts any returned data from another language datatypes to the corresponding PL/SQL types, and sends the data back to the database.

Steps to Configure:

 1-

• Log in to the system as the applicable user.
• cd to the $ORACLE_HOME/bin directory.
• Execute the following command at the prompt to check that required executable for external procedure is available and working:

C:\Documents and Settings\inam>extproc
Oracle Corporation --- MONDAY    OCT 03 2011 09:50:33.303
Heterogeneous Agent Release 10.2.0.4.0 - Production

2- Listener Configuration
When configuring the listener for external procedures, the following two options exist:

• Configure the existing database listener to listen for external procedures and database connections
  or
• Configure a separate listener specifically for external procedures.

There are several benefits to the second option including:

• Maintenance is simplified since the external procedure listener can be started and stopped independent of database connections.
  
• Security is enhanced since a separate listener can be run with a different effective user id. Running external procedures as the 'oracle' (or equivalent) user can expose the database to unnecessary risk since user-written procedures can then have unrestricted access to database resources (e.g. datafiles).

To properly configure a separate listener for external procedures:

• Decide on a name for the new listener (callout_listener in the examples that follow). This name appears in several places in the listener.ora file. Substitute the name you selected for callout_listener in the examples that follow.
  
• Modify the listener.ora file as follows:
  
  
  • Add the following entries:
    
    
    callout_listener =
  (ADDRESS_LIST =
      (ADDRESS =(PROTOCOL = IPC) (KEY = extproc_key))
  )

sid_list_callout_listener =
(SID_LIST =
     (SID_DESC =
        (SID_NAME = extproc_agent)
        (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
        (PROGRAM = extproc)
     )
) 
    
    where callout_listener is the name of the listener extproc_key agrees with the KEY in tnsnames.ora. extproc_agent agrees with the SID in tnsnames.ora
    
  • Remove any entries related to external procedures from the default listener that is being used to listen for database connections if such entries exist. This is only an issue if you have previously attempted to configure a single listener for both purposes.
    
  • Restart the database listener to make sure it reflects the most recent listener.ora changes. Restarting the original listener is especially important if it was previously set to listen for external procedures.

 3- Configure tnsnames.ora
The tnsnames.ora file must be configured to contain a special service name called extproc_connection_data. This service name provides the database with the necessary parameters to connect to the external procedure listener. The entry looks like:

EXTPROC_CONNECTION_DATA=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc_key))
   (CONNECT_DATA=
   (SID=extproc_agent)
    )
  )  

• extproc_connection_data cannot be changed except as follows:
  
  If, and only if, the sqlnet.ora file contains the following:
  
  names.default_domain = some.domain (e.g. us.oracle.com)
  
  then you must modify the above tnsnames.ora entry as follows:
  
  
  EXTPROC_CONNECTION_DATA.SOME.DOMAIN=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc_key))
(CONNECT_DATA=
(SID=extproc_agent))) 
  
  Setting default_domain causes some.domain to be automatically appended to all service names that are not already qualified with a domain. This is also true for extproc_connection_data.
  
  If default_domain is set, and that domain is not appended to the service name in tnsnames.ora, the connection fails.
  
  Note: As was the case for tnsnames.ora, the database uses the sqlnet.ora file that was pointed to by TNS_ADMIN at the time the database was last started. Make sure you are looking at the right copy of the file.
  
• extproc_key must be the same as the KEY=extproc_key entry in the listener.ora file (see below). Otherwise, it can be any string that is not already in use. This uniquely identifies
  which external procedure listener is to be contacted.
  
• extproc_agent must be the same as the SID_NAME=extproc_agent entry in the listener.ora file (see below). Otherwise, it can be any SID that is not already in use. This is the service that appears in the list displayed by 'lsnrctl status'.
  
• PROTOCOL must be set to IPC. Other protocols (e.g. TCP) are currently not supported for external procedures. 

NOTE:

In Oracle versions Oracle9i release 2 and newer, EXTPROC will only load Shared Libraries/Dynamic link Libraries (DLLs) that are in the $ORACLE_HOME/bin or $ORACLE_HOME/lib directory. Also, only local Oracle client processes will be able to call EXTPROC. To load shared libraries/DLLs from other directories, the environment variable EXTPROC_DLLS should be set. On both Windows and Unix platforms, the value for this environment variable is a list of colon (:) separated shared library names qualified with the complete path. When extproc is requested to load a shared library/DLL that is not in either $ORACLE_HOME/lib or $ORACLE_HOME/bin and is not specified as the value for the EXTPROC_DLLS environment variable, Oracle error ORA-28595 is returned to the client application. In addition an error is logged in a trace file written to the $ORACLE_HOME/rdbms/log or trace directory.

The preferred method for setting this environment variable is through the ENVS parameter in the LISTENER.ORA file. EXTPROC_DLLS can also be set to ANY to ignore shared library/DLL name validation or to ONLY for higher security. Set EXTPROC_DLLS to one of the following values:

• Colon-separated list of the shared libraries/DLLs

Syntax: "DLL:DLL"
Windows Example: Variable: EXTPROC_DLLS Value: C:\mypc\mydll\joe_lib.dll:c:\mydlls\mydll.dll:c:\bill\mydll\bill_lib.dll
Description: This value allows extproc to load the specified shared libraries/DLLs and the shared libraries/DLLs from $ORACLE_HOME/lib on UNIX operating systems and ORACLE_HOME\bin on Windows NT/2000. You must enter the complete directory location and file name of the shared libraries/DLLs.

• ONLY (Recommended for maximum security)

Syntax: "ONLY:DLL:DLL"
Description: This value allows extproc to load only the specified shared libraries/DLLs. You must enter the complete directory location and file name of the shared libraries/DLLs.

• ANY

Syntax: "ANY"
Description: This value allows extproc to load any shared libraries/DLLs. ANY disables shared libraries/DLLs
name validation.
Unix Example:
ENVS="EXTPROC_DLLS=/home/xyz/mylib.so:/home/abc/urlib.so,
LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs,
MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt"

ENVS="EXTPROC_DLLS=ONLY:/home/xyz/mylib.so:/home/abc/urlib.so,
LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs,
MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt"

ENVS="EXTPROC_DLLS=ANY,
LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs,
MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt"ENVS Example:
LISTENER_PROC =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY=external))
(ADDRESS= (PROTOCOL= TCP)(HOST=[host])(PORT=[port]))
)

SID_LIST_LISTENER_PROC =
(SID_LIST =
(SID_DESC =
(SID_NAME = [sid])
(ORACLE_HOME = [oracle home])
)
(SID_DESC =
(SID_NAME = external)
(ORACLE_HOME = [oracle home])
(PROGRAM = [path to exproc]/extproc)
(ENVS='PATH=[path definiton goes here],EXTPROC_DLLS=[location of shared library to be created later], ORACLE_SID=[sid]')
)
) 

Ref:  70638.1, 198523.1, 341586.1