External Procedures Overview & usage in Oracle

External Procedures Overview

External procedures are functions written in a third-generation language (C, for example) and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function.

External procedures let you take advantage of strengths and capabilities of a third-generation programming language in a PL/SQL environment.

External procedures are functions written in a third-generation language and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function.

External procedures promote reusability, efficiency, and modularity. Existing dynamic link libraries (DLLs) written in other languages can be called from PL/SQL programs. The DLLs are loaded only when needed and they can be enhanced without affecting the calling programs.

Using external procedures also enhances performance, because third-generation languages perform certain tasks more efficiently than PL/SQL, which is better suited for SQL transaction processing.

Note:

Oracle Database also provides a special purpose interface, the call specification, that lets you call external procedures from other languages, as long as they are callable by C.

The main advantages of external procedures are: 

• Performance, because some tasks are performed more efficiently in a third-generation language than in PL/SQL, which is better suited for SQL transaction processing
• Code re-usability, because dynamic link libraries (DLLs) can be called directly from PL/SQL programs on the server or in client tools

You can use external procedures to perform specific processes:

• Solving scientific and engineering problems
• Analyzing data
• Controlling real-time devices and processes

Note

Special security precautions are warranted when configuring a listener to handle external procedures. 

To create and use an external procedure, perform the following steps:

1. Installing and Configuring Oracle Database and Oracle Net Services 
2. Writing an External Procedure
3. Building a DLL
4. Registering an External Procedure
5. Restricting Library-Related Privileges to Trusted Users Only
6. Executing an External Procedure

Note

• You must have a C compiler and linker installed on your system to build DLLs.
• You can combine the instructions described in the fourth and fifth tasks into one SQL script that automates the task of registering and executing your external procedure. See ORACLE_HOME\rdbms\extproc\extern.sql for an example of a SQL script that combines these steps.

Installing and Configuring Oracle Database and Oracle Net Services 

Describes about the installation and configuration of Oracle Database and Oracle Net.

• Installing Oracle Database
  Learn how to install Oracle Database on your Windows server.
• Configuring Oracle Net Services
  During database server installation, Oracle Net Configuration Assistant configures listener.ora and tnsnames.ora files for external procedure calls.

Installing Oracle Database

Learn how to install Oracle Database on your Windows server.

Follow the steps in to install these products on your Windows server:

• Oracle Database Enterprise Edition, Oracle Database Standard Edition, or Oracle Database Personal Edition. Each type contains PL/SQL, from which external procedures are called, and the PL/SQL external procedure program (EXTPROC), which runs external procedures.
• Oracle Net Services
• Oracle Protocol Support

I skiped installation of Oracle in Ms Windows .

Configuring Oracle Net Services

During database server installation, Oracle Net Configuration Assistant configures listener.oraand tnsnames.ora files for external procedure calls.

When an application calls an external procedure, Oracle Net Listener starts an external procedure agent called EXTPROC. By default, the extprocprocess communicates directly through the server process. Using a network connection established by the listener, the application passes the following information to EXTPROC:

• DLL name
• External procedure name
• Parameters (if necessary)

EXTPROC then loads the DLL, runs the external procedure, and passes back any values returned by the external procedure.

If you overwrite default listener.ora and tnsnames.ora files, then you must manually configure the following files for the external procedure behavior described previously to occur:

ORACLE_HOME\network\admin\listener.ora

ORACLE_HOME\network\admin\tnsnames.ora

Note
Additional security may be required for the listener in a production environment

Writing an External Procedure

Using a third-generation programming language, you can write functions to be built into DLLs and started by EXTPROC. 

The following is a simple Microsoft Visual C++ example of an external procedure called FIND_MAX:

Note

Because external procedures are built into DLLs, they must be explicitly exported. In this example, the DLLEXPORT storage class modifier exports the function FIND_MAX from a dynamic link library.

#include <windows.h>

#define NullValue -1

/*

  This function tests if x is at least as big as y.

*/

long __declspec(dllexport) find_max(long  x, 

  short  x_indicator, 

long  y, 

short y_indicator, 

  short *ret_indicator)

{

   /* It can be tricky to debug DLL's that are being called by a process

      that is spawned only when needed, as in this case.  

      Therefore try using the DebugBreak(); command.  

      This starts your debugger.  Uncomment the line with DebugBreak(); 

      in it and you can step right into your code.

   */

   /* DebugBreak();  */

   /* First check to see if you have any nulls. */

   /* Just return a null if either x or y is null. */

   if ( x_indicator==NullValue || y_indicator==NullValue) {

      *ret_indicator = NullValue;   

      return(0);

   } else { 

      *ret_indicator = 0;       /* Signify that return value is not null. */

      if (x >= y) return x;

      else return y;

   }

}

Building a DLL

After writing your external procedures in a third-generation programming language, use the appropriate compiler and linker to build a DLL, making sure to export the external procedures as noted previously. 

See your compiler and linker documentation for instructions on building a DLL and exporting its functions. 

You can build the external procedure FIND_MAX, created in "Writing an External Procedure", into a DLL called extern.dll by going to ORACLE_HOME\rdbms\extproc and typing make. After building the DLL, you can move it to any directory on your system.

The default behavior of EXTPROC is to load DLLs only from ORACLE_HOME\bin or ORACLE_HOME\lib. To load DLLs from other directories, you must set environment variable EXTPROC_DLLS to a colon (:) separated list (semicolon-separated on Windows systems) of the DLL names qualified with their complete paths. The preferred way to set this environment variable is through the ENVS parameter in listener.ora.

Registering an External Procedure

Once you have built a DLL containing your external procedures, you must register your external procedures with Oracle Database. 

Now, you can configure the EXTPROC process to be authenticated through a CREDENTIAL for better security.

Oracle Database supports two new extensions to the CREATE LIBRARY command. This includes a CREDENTIAL clause and a DIRECTORY object option. The CREDENTIAL clause defines the user the EXTPROC runs as while the DIRECTORY object option specifies the directory where the DLL can be located.

To create a PL/SQL library to map to the DLL:

1- Set environment variable EXTPROC_DLLSin the ENVS parameter in listener.ora. For example

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (SID_NAME=PLSExtProc)

   (ENVS=EXTPROC_DLLS=C:\app\oracle\product\19.0.0\dbhome_1\rdbms\extproc\extern.dll)

   (ORACLE_HOME=C:\app\oracle\product\19.0.0\dbhome_1)

   (PROGRAM=extproc)

  )

 )

2- Start SQL*Plus:

C:\> sqlplus

3- Connect to the database with appropriate username and password.

4- Create the PL/SQL library using the CREATE LIBRARY command:

DBMS_CREDENTIAL.CREATE_CREDENTIAL(...);

CREATE DIRECTORY DLL_LOC as ...;

CREATE LIBRARY externProcedures as 'extern.dll' in DLL_LOC credential the_credential;

where the_credential is the name chosen during the DBMS_CREDENTIAL.CREATE_CREDENTIAL invocation
SQL> CREATE LIBRARY externProcedures AS 'C:\app\oracle\product\19.0.0\dbhome_1\rdbms\ extproc\extern.dll';

where  externProcedures is an alias library (essentially a schema object in the database), and

C:\app\oracle\product\19.0.0\dbhome_1\rdbms\extproc\extern.dll

is the path to the Windows operating system dllextern.dll. 

This example uses C:\app\oracle\product\19.0.0 as your Oracle base and dbhome_1 as your Oracle home.

Note
The DBA must grant the EXECUTE privilege on the PL/SQL library to users who want to call the library's external procedure from PL/SQL or SQL. Separate EXECUTE privilege on credential and directory object extensions are required for them to function properly.

5- Create a PL/SQL program unit specification.Do this by writing a PL/SQL subprogram that uses the EXTERNAL clause instead of declarations and a BEGIN...END block. The EXTERNAL clause is the interface between PL/SQL and the external procedure. The EXTERNAL clause identifies the following information about the external procedure:

• Name
• DLL alias
• Programming language in which it was written
• Calling standard (defaults to C if omitted)

In the following example, externProcedures is a DLL alias. You need the EXECUTE privilege for this library. The external procedure to call is find_max. If enclosed in double quotation marks, it becomes case-sensitive. The LANGUAGE term specifies the language in which the external procedure was written.

CREATE OR REPLACE FUNCTION PLS_MAX(

  x BINARY_INTEGER,

  y BINARY_INTEGER)

RETURN BINARY_INTEGER AS EXTERNAL

  LIBRARY externProcedures

  NAME "find_max"

  LANGUAGE C

  PARAMETERS (

    x long,                  -- stores value of x

    x_INDICATOR short,       -- used to determine if x is a NULL value

    y long,                  -- stores value of y

    y_INDICATOR short,        -- used to determine if y is a NULL value

  RETURN INDICATOR short );  -- need to pass pointer to return value's

                             -- indicator variable to determine if NULL

-- This means that my function is defined as:

    -- long max(long x, short x_indicator,

    -- long y, short y_indicator, short * ret_indicator)

Restricting Library-Related Privileges to Trusted Users Only

The CREATE LIBRARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, and EXECUTE ANY LIBRARY privileges, and grants of EXECUTE ONlibrary_name convey a great deal of power to users. 

If you plan to create PL/SQL interfaces to libraries, only grant the EXECUTE privilege to the PL/SQL interface. Do not grant EXECUTE on the underlying library. You must have the EXECUTE privilege on a library to create the PL/SQL interface to it. However, users have this privilege implicitly on libraries that they create in their own schemas. Explicit grants of EXECUTE ON library_name are rarely required. Only make an explicit grant of these privileges to trusted users, and never to the PUBLIC role.

Executing an External Procedure

To run an external procedure, you must call the PL/SQL program unit (that is, the alias for the external function) that registered the external procedure. 

These calls can appear in any of the following:

• Anonymous blocks
• Standalone and packaged subprograms
• Methods of an object type
• Database triggers
• SQL statements (calls to packaged functions only)

In “Registering an External Procedure”, PL/SQL function PLS_MAX registered external procedure find_max. Follow these steps to run find_max:

1. Call PL/SQL function PLS_MAX from a PL/SQL routine named UseIt:

SET SERVER OUTPUT ON

CREATE OR REPLACE PROCEDURE UseIt AS

          a integer;

          b integer;

          c integer;

BEGIN

          a := 1;

          b := 2;

          c := PLS_MAX(a,b);

          dbms_output.put_line('The maximum of '||a||' and '||b||' is  '||c);

END;

1. Run the routine:
   SQL> EXECUTE UseIt;
   

Overview of Multithreaded Agent Architecture

An agent process is started for each session to access a system at the same time leading to several thousand agent processes concurrently.

The agent processes operation regardless of whether each individual agent process is currently active. Agent processes and open connections can consume a disproportionate amount of system resources. This problem is addressed by using multithreaded agent architecture.

The multithreaded agent architecture uses a pool of shared agent threads. The tasks requested by the user sessions are put in a queue and are picked up by the first available multithreaded agent thread. Because only a small percentage of user connections are active at a given moment, using a multithreaded architecture allows for more efficient use of system resources.

To find more info about Multithreaded Agent architecture, you can see:

https://docs.oracle.com/en/database/oracle/oracle-database/26/lnoci/multithreaded-extproc-agent.html

About Debugging External Procedures

Usually, when an external procedure fails, its C prototype is faulty.

That is, the prototype does not match the one generated internally by PL/SQL. This can happen if you specify an incompatible C data type. For example, to pass an OUT parameter of type REAL, you must specify float *. Specifying float, double *, or any other C data type results in a mismatch. 

In such cases, you get a lost RPC connection toexternal procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. 

• Using Package DEBUG_EXTPROC
  To help you debug external procedures, PL/SQL provides the utility package DEBUG_EXTPROC.

Using Package DEBUG_EXTPROC

To help you debug external procedures, PL/SQL provides the utility package DEBUG_EXTPROC.

To install the package, run the script dbgextp.sql, which you can find in the PL/SQL demo directory. 

To use the package, follow instructions in dbgextp.sql. Your Oracle Database account must have EXECUTE privileges on the package and CREATE LIBRARY privileges.

To debug external procedures: 

1. From Windows Task Manager, in the Processes dialog, select ExtProc.exe. 
2. Right click, and select Debug. 
3. Click OK in the message window. If you have built your DLL in a debug fashion with Microsoft Visual C++, then Visual C++ is activated. 
   
4. In the Visual C++ window, select Edit >Breakpoints. Use the breakpoint identified in dbgextp.sql in the PL/SQL demo directory.
   
   See also
   ORACLE_HOME\rdbms\extproc\readme.doc (explains how to run the sample and provides debugging advice)

Default Configuration for External Procedures 

An external procedure is a procedure called from another program, written in a different language. An example is a PL/SQL program calling one or more C routines that are required to perform special-purpose processing. 

When an application calls an external procedure, Oracle Database starts an external procedure agent named extproc. Using the network connection established by Oracle Database, the application passes the following information to the agent:

• DLL or shared library name 
• External procedure name 
• Any parameters 

The agent then loads the DLL or the shared library, and runs the external procedure and passes back to the application any values returned by the external procedure. The agent must reside on the same computer as the application making the external procedure call.

When you use the default configuration for external procedures, the extproc agent is spawned directly by Oracle Database. There are no configuration changes required for either the listener.ora ortnsnames.ora file. However, you must define the environment variables to be used by external procedures in the extproc.ora file located in the ORACLE_BASE_HOME/hs/admin directory.

The default configuration for external procedures does not require a network listener to work with Oracle Database and the extproc agent. The extproc agent is spawned directly by Oracle Database and eliminates the risks that the extproc agent might be spawned by Oracle Listener unexpectedly. 

This default configuration is recommended for maximum security.

You can change the default configuration for external procedures and have the extprocagent spawned by Oracle Listener. To do this, you must perform additional network configuration steps.

Having the extproc agent spawned by Oracle Listener is necessary if you use:

• Multi-threaded agent
• Oracle Database in MTS mode on Microsoft Windows
• The AGENT clause of the LIBRARY specification or the AGENT IN clause of the PROCEDURE specification such that you can redirect external procedures to a different extproc agent.

Configuring Oracle Net Services for External Procedures

You can change the default configuration for external procedures and have the extproc agent spawned by the listener similar to earlier releases of Oracle Database. Here is the process:

----------------------------------------

1. Configure an existing listener or create a new listener to serve external procedures.

Following example shows a sample configuration in the listener.ora file.

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=sales.us.example.com)

      (ORACLE_HOME=/oracle)

      (SID_NAME=sales))

    (SID_DESC=

      (SID_NAME=plsextproc)

      (ORACLE_HOME=/oracle)

      (PROGRAM=extproc)))

----------------------------------------

2. Add a new entry in tnsnames.ora.

Following example shows a sample configuration in the tnsnames.ora file.

EXTPROC_CONNECTION_DATA_1=            

 (DESCRIPTION=                     

   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))                      

   (CONNECT_DATA=

    (SID=plsextproc)))

----------------------------------------

3. Use AGENT clause of the LIBRARY specification or AGENT IN clause of the PROCEDURE specification such that you can redirect external procedures to a different extproc agent (for example, extprocspawned by Oracle listener).

$ cat test.c

#include <stdlib.h>

int negative(char* db, int n)

{

        return -1*n;

}

char* mygetenv(const char* env)

{

        return getenv(env);

}

$ gcc -shared -fPIC -o test.so test.c

$ cp test.so $ORACLE_HOME/lib

In SQL/PLUS run:

DROP DATABASE LINK extproclink;

CREATE DATABASE LINK extproclink USING 'extproc_connection_data_1';

CREATE OR REPLACE LIBRARY test1 AS '$ORACLE_HOME/lib/test.so';

/

-- 

-- Use 'AGENT' clause in LIBRARY SPEC

-- 

CREATE OR REPLACE LIBRARY test2 AS '$ORACLE_HOME/lib/test.so' AGENT 'extproclink';

/

-- 

-- Use 'AGENT IN' clause in FUNCTION

-- 

CREATE OR REPLACE FUNCTION ftest1(x VARCHAR2, y BINARY_INTEGER)

RETURN BINARY_INTEGER

AS LANGUAGE C

LIBRARY test1

NAME "negative"

PARAMETERS(x STRING, y INT)

AGENT IN ( x );

/

CREATE OR REPLACE FUNCTION ftest2(x VARCHAR2)

RETURN VARCHAR2

AS LANGUAGE C

LIBRARY test2

NAME "mygetenv";

/

$ select ftest1('extproclink', 123) from dual;

$ select ftest2('LD_LIBRARY_PATH') from dual;

The listener for external procedures should have a user account that does not have general access to the files owned by the oracle user. Specifically, this user should not have permission to read or write to database files or to the Oracle server address space. In addition, this user should have read access to the listener.ora file, but must not have write access to it.

Running the listener with lower privileges also prevents using the Listener Control SET commands to alter the configuration of the listener in the listener.ora file. For this reason, Oracle recommends that you complete listener.ora file configuration before running the listener.

Also you can modify the Default Configuration for External Procedures 

And also create a New Listener to Run External Procedures for security or performance improvements.