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> )
      (PROGRAM = extproc)
      (ENV = "EXTPROC_DLLS=ONLY:<custom_dll_directory>/<custom_shared_library>,LD_LIBRARY_PATH=<oracle_home_directory>/lib") ) )

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 

/* Declare a writestr function. */
void writestr1(char *path, char *message)
{
  /* Declare a FILE variable. */
  FILE *file_name;

  /* Open the File. */
  file_name = fopen(path,"w");

  /* Write to file the message received. */
  fprintf(file_name,"%s\n",message);

  /* Close the file. */
  fclose(file_name);

}

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
ACCEPT filedir PROMPT "Enter the library directory: " -- Define a session bind variable. VARIABLE directory VARCHAR2(255) DECLARE -- Define variables to build command. cmd VARCHAR2(255) := 'CREATE OR REPLACE LIBRARY '; int VARCHAR2(5) := ' AS '''; dir VARCHAR2(100) := '/tmp'; -- Windows default 'C:\TEMP' ext VARCHAR2(4) := '.so'''; file VARCHAR2(30) := 'writestr1'; lib VARCHAR2(30) := 'library_write_string'; BEGIN -- Check if an argument was passed. IF '&filedir' IS NOT NULL OR dir IS NOT NULL
  THEN -- Assign the argument as the directory. IF '&filedir' IS NOT NULL
    THEN IF INSTR('&filedir','$') = 0
      THEN dir := '&filedir'; ELSE dbms_output.put_line('Hey'); END IF; END IF; -- Assign session bind variable. :directory := dir; -- Build the command. cmd := cmd || lib || int || dir || '/' || file || ext; -- Print title and command. DBMS_OUTPUT.PUT_LINE('Command issued:'); DBMS_OUTPUT.PUT_LINE('---------------'); DBMS_OUTPUT.PUT_LINE(cmd); -- Execute the command. EXECUTE IMMEDIATE cmd; END IF; END; / -- List other Libraries
BEGIN -- Print title. DBMS_OUTPUT.PUT_LINE('Libraries found:'); DBMS_OUTPUT.PUT_LINE( '----------------------------------------'); -- Read all user libraries. FOR i IN (SELECT library_name c1 , file_spec c2 , dynamic c3 , status c4 FROM user_libraries)
  LOOP -- Print columns as rows. DBMS_OUTPUT.PUT_LINE('Library Name: ['||i.c1||']'); DBMS_OUTPUT.PUT_LINE('File Spec : ['||i.c2||']'); DBMS_OUTPUT.PUT_LINE('Dynamic : ['||i.c3||']'); DBMS_OUTPUT.PUT_LINE('Status : ['||i.c4||']'); -- Print title and command. DBMS_OUTPUT.PUT_LINE('----------------------------------------'); END LOOP; END; /
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 
 THEN DBMS_OUTPUT.PUT_LINE('The DLL path is not found for '||:directory||'.'); RETURN; -- Process file not found. WHEN missing_file
  THEN DBMS_OUTPUT.PUT_LINE('The library is not found in '||:directory||'.'); RETURN; END; /

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