About Building External Procedures

Describes how to create and use external procedures on Windows.

The following files are located in ORACLE_HOME\rdbms\extproc:

extern.c is the code example shown in "Writing an External Procedure"

make.bat is the batch file that builds the dynamic link library
extern.sql automates the instructions described in "Registering an External Procedure" and "Executing an External Procedure"

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.
Installing and Configuring Oracle Database and Oracle Net Services
Describes about the installation and configuration of Oracle Database and Oracle Net.
Writing an External Procedure
Using a third-generation programming language, you can write functions to be built into DLLs and started by EXTPROC.
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.
Registering an External Procedure
Once you have built a DLL containing your external procedures, you must register your external procedures with Oracle Database.
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 ON library_name convey a great deal of power to users.
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.

Parent topic: Developing Applications for Windows

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.

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:

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.

Parent topic: About Building External Procedures

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.

Parent topic: About Building External Procedures

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

Parent topic: Installing and Configuring Oracle Database and Oracle Net Services

Configuring Oracle Net Services

During database server installation, Oracle Net Configuration Assistant configures listener.ora and 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 extproc process 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.

Parent topic: Installing and Configuring Oracle Database and Oracle Net Services

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

Parent topic: About Building External Procedures

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.

See Also:

Oracle Database Development Guide for more information on EXTPROC

Parent topic: About Building External Procedures

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:

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\18.0.0\dbhome_1\rdbms\extproc\extern.dll)
   (ORACLE_HOME=C:\app\oracle\product\18.0.0\dbhome_1)
   (PROGRAM=extproc)
  )
 )

Start SQL*Plus:
```
C:\> sqlplus
```
Connect to the database with appropriate username and password.
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\18.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\18.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\18.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.
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)
```

Parent topic: About Building External Procedures

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 ON library_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.

Parent topic: About Building External Procedures

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:

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;

Run the routine:
```
SQL> EXECUTE UseIt;
```

Related Topics

Registering an External Procedure

Parent topic: About Building External Procedures