Stored Procedure mit User-defined Types unter Oracle

In diesem Beitrag möchte ich zeigen, wie eine Stored Procedure mit User-defined Types (UDT) in einer Oracle Database programmiert wird. Im übergeordneten Beitrag Spring und Stored Procedure mit User-defined Types wird das Aufrufen dieser Stored Procedure aus Java heraus gezeigt. Im Beitrag Stored Procedure mit User-defined Types unter PostgreSQL wird dieses Beispiel anhand einer PostgreSQL Datenbank beschrieben.

Dieser Beitrag beschreibt …

• das Installieren bzw. Einrichten eines Oracle Database Servers in einer Oracle VM VirtualBox
• das Erzeugen einer Stored Procedure mit User-defined Types (UDT)

Die Installation des Oracle Datenbankservers

Hier greife ich auf einen vorinstallierten und konfigurierten Datenbankserver in einer VirtualBox zurück. Dazu muss zunächst die Oracle VM VirtualBox unter dem Betriebssystem Ubuntu installiert werden.

$ sudo apt-get install virtualbox-4.2
...

Auf der Seite Pre-Built Developer VM kann unter dem Namen Database App Development VM eine vorgefertigte virtuelle Maschine (VM, ca. 4 GByte) mit folgenden Bestandteilen heruntergeladen werden.

• Oracle Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express
• Oracle JDeveloper
• Oracle XML DB
• Oracle TimesTen In-Memory Database Cache
• Hands-On-Labs

Um diese virtuelle Maschine herunterladen zu können muss man allerdings Mitglied des Oracle Technology Network (OTN) sein.

Achtung

Bitte beachten Sie die Hinweise zur Nutzung von Oracle.

Nach dem Download kann eine virtuelle Maschine aus der Datei Oracle_Developer_Day.ova importiert werden.

$ VBoxManager import Oracle_Developer_Day.ova 
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
 ...
Successfully imported the appliance.
$

Nach dem Import der virtuellen Maschine kann diese aufgelistet und der konkrete Name ermittelt werden.

$ VBoxManage list vms
"Kubuntu 12.10 amd64" {eb62ac51-9223-4152-a72d-13a01245e733}
"Oracle Developer Days" {8016926a-7770-4c87-845a-a414280bc331}
$

Es müssen noch notwendige Anpassungen am Netzwerk-Adapter (hier mit der Nummer 1) der virtuelle Maschine vorgenommen werden.

• In der Zeile 8 wird der Typ des Netzwerk-Adapters auf NAT umgestellt. Damit stellt die VirtualBox dem Gastsystem (Oracle Datenbank) einen NAT-Router inklusive DHCP-Server zu Verfügung. Dadurch erscheint das Gastsystem nicht im physischen Netzwerk des Hostsystems (mein Rechner). Die Verbindungen vom Gastsystem werden über die Netzwerkverbindung des Hostsystems transportiert. Ein direkter Verbindungsaufbau zum Gastsystem ist nur durch eine Port-Weiterleitung möglich.
• In der Zeile 9 wird eine Port-Weiterleitung vom Gastsystem (Port 1521) auf das Hostsystem (Port 1521) für die Datenbank (Listener) vorgenommen.
• In der Zeile 10 wird eine Port-Weiterleitung vom Gastsystem (Port 22) auf das Hostsystem (Port 2222) für SSH vorgenommen.

$ VBoxManage modifyvm "Oracle Developer Days" --nic1 nat
$ VBoxManage modifyvm "Oracle Developer Days" --natpf1 "ORACLE,tcp,,1521,,1521"
$ VBoxManage modifyvm "Oracle Developer Days" --natpf1 "SSH,tcp,,2222,,22"
$

Die virtuelle Maschine mit dem Namen Oracle Developer Days kann mit folgenden Parametern gestartet werden.

$ VBoxManage startvm "Oracle Developer Days"
Waiting for VM "Oracle Developer Days" to power on...
VM "Oracle Developer Days" has been successfully started.
$

In der Oberfläche des Oracle Linux kann man sich mit dem Benutzer oracle und dem Passwort oracle anmelden.

Eine Anmeldung am Gastsystem per ssh kann wie folgt durchgeführt werden.

$ ssh -p 2222 oracle@localhost
oracle@localhost password: 
Last login: ...
...
[oracle@localhost ~]$

Die Konsole kann durch den Befehl exit verlassen werden.

In dieser Version der Oracle Datenbank wird bei Benutzername und Passwort zwischen Groß- und Kleinschreibung unterschieden. Da einige Treiber bzw. Frameworks damit noch Probleme haben, schalten wir, für dieses Beispiel, dieses Feature wieder ab.

