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