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
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 ~]$
- Wer ist der optimale Java Bean Mapper? - Freitag, 22. September 2023
- Spring Boot Webanwendung: Die ersten Schritte (Tutorial) - Montag, 28. März 2016
- Mainframe-Zugriff via Java - Sonntag, 04. Mai 2014
Hinterlasse einen Kommentar
An der Diskussion beteiligen?Hinterlasse uns deinen Kommentar!