Oracle PL/SQL Programming, 5th Edition

Creating an Oracle Library

The SQL statement CREATE LIBRARY defines an alias in the Oracle data dictionary for the external shared library file, allowing the PL/SQL runtime engine to find the library when it is called. The only users who can create libraries are administrators and those to whom they have granted the CREATE LIBRARY or CREATE ANY LIBRARY privilege.

The general syntax for the CREATE LIBRARY command is:

CREATE [ OR REPLACE ] LIBRARY library_name
AS
   'path_to_file' [ AGENT 'agent_db_link' ] ;

where:

library_name

A legal PL/SQL identifier. This name will be used in subsequent bodies of external procedures that need to call the shared object (or DLL) file. The library name cannot be the same as a table, top-level PL/SQL object, or anything else in the main namespace.

path_to_file

The fully qualified pathname to the shared object (or DLL) file, enclosed in single quotes.

In Oracle9i Database, it became possible to use environment variables in path_to_file. In particular, if the operating system-level account sets the variable before starting the listener, you can put this variable in the CREATE LIBRARY statement; for example:

CREATE LIBRARY extprocshell_lib AS '${ORACLE_HOME}/lib/extprocsh.so'; -- Unix
CREATE LIBRARY extprocshell_lib AS '%{ORACLE_HOME}%\bin\extprocsh.dll'; -- MS

This may be a good thing to do for the sake of script portability.

You can also use an environment variable that you supply via EXTPROC_DLLS in the listener.ora file, as discussed earlier.

AGENT ‘agent_db_link’ ...