Mit Oracle-8 ist es erstmals möglich, aus PL/SQL sogenannte Callouts durchzuführen. Die Motivation dazu ist einfach und offensichtlich. Zugriffe auf das Betriebssystem, rechenintensive Berechungen in effiziente Sprachen auslagern. Dazu werden Shared Libraries verwendet, welche beispielsweise unter WIN-NT als DLL implementiert sind. Unter UNIX (Solaris, Linux) sind Shared Libraries ebenfalls nichts Neues, sie tragen die Dateiendung xxx.so. Oracle-8 kreiert beim Aufruf eines externen Programms einen eigenen Prozess. Die Aufrufe werden dann von einem SQL*Net Listener abgefangen. Der normale Listener kann dazu verwendet werden, dabei ist zu beachten, dass der externe Prozess die Privilegien desjenigen Betriebssystem Users hat, welcher den Listener gestartet hat. Die Verbindung von PL/SQL zum externen Prozess erfolgt ebenfalls über das SQL*Net Konfigurationsfile Tnsnames.ora.

Architektur

Um eine externe Procedure aufzurufen, muss die Calling PL/SQL Procedure wissen, in welcher DLL (oder Shared Library) sich diese Procedure befindet. Dazu wird in der Alias Library, welche im Data Dictionary gespeichert ist, nach der DLL gesucht. Wird der Name der DLL gefunden, so wird via SQL*NET der Listener kontaktiert, welcher den Oracle Agent extproc startet. Von nun an kommuniziert der Oracle Agent direkt mit der PL/SQL Engine. Die PL/SQL Engine übergibt mittels der PL/SQL Wrapper Function den Namen der DLL an den Oracle Agent. Dieser startet die DLL und übergibt das Resultat an die PL/SQL Engine zurück. Die Wrapper Function dient dazu, ein Mapping zwischen der DLL und der Calling PL/SQL Procedure zu erstellen. Sie wird mit einer speziellen Syntax definiert (siehe folgende Beispiele).

Im folgenden Artikel, wird die Vorgehensweise für WIN-NT4 und Linux an einem Beispiel gezeigt.

WIN-NT4 Variante

Im NT4 Beispiel, soll der Hostname des lokalen oder externen Hosts abgefragt werden. Dazu dient die Standard NT4 DLL Kernel32.dll, welche die C-Procedure "GetComputerNameA" enthält.

Übersicht

Auf dem Host Arkum (Client) wird die notwendige Library und die external Procedure angelegt und das entsprechend angepasste Tnsnames.ora File definiert. Auf dem Host Dorint (Server) muss nur der Listener (Listener.ora) modifiziert werden.

• Erstellen der Oracle Library auf Arkum

Die Oracle Library stellt den Link zur Shared Library auf dem Betriebssystem her. Die erstellte Library wird im Oracle Enterprise Manager Version 1.6 noch nicht ausgewiesen, sie kann jedoch in der View USER_LIBRARIES verifiziert werden.

CREATE OR REPLACE LIBRARY Kernel32 AS
'C:\WINNT\system32\Kernel32.dll';

• Registrierung des externen Aufrufes (Wrapper Function)

Damit ein externes Programm aufgerufen werden kann, muss eine Wrapper Funktion in PL/SQL geschrieben werden. Der Wrapper bestimmt die Art und Weise wie Parameter an die externe Funktion übergeben werden und welches die Datentypen sind. Folgender Wrapper wird auf Arkum definiert. Man beachte, dass der Functionsname des Wrappers mit der aufgerufenen externen Function in der Shared Library übereinstimmen muss.

Function GETCOMPUTERNAME (lpbufer IN OUT VARCHAR2,
                          nsize    IN OUT BINARY_INTEGER)
RETURN BINARY_INTEGER
IS EXTERNAL
LIBRARY Kernel32
NAME "GetComputerNameA"
LANGUAGE C
CALLING STANDARD PASCAL;

• Aufrufende PL/SQL Procedure

• Der obige Wrapper kann nun aus einem PL/SQL Programm einfach aufgerufen werden.

Procedure MY_EXT_PROC IS
  rwert BINARY_INTEGER;
  slen BINARY_INTEGER := 255; /* Must be length of sbuf */
  sbuf VARCHAR2(255) := ' ';  /* May NOT be NULL */
BEGIN
  rwert := getcomputername(sbuf,slen);
  dbms_output.put_line(sbuf);
END;

• Listener auf Dorint anpassen (Listener.ora)

  SQLNET.AUTHENTICATION_SERVICES = (NONE)
  USE_PLUG_AND_PLAY_LISTENER = OFF
  USE_CKPFILE_LISTENER = OFF
  LISTENER =
    (ADDRESS_LIST =
          (ADDRESS=
            (PROTOCOL=IPC)
            (KEY= DOR1.world)
          )
          (ADDRESS=
            (PROTOCOL=IPC)
            (KEY= DOR1)
          )
          (ADDRESS =
            (COMMUNITY = tcp.world)
            (PROTOCOL = TCP)
            (Host = dorint)
            (Port = 1526)
          )
    )
  STARTUP_WAIT_TIME_LISTENER = 0
  CONNECT_TIMEOUT_LISTENER = 10
  LOG_DIRECTORY_LISTENER = D:\ORANT\NET80\log
  LOG_FILE_LISTENER = listener
  TRACE_LEVEL_LISTENER = OFF
  TRACE_DIRECTORY_LISTENER = D:\ORANT\NET80\log
  TRACE_FILE_LISTENER = listener.trc
  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = DOR1.world)
        (SID_NAME = DOR1)
        (PRESPAWN_MAX = 10)
      )
      (SID_DESC =
        (SID_NAME = extproc)
        (PROGRAM = extproc)
      )
    )

• SQL*Net Client Konfigurationsfile Tnsnames.ora auf Arkum anpassen

  Der Connect-Descriptor muss in diesem Fall extproc_connection_data heissen.

  #
  # External Procedure Usage ####################################
  #
  extproc_connection_data.world =
                 (DESCRIPTION = (ADDRESS = (COMMUNITY = tcp.world)
                 (PROTOCOL = TCP) (Host = dorint) (Port = 1526))
                 (CONNECT_DATA = (SID = extproc)))

• Test aus SQL*Plus auf Arkum

SQL> set serveroutput on
SQL> execute my_ext_proc;
DORINT

PL/SQL procedure successfully completed.

Linux Variante

Im Linux Beispiel, soll ein beliebiges Unix Kommando aus der PL/SQL-Procedure aufgerufen werden können. Dazu erstellen wir die Shared Library libosint.so.

Übersicht

Auf dem Host Arkum (Client) wird die notwendige Library und die external Procedure angelegt und das entsprechend angepasste Tnsnames.ora File definiert. Auf dem Host Dorint (Server) muss nur der Listener (Listener.ora) modifiziert werden.

• Compileren und Linken der Shared Library libosint.so

Der C-Code wird erstellt, compiliert und als Shared Library gelinkt, die Shared Library kann mit dem folgenden Makefile unter Linux erstellt werden.

• Erstellen der Oracle Library auf Arkum

Die Oracle Library stellt den Link zur Shared Library auf dem Betriebssystem her. Die erstellte Library wird im Oracle Enterprise Manager Version 1.6 noch nicht ausgewiesen, sie kann jedoch in der View USER_LIBRARIES verifiziert werden.

CREATE OR REPLACE LIBRARY libosint AS
'/oracle/product/8.0.5/plsql/demo/libosint.so';

• Registrierung des externen Aufrufes (Wrapper Function)

Damit ein externes Programm aufgerufen werden kann, muss eine Wrapper Funktion in PL/SQL geschrieben werden. Der Wrapper bestimmt die Art und Weise wie Parameter an die externe Funktion übergeben werden und welches die Datentypen sind. Folgender Wrapper wird auf Arkum definiert. Man beachte, dass der Functionsname des Wrappers mit der aufgerufenen externen Function in der Shared Library übereinstimmen muss. Die Wrapper Function und die aufrufende Funktion haben wir in ein PL/SQL Package Osutil verpackt.

PROCEDURE RunOsCmdReg(cmdin IN VARCHAR2, cmdoutput OUT VARCHAR2)
  IS EXTERNAL
  NAME "RunOsCmd"  /* Function in Shared libosint.so */
  LIBRARY libosint
  WITH CONTEXT
  PARAMETERS (CONTEXT,
              cmdin STRING,
              cmdin INDICATOR SHORT,
              cmdin LENGTH INT,
              cmdoutput STRING,
              cmdoutput INDICATOR SHORT,
              cmdoutput LENGTH INT);

• Aufrufende PL/SQL Procedure

Der obige Wrapper kann nun aus einem PL/SQL Programm einfach aufgerufen werden.

PROCEDURE RunOsCmd(acmd IN VARCHAR2) IS

  cmd_out VARCHAR2(32767); -- output of OS command
  i INTEGER;    -- counter variabl
  j INTEGER;    -- counter variable
  len INTEGER;  -- length of command output
  cnl CHAR(1) := chr(10); -- "newline" character

BEGIN

  dbms_output.enable(32767); -- enable a large output buffer
  RunOsCmdReg(acmd, cmd_out); -- call the external procedure

  IF (cmd_out IS NULL) THEN
  dbms_output.put_line('ERROR, bad command or
                        no output returned...');
  ELSE -- output one line at a time
  len := length(cmd_out);
  i := 1; -- start at first char
  j := instr(cmd_out,cnl,i,1); -- find first newline char
  LOOP
    dbms_output.put_line(substr(cmd_out,i,j-i));
    IF ( j = len ) THEN
      EXIT;
    END IF;
    i := j + 1; -- start after last newline
    j := instr(cmd_out,cnl,i,1); -- find the next newline
  END LOOP;
END IF;

END RunOsCmd;

• Listener auf Rabbit (Linux Host) anpassen (Listener.ora)

CONNECT_TIMEOUT_LSNRRAB3 = 60
LOG_DIRECTORY_LSNRRAB3 =  /oracle/product/8.0.5/sqlnet
LOG_FILE_LSNRRAB3 =  listener.log
SQLNET.AUTHENTICATION_SERVICES = (NONE)
STARTUP_WAIT_TIME_LSNRRAB3 = 0
TRACE_DIRECTORY_LSNRRAB3 = /oracle/product/8.0.5/sqlnet
TRACE_FILE_LSNRRAB3 = listener.trc
TRACE_LEVEL_LSNRRAB3 = ADMIN
USE_CKPFILE_LSNRRAB3 = OFF
USE_DEDICATED_SERVER = ON
USE_PLUG_AND_PLAY_LSNRRAB3 = OFF

LSNRRAB3 =
  (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = TCP.world)
          (PROTOCOL = TCP)
          (Host = rabbit)
          (Port = 1532)
        )
  )

SID_LIST_LSNRRAB3 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = RAB3.world)
      (SID_NAME = extproc)
      (ORACLE_HOME = /oracle/product/8.0.5)
      (PROGRAM = /oracle/product/8.0.5/bin/extproc)
    )
  )

• SQL*Net Client Konfigurationsfile Tnsnames.ora auf Arkum anpassen.

Der Connect-Descriptor muss in diesem Fall extproc_connection_data heissen.

#
# External Procedure Usage ####################################
#
extproc_connection_data.world =
           (DESCRIPTION = (ADDRESS = (COMMUNITY = tcp.world)
           (PROTOCOL = TCP) (Host = rabbit) (Port = 1532))
           (CONNECT_DATA = (SID = extproc)))

• Test aus SQL*Plus auf Arkum (WIN-NT4)

SQL> execute osutil.runoscmd('/bin/ls -l /');

total 53
drwxr-xr-x 2 root root 2048 Apr 22 22:15 bin
drwxr-xr-x 3 root root 1024 Apr 22 22:24 boot
drwxr-xr-x 3 root root 21504 May 17 20:09 dev
drwxr-xr-x 31 root root 3072 May 17 20:09 etc
drwxr-xr-x 18 root root 1024 May 15 13:06 home
drwxr-xr-x 4 root root 2048 Apr 22 22:13 lib
drwxr-xr-x 2 root root 12288 Apr 22 22:07 lost+found
drwxr-xr-x 2 root root 0 May 17 20:09 misc
drwxr-xr-x 4 root root 1024 Apr 22 22:09 mnt
drwxr-xr-x 6 root root 1024 May 14 12:53 opt
dr-xr-xr-x 5 root root 0 May 17 20:07 proc
drwxr-x--- 7 root root 1024 May 15 15:03 root
drwxr-xr-x 3 root root 2048 Apr 22 22:15 sbin
drwxrwxrwt 3 root root 1024 May 19 14:01 tmp
drwxr-xr-x 21 root root 1024 Apr 22 22:12 usr
drwxr-xr-x 19 root root 1024 Apr 22 22:15 var

PL/SQL procedure successfully completed.