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 …

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.

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.

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.

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

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

  • In der Zeile 1 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 2 wird eine Port-Weiterleitung vom Gastsystem (Port 1521) auf das Hostsystem (Port 1521) für die Datenbank (Listener) vorgenommen.
  • In der Zeile 3 wird eine Port-Weiterleitung vom Gastsystem (Port 22) auf das Hostsystem (Port 2222) für SSH vorgenommen.

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

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.

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.

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

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).

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

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

Die Anzeigen der erzeugten Funktion.

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.

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

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

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.

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.

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

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.

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

Das Kommando liefert folgende Konsolenausgaben.

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.

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

Frank Rahn

Frank Rahn ist Softwarearchitekt. Er unterstützt bei der Konzeption von Softwarearchitekturen mit Java-Technologie. Folge Sie ihm auf Facebook, Twitter oder Google+.

Benötigen Sie Unterstützung? Kontaktieren Sie ihn.

Hat Ihnen dieser Beitrag gefallen? Wir würden uns über Ihren Kommentar freuen! Bitte verwenden Sie Ihren bürgerlichen Namen und eine E-Mail-Adresse mit Gravatar.

Letzte Artikel von Frank Rahn (Alle anzeigen)

0 Kommentare

Dein Kommentar

Want to join the discussion?
Feel free to contribute!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.