Zunächst wird die Konsolenanwendung SQL*Plus mit SYSDBA Recht gestartet.

[oracle@localhost ~]$ sqlplus hr/oracle as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on ...

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Das Ausschalten der Beachtung von Groß- und Kleinschreibung bei der Authentifizierung.

SQL> SHOW PARAMETER sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> ALTER SYSTEM SET sec_case_sensitive_logon=FALSE;

System altered.

SQL>

Das Erzeugung einer Stored Function per Konsolenanwendung

Als erstes wird über die Konsolenanwendung SQL*Plus eine Funktion erzeugt. Dazu auf den Gastrechner anmelden (Beschreibung siehe hier).

[oracle@localhost ~]$ sqlplus hr/oracle

SQL*Plus: Release 11.2.0.2.0 Production on ...

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Die Eingabe des SQL Befehls zur Erzeugung der Funktion fn_info().

SQL> CREATE OR REPLACE FUNCTION fn_info RETURN VARCHAR2 IS
  2  BEGIN
  3  RETURN 'Eine einfache Funktion';
  4  END fn_info;
  5  /

Function created.

SQL>

Mit dem folgenden SQL Befehl wird die gerade erzeugte Funktion getestet.

SQL> SELECT fn_info() FROM dual;

FN_INFO()
--------------------------------------------------------------------------------
Eine einfache Funktion

SQL>

Die Anzeigen der erzeugten Funktion.

SQL> DESC user_plsql_object_settings
 Name                                       Null?       Type
 -----------------------------------------  --------    ----------------------------
 NAME                                       NOT NULL    VARCHAR2(30)
 TYPE                                                   VARCHAR2(12)
 PLSQL_OPTIMIZE_LEVEL                                   NUMBER
 PLSQL_CODE_TYPE                                        VARCHAR2(4000)
 PLSQL_DEBUG                                            VARCHAR2(4000)
 PLSQL_WARNINGS                                         VARCHAR2(4000)
 NLS_LENGTH_SEMANTICS                                   VARCHAR2(4000)
 PLSQL_CCFLAGS                                          VARCHAR2(4000)
 PLSCOPE_SETTINGS                                       VARCHAR2(4000)

SQL> SELECT name, type FROM user_plsql_object_settings;

NAME                            TYPE
------------------------------  ------------
ADD_JOB_HISTORY                 PROCEDURE
FN_INFO                         FUNCTION
GETRESULTSET                    FUNCTION
SECURE_DML                      PROCEDURE
SECURE_EMPLOYEES                TRIGGER
TYPES                           PACKAGE
UPDATE_JOB_HISTORY              TRIGGER

7 rows selected.

SQL>

Die Erzeugung der Stored Procedure mit User-defined Types

Mit dem folgendem Skript wird die Stored Procedure und die User-defined Types angelegt.

Zunächst wird im Skript der Server Output angeschaltet und in der letzten Zeile das Programm wieder abgeschaltet.

SET SERVEROUTPUT ON

QUIT

In den folgenden Zeilen werden die benutzerdefineirten Typen des aktuellen Benutzers und einer Person angelegt bzw. überschrieben.

CREATE OR REPLACE TYPE s_User AS OBJECT ( -- Der aktuelle Benutzer
    id  CHAR(10),
    name    VARCHAR(30),
    dept    VARCHAR(254)
);
/

CREATE OR REPLACE TYPE s_Person AS OBJECT ( -- Eine Person
    id      NUMBER(19),
    name        VARCHAR(30),
    salary      DECIMAL,
    dateOfBirth DATE
);
/

Für die Person muss noch eine Liste definiert werden.

CREATE OR REPLACE TYPE a_Person AS TABLE OF s_Person; -- Collection der Person
/

In folgenden Zeilen wird die eigentliche Stored Procedure erzeugt.

• In den Zeilen 22 bis 23 sind die Eingabeparameter der Prozedur definiert.
• In der Zeile 24 ist der Rückgabeparameter als ein Liste von Personen definiert.
• In den Zeilen 26 bis 28 sind die Variabeln dieser Prozedur definiert.
• In der Zeile 28 wird das Geburtsdatum mit dem aktuellen Datum vorbelegt.
• In der Zeile 30 wird eine Meldung auf die Kommandozeile geschrieben.
• In der Zeile 32 wird die Liste für die Personen angelegt.
• In der Zeile 36 wird eine neue Person angelegt.
• In der Zeile 39 wird die konstant Kreiszahl Pi (π) verwendet, um ein Gehalt auszurechnen. Da Oracle die Kreiszahl nicht als Konstante kennt, wird die Funktion cos^-1(-1) verwendet, um die Kreiszahl zu berechnen.
• In der Zeile 42 wird die Liste der Personen um ein Nullelement erweitert.
• In der Zeile 43 wird der letzte Platz in der Liste der Personen mit der erzeugte Person gefüllt.

CREATE OR REPLACE PROCEDURE searchPersons( -- Suche Personen
    p_num       IN  INTEGER,
    p_user      IN  s_User,
    p_persons   OUT a_Person
) IS
    i       INTEGER;
    p       s_Person;
    dateOfBirth DATE := CURRENT_DATE;
BEGIN -- Ausführungsteil
    DBMS_OUTPUT.PUT_LINE('Die Stored Procedure searchPersons wurde aufgerufen. User=' || p_user.id);

    p_persons := a_Person();

    FOR i IN 1..p_num
    LOOP -- Fake der Suche
        p := s_Person(
            i,                      -- id
            p_user.name,            -- name
            ACOS(-1.0) * i * 100.0, -- Salary
            dateOfBirth             -- dateOfBrith
        );
        p_persons.extend();
        p_persons(p_persons.LAST) := p;
    END LOOP;
END searchPersons;
/

Bevor die SQL-Skripte ausgeführt werden können, müssen die SQL-Dateien per scp aus dem Verzeichnis src/sql/oracle auf den Oracle Server hochgeladen werden.

$ scp -P 2222 *.sql oracle@localhost:~
oracle@localhost password: 
init.sql                                                       100%  985     1.0KB/s   00:00
test.sql                                                       100%  571     0.6KB/s   00:00
$

In der Grundkonfiguration des Oracle Servers wird folgende Meldung erscheinen und die Dateien werden nicht hochgeladen.

$ scp -P 2222 *.sql oracle@localhost:~
oracle@localhost password: 
All passwords are oracle or noted if otherwise.
$

Dieses Problem liegt in den Skripten ~/.bashrc und ~/.bash_profile auf dem Oracle Server. Diese Skripte werden bei einer Anmeldung des Benutzers ausgeführt und produzieren Ausgaben. Leider kann scp mit den Ausgaben aus den Startskripten nicht umgehen und überträgt die Dateien nicht.

Es müssen folgenden Zeilen, die die Ausgaben produzieren, auskommentiert werden.

#cat ~/Desktop/README.txt

#/sbin/ifconfig | grep "inet addr"

#echo IP Address is:
#/sbin/ifconfig | grep "inet addr"

Nach der Durchführung dieser Änderungen, kann das Skriptes mit dem Kommando sqlplus ausgeführt werden.

Das Kommando liefert folgende Konsolenausgaben.

[oracle@localhost ~]$ sqlplus hr/oracle @init.sql

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 1 07:33:40 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Type created.


Type created.


Type created.


Procedure created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Das folgende Skript dient der Überprüfung der angelegten Prozedur.

• In den Zeilen 3 bis 15 wird eine Funktion definiert, die ein Benutzer erzeugt.
• In den Zeilen 18 bis 32 wird ein Block definiert, der es ermöglicht die Prozedur aufzurufen.

SET SERVEROUTPUT ON

CREATE OR REPLACE FUNCTION get_user
    RETURN s_User
IS
    u   s_User;
BEGIN
    u := s_User(
        4711,
        'Frank Rahn',
        'Entwicklung'
    );

    RETURN u;
END get_user;
/

DECLARE
    a   a_Person;
    i   INTEGER;
BEGIN
    searchPersons(5, get_user(), a);

    FOR i IN a.FIRST..a.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('Person ' || i);
        DBMS_OUTPUT.PUT_LINE('id=' || a(i).id);
        DBMS_OUTPUT.PUT_LINE('name=' || a(i).name);
        DBMS_OUTPUT.PUT_LINE('salary=' || a(i).salary);
        DBMS_OUTPUT.PUT_LINE('dateOfBirth=' || a(i).dateOfBirth);
    END LOOP;
END;
/

DROP FUNCTION get_user;

QUIT

Die Konsolenausgabe dieses Skripts zeigt, dass die Stored Procedure aufgerufen wurde.

[oracle@localhost ~]$ sqlplus hr/oracle @test.sql

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 1 07:44:02 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Function created.

Die Stored Procedure searchPersons wurde aufgerufen. User=4711
Person 1
id=1
name=Frank Rahn
salary=314
dateOfBirth=01-APR-13
Person 2
id=2
name=Frank Rahn
salary=628
dateOfBirth=01-APR-13
Person 3
id=3
name=Frank Rahn
salary=942
dateOfBirth=01-APR-13
Person 4
id=4
name=Frank Rahn
salary=1257
dateOfBirth=01-APR-13
Person 5
id=5
name=Frank Rahn
salary=1571
dateOfBirth=01-APR-13

PL/SQL procedure successfully completed.


Function dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$