External Procedures
External routines are delivered through external procedures, you can communicate with external applications through PL/SQL. Before we begin you might want to familiarize yourself with the Oracle listener.
External routines provide the ability to communicate between the database and external programs written in C, C++, Visual Basic or Java, however there is one caveat the language must be callable from C. External routines leverage the Oracle Net Services transport layer. The platform must be able to support shared libraries, the libraries are loaded dynamically at run time as external procedures, by default each remote procedure call uses a discrete and dedicated extproc agent to access the shared library. External procedures use the PL/SQL library definition to exchange data between PL/SQL run-time engine and the shared libraries (acts like a wrapper).
A call to a PL/SQL wrapper translates types, then the wrapper sends a signal across Oracle Net Services (ONS), ONS receives the signal and spawns or forks an extproc agent process. It is the extproc agent that accesses the shared library, it forks a RPC to the shared library, the shared library result is returned to the extproc agent by RPC, the agent then returns the result to the PL/SQL wrapper, the below diagram highlights the procedure used.
You can create a multithreaded agent which will share a single extproc agent among multiple database sessions, however this is complex to setup but does reduce resource issues. By default a extproc agent will be forked for each external procedure call but this can consume alot of resources.
You configure the extproc in the listener.ora file
extproc example (listener.ora) | SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = <oracle_home_dir> |
The program option specifys a valid executable in $oracle_home/bin, using env you can tighten up security a little by accessing libraries in certain directories. The env has other options
Syntax |
Security Level |
Description |
DLL:DLL |
Medium |
Allows the extproc agent to load any of the specified shared libraries located in the $oracle_home/lib directory |
ONLY:DLL:DLL |
High |
Allows extproc to run any entered DLL's from the specified directories |
ANY |
Low |
Allows extproc to load any DLL, it disables DLL checking |
You would update the tnsnames.ora file with the below
tnsnames.ora | EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) Note: you can test by using the tnsping command (make sure you have stopped/restarted the listener) |
Multithreaded External Procedure Agent
When using the multithreaded external procedure agent, you must start the agent sperately from the database. The agent keeps its configuration file in $oracle_home/hs/admin/initagt.dat, you can run the agent to get a shell mode GUI.
agent configuration file | $oracle_home/hs/admin/initagt.dat |
starting the agent | # agtctl |
agtctl utility uses six commands and six parameters
agtctl commands |
|
delete agent_sid | deletes an agent sid entry |
exit | exits the agtctl utility |
help | display available commands |
set parameter_name parameter_value | set a configuration parameter |
show parameter_name | shows a parameters value |
shutdown agent_sid | shuts down a agent_sid multithreaded agent |
startup agent_sid | starts up a agent_sid multithreaded agent |
unset parameter_name parameter_value | unsets a configuration parameter. |
agtctl parameters |
|
listener_address | address list for the agent controller listener |
max_dispatchers | maximum number of dispatchers |
max_sessions | maximum number of sessions |
max_task_threads | maximum number of threads |
shutdown_address | address on which agtctl listens for shutdown instructions |
tcp_dispatchers | number of dispatchers listening on TCP. All other dispatchers listen on IPC |
You now need to create a shared library, you can use the one below
/* * writestr1.c * Chapter 13, Oracle Database 11g PL/SQL Programming * by Michael McLaughlin * * ALERTS: * * This script opens a file and write a single line * of text to the file. It is used in conjunction * with the create_library1.sql script. * * Compilation instructions are below. You need to have * a C compiler installed on your local platform. If * you do not have a C compiler this is not possible. * You do the following: * - You need to compile this as a shared library in * UNIX, which has an *.so extension and as a * Dynamic Link Library (*.DLL) on the Windows * platforms. * - On UNIX, there are two different ways to compile * a shared library. They are noted below for * reference: * - Solaris: gcc -G -o sample.so sample.c * - GNU: gcc -shared -o sample.so sample.c * - It is assumed Microsoft's IDE is well designed * and provides help messages to compile a DLL. */ /* Include standard IO. */ #include |
You now have to create a PL/SQL library definition and wrapper so that you can pass information from the database to your C program.
Create Library definition | -- Create library definition |
Create the wrapper | CREATE OR REPLACE PROCEDURE write_string (path VARCHAR2, message VARCHAR2) AS EXTERNAL LIBRARY library_write_string NAME "writestr1" PARAMETERS (path STRING, message STRING); / |
Execute the wrapper | DECLARE -- Define a bad DLL path exception. bad_dll_path EXCEPTION; PRAGMA EXCEPTION_INIT(bad_dll_path,-28595); -- Define an missing file exception. missing_file EXCEPTION; PRAGMA EXCEPTION_INIT(missing_file,-6520); BEGIN -- Call external library. write_string('/tmp/file.txt','Hello World!'); EXCEPTION -- Process bad DLL path. WHEN bad_dll_path |
Working with a Java Shared Library
Oracle directly supports Java as part of the database, Java libraries do not use the extproc agent because that are natively part of the Oracle database. Java has a few advantages over the C procedures
However ther are a few disadvantages
The following steps detail how to use a Java library
grant read access to a file | DBMS_JAVA.GRANT_PERMISSION('PLSQL', 'SYS:java.io.FilePermission', '/tmp/file.txt', 'read'); |
Create the Java Library | /* * ReadFile1.java * Chapter 15, Oracle Database 11g PL/SQL Programming * by Michael McLaughlin * * ALERTS: * * This script demonstrates how to read a file in Java. * It is designed as a Java library file. */ // Class imports. import java.io.*; // Class defintion. public class ReadFile1 { // Convert the string to a file resource and call private method. public static String readString(String s) { return readFileString(new File(s)); } // Read an external file. private static String readFileString(File file) { // Define local variables. int c; String s = new String(); FileReader inFile; try { inFile = new FileReader(file); while ((c = inFile.read()) != -1) { s += (char) c; } } catch (IOException e) { return e.getMessage(); } return s; } // Testing method. public static void main(String[] args) { String file = new String("/tmp/file.txt"); System.out.println(ReadFile1.readString(file)); } } Note: you compile the file as follows loadjava -r -f -o -user plsql/plsql ReadFile1.class |
Publish you Java Library | CREATE OR REPLACE FUNCTION read_string (file IN VARCHAR2) RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'ReadFile1.readString(java.lang.String) return String'; / |
Test the Java Library | select read_string('/tmp/file.txt') from dual; Note: we are calling a function which in turns calls the Java library |