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.

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

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

Topics:

• 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.

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

#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;
   }
}

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

1. Set environment variable EXTPROC_DLLS in 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\12.2.0\dbhome_1\rdbms\extproc\extern.dll)
      (ORACLE_HOME=C:\app\oracle\product\12.2.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\12.2.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\12.2.0\dbhome_1\rdbms\extproc\extern.dll
   

   is the path to the Windows operating system dllextern.dll. This example uses C:\app\oracle\product\12.2.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)

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

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.

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;
   

2. Run the routine:

   SQL> EXECUTE UseIt;