Application Express und Web Services: Integration mit PL/SQL

Web Services verbreiten sich mehr und mehr in den IT-Landschaften; sie sind das zentrale Element service-orientierter Architekturen (SOA). Kern dieser Architektur ist es, bestimmte, häufig benötigte Funktionalität als Web Service bereitzustellen, so dass verschiedenste Applikationen darauf zugreifen können, und zwar unabhängig von der verwendeten Technologie.

Application Express bietet eine sehr gute Unterstützung für Web Services an - in der letzten Ausgabe wurde diese ausführlich anhand eines Beispiels vorgestellt.

Application Express und Web Services: Warum reicht das nicht aus?

Die Web Service-Unterstützung in Application Express weist ein entscheidendes Merkmal auf. Das Aufrufen eines Web Service ist an ein Application Express-Formular oder an einen Bericht (oder genauer: An den Seitenprozeß Web Service) geknüpft. Ein Prozeß Web Service kann nur als Seitenprozeß (onLoad oder onSubmit) hinterlegt werden.

Da der Aufruf eines Web Service stets Netzwerkaktivität bedeutet, kann diese Kopplung unter Umständen zu Problemen führen:

  • Wenn der Web Service langsam reagiert, wird dadurch der Aufbau der gesamten Application Express-Seite verzögert. Wird der Web Service nur genutzt, um (wie im Beispiel der letzten Ausgabe) Wetterdaten oder einen um 15 Minuten verzögerten Aktienkurs anzuzeigen, so ist eine solche Verzögerung kaum hinzunehmen. Eine Abhilfe könnte hier allerdings mit der AJAX-Technologie erfolgen.
  • Wenn die Application Express-Seite sehr stark frequentiert ist, finden auch entsprechend viele Aufrufe des Web Service und damit entsprechender Netzwerkverkehr statt - vielfach (auch bei den Wetterdaten) ist ein solcher Realtime-Aufruf jedoch gar nicht erforderlich: Besser wäre ein asynchrones Aufrufen des Web Service (als Job) im Hintergrund und eine "Pufferung" der Daten in einer lokalen Tabelle. Dies ist mit der Web Service-Unterstützung in Application Express aus oben genannten Gründen allerdings nicht möglich.

Das Ziel: Integration der Web Services in PL/SQL

Abhilfe würde eine Integration des Web Service-Aufrufs direkt in PL/SQL bringen - und genau dies ist das Ziel des heutigen Tipps: Sie werden in der Lage sein, den Web Service in der Datenbank durch ein PL/SQL-Package abzubilden. Aufrufe auf diesem Paket führen zu Aufrufen des Web Service. Der Code wird komplett generiert - sie brauchen die Integration nicht selbst zu programmieren.

Die Datenbank muss allerdings vorbereitet werden, damit diese Integration möglich wird. Dazu benötigen Sie DBA-Privilegien auf der Datenbank und Zugriff auf den Datenbankserver - Sie müssen also mit dem DBA der Datenbank zusammenarbeiten.

Wie die Kommunikation mit dem Web Service stattfindet, zeigt Abbildung 1. Die eigentliche "Arbeit" wird durch die Java-Engine in der Datenbank erledigt (aus diesem Grund kann dieser Tipp nicht in einer OracleXE-Datenbank nachvollzogen werden). Mit Hilfe des WSDL-Dokumentes wird eine Java-Klasse generiert, die mit Hilfe einer Web Service-Client-Bibliothek den Aufruf durchführt. Der PL/SQL-Wrapper ist für die "Darstellung" der Funktionalität als PL/SQL-Package verantwortlich.

Aufruf von Web Services als PL/SQL-Paket: Funktionsweise

Abbildung 1: Aufruf von Web Services als PL/SQL-Paket: Funktionsweise

Vorbereitungen

Der erste Schritt ist die Installation der Web Service-Client-Bibliothek in die Datenbank. Voraussetzung ist, wie schon erläutert, dass die Java-Engine in der Datenbank vorhanden ist (das ist normalerweise der Fall). Laden Sie also das Web Service Callout Utility 10.1.3.1 aus dem Oracle Technet herunter und legen Sie es in ein Verzeichnis auf dem Datenbankserver. Das ZIP-Archiv enthält die folgenden Dateien und Ordner: 

./samples
./samples/test-plsql-dii.sql
./samples/javacallout.wsdl
./samples/javacallout.ear
./sqlj
./sqlj/lib
./sqlj/lib/utl_dbws_body.sql
./sqlj/lib/dbwsclientdb101.jar
./sqlj/lib/dbwsclientws.jar
./sqlj/lib/utl_dbws_decl.sql
./sqlj/lib/dbwsclientdb11.jar
./sqlj/lib/dbwsclientdb102.jar
./sqlj/lib/dbwsa.jar
./README.Callout.htm
./README.Callout.txt

Packen Sie das ZIP-Archiv nun in das $ORACLE_HOME Ihrer Datenbank aus, so dass die Dateien sich danach unterhalb von $ORACLE_HOME/sqlj befinden werden. Zum Laden der Web Service Client-Bibliothek benötigen Sie, wie schon gesagt, DBA-Privilegien. Setzen Sie dazu als SYS folgendes Kommando ab (am besten leiten Sie die Ausgabe in eine Datei um):

  • Wenn Sie Oracle10g Release 1 (10.1) verwenden: 
    loadjava -u sys/[passwort] -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb101.jar
  • Wenn Sie Oracle10g Release 2 (10.2) verwenden: 
    loadjava -u sys/[passwort] -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar
  • Wenn Sie Oracle11g (11.1) verwenden: 
    loadjava -u sys/[passwort] -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

Während der Ausführung (die einige Minuten dauern kann) kann es sein, dass ab und zu folgende Fehlermeldungen auftreten. 

Error while creating class com/sun/jmx/interceptor/DefaultMBeanServerInterceptor$1
    ORA-01031: Nicht ausreichende Berechtigungen
ORA-06512: in Zeile 1

Diese Fehlermeldungen können Sie ignorieren. Die Funktionalität zum Aufrufen des Web Service ist nun in der Datenbank installiert.

Einrichten des JPub-Werkzeugs

Der nächste Schritt ist die Einrichtung des JPub-Werkzeugs - mit seiner Hilfe können Sie den Code zum Aufrufen des Web Service anhand des WSDL-Dokumentes einfach als Java-Klasse (in der Datenbank) und zugehöriges PL/SQL-Paket generieren lassen. Laden das JPub Werkzeug (Version 10.2) aus dem Oracle Technet herunter, legen Sie es in ein Verzeichnis auf dem Datenbankserver ab. Es ist ebenfalls auch für Oracle11g geeignet und enthält unter anderem folgende Dateien: 

./sqlj
./sqlj/bin
./sqlj/bin/jpub
./sqlj/bin/README.txt
./sqlj/bin/jpub.exe
./sqlj/bin/jpub.c
./sqlj/lib
./sqlj/lib/sqljutl.sql
./sqlj/lib/runtime12.jar
./sqlj/lib/translator.jar
./sqlj/README.txt
./sqlj/doc
./sqlj/doc/faq.html
:

Packen Sie das ZIP-Archiv wie vorhin das Web Service Callout Utility in das $ORACLE_HOME Ihrer Datenbank aus - das führt dazu, dass die Dateien sich wie vorhin unterhalb von $ORACLE_HOME/sqlj befinden werden - genau dort gehören sie hin.

Setzen Sie nun den Java-CLASSPATH wie folgt; am besten schreiben Sie sich dafür ein Shellskript (UNIX/Linux) oder eine Batchdatei (Windows). Die folgenden Beispiele setzen voraus, dass die Umgebungsvariable ORACLE_HOME gesetzt ist. Zunächst das Unix/Linux-Beispiel: 

#!/bin/sh
export CLASSPATH=.
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ojdbc5.jar
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/dbwsa.jar
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/translator.jar
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/runtime12.jar
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/lib/xmlparserv2.jar

echo $CLASSPATH

Das Windows-Beispiel: 

@echo off

set CLASSPATH=.
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\ojdbc5.jar
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\dbwsa.jar
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\translator.jar
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\runtime12.jar
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\lib\xmlparserv2.jar

echo %CLASSPATH%

Achten Sie beim JDBC-Treiber auf die Java-Version. Die Beispielskripte gehen von Java 1.5 aus (ojdbc5.jar). Wenn Sie Java 1.4 verwenden, tauschen Sie es gegen ojdbc14.jar aus und wenn Sie Java6 verwenden, nehmen Sie ojdbc6.jar. Sie finden die Java-Version heraus, indem Sie auf der Kommandozeile "java -version" eingeben. ACHTUNG: Diese "Wahl" haben Sie nur bei Oracle11g; mit Oracle10g Release 2 können Sie auch außerhalb der Datenbank maximal Java 1.4 - mit Oracle10g Release 1 maximal Java 1.3 verwenden. 

$ java -version
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode)

Testen Sie zusätzlich (ebenfalls auf der Kommandozeile), ob der Java-Compiler vorhanden ist. Ein Aufruf von javac sollte folgende Ausgabe liefern. 

$ javac
Usage: javac <options> <source files>
where possible options include:
  -g                         Generate all debugging info
  :                          :

Kopieren Sie nun die Dateien jpub (bzw. jpub.exe auf Windows) von $ORACLE_HOME/sqlj/bin nach $ORACLE_HOME/bin. Auf Unix/Linux müssen Sie "jpub" ggfs. noch mit einem "chmod +x jpub" ausführbar machen.

Vergeben Sie nun als SYS noch folgendes Privileg, damit "normale" Datenbankuser mit dem JPub-Werkzeug Web Services einrichten können. Sie können als GRANTEE entweder gezielt Datenbanknutzer eintragen oder das Privileg mit PUBLIC an alle vergeben. 

begin
  dbms_java.grant_permission( 
    grantee           => 'PUBLIC', 
    permission_type   => 'SYS:oracle.aurora.security.JServerPermission', 
    permission_name   => 'Verifier', 
    permission_action => '' 
  );
end;
/

Nun geht es los: Einrichten des Web Service als PL/SQL Paket

Als Beispiel nehmen wir den bereits in der letzten Ausgabe vorgestellten Web Service Global Weather. Wiederum benötigen Sie die URL des WSDL-Dokumentes, welches den Web Service beschreibt. Sie ist http://www.webservicex.com/globalweather.asmx?WSDL. Nutzen Sie nun das JPub-Werkzeug und setzen Sie auf dem Datenbankserver folgendes Kommando ab: 

jpub \
  -J-Dhttp.proxyHost=[Proxyserver-Hostname] \
  -J-Dhttp.proxyPort=[Proxyserver-Port] \
  -user=[APEX Parsing Schema]/[passwort] \
  -proxywsdl="http://www.webservicex.com/globalweather.asmx?WSDL" \
  -package=globalweather \
  -plsqlpackage=WS_GLOBALWEATHER

Daraufhin sollten Sie in etwa folgende Ausgabe sehen: 

WARNING: ignoring port "GlobalWeatherHttpPost": because it references "{http://www.webserviceX.NET}GlobalWeatherHttpPost" 
that does not contain a SOAP Binding.  Only soap:binding is supported.
WARNING: ignoring port "GlobalWeatherHttpGet": because it references "{http://www.webserviceX.NET}GlobalWeatherHttpGet"
that does not contain a SOAP Binding.  Only soap:binding is supported.
tmp/src/globalweather/GlobalWeatherSoapClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Loading plsql_proxy.jar
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Warning: classes not generated for references from source
Please load the generated jar file and run plsql_grant.sql as SYS.

Die Warnungen können Sie ignorieren. Der Code zum Aufrufen des Web Service wurde generiert und auch gleich in die Datenbank geladen. Um den tatsächlichen Aufruf (und die damit verbundene Netzwerkverbindung) tatsächlich tätigen zu können, benötigen Sie wiederum Privilegien; das dazu nötige Skript hat das JPub-Werkzeug ebenfalls bereits generiert (plsql_grant.sql). Lassen Sie es als SYS laufen, dann ist alles fertig.

Den Web Service wie ein PL/SQL-Paket nutzen

Navigieren Sie nun im Application Express SQL Workshop (oder dem SQL Developer oder SQL*Plus) und lassen Sie sich das neue PL/SQL-Paket WS_GLOBALWEATHER beschreiben (desc WS_GLOBALWEATHER). Im SQL*Plus sehen Sie in etwa folgende Ausgabe. 

FUNCTION GETCITIESBYCOUNTRY RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           VARCHAR2                IN
FUNCTION GETENDPOINT RETURNS VARCHAR2
FUNCTION GETMAINTAINSESSION RETURNS NUMBER
FUNCTION GETPASSWORD RETURNS VARCHAR2
FUNCTION GETUSERNAME RETURNS VARCHAR2
FUNCTION GETWEATHER RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           VARCHAR2                IN
 ARG1                           VARCHAR2                IN
PROCEDURE SETENDPOINT
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           VARCHAR2                IN
PROCEDURE SETHTTPPROXY
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 HOST                           VARCHAR2                IN
 PORT                           NUMBER                  IN
PROCEDURE SETMAINTAINSESSION
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           NUMBER                  IN
PROCEDURE SETPASSWORD
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           VARCHAR2                IN
PROCEDURE SETUSERNAME
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 ARG0                           VARCHAR2                IN

Mit einem Aufruf von WS_GLOBALWEATHER.GETWEATHER können Sie den Web Service testen - denken Sie allerdings daran, zunächst den Proxy-Server mit WS_GLOBALWEATHER.SETHTTPPROXY einzustellen; Ihre Datenbank befindet sich sicherlich hinter einer Firewall. 

SQL> exec ws_globalweather.sethttpproxy('[http-proxy-host]',[http-proxy-port]);

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.00

select ws_globalweather.getweather('MUNICH','GERMANY') from dual;

WS_GLOBALWEATHER.GETWEATHER('MUNICH','GERMANY')
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>
  <Location>Munchen, Germany (EDDM) 48-21N 011-47E</Location>
  <Time>Dec 06, 2007 - 08:50 AM EST / 2007.12.06 1350 UTC</Time>
  <Wind> from the WSW (250 degrees) at 17 MPH (15 KT):0</Wind>
  <Visibility> greater than 7 mile(s):0</Visibility>
  <SkyConditions> mostly cloudy</SkyConditions>
  <Temperature> 50 F (10 C)</Temperature>
  <DewPoint> 39 F (4 C)</DewPoint>
  <RelativeHumidity> 66%</RelativeHumidity>
  <Pressure> 30.09 in. Hg (1019 hPa)</Pressure>
  <Status>Success</Status>
</CurrentWeather>

Und auch das strukturierte Auslesen ist nun ganz einfach: 

with weather as (
  select xmltype(ws_globalweather.getweather('MUNICH','GERMANY')) xml from dual
)
select
  extractvalue(w.xml, '/CurrentWeather/SkyConditions/text()') SKY_CONDITIONS,
  extractvalue(w.xml, '/CurrentWeather/Temperature/text()') TEMPERATURE,
  extractvalue(w.xml, '/CurrentWeather/RelativeHumidity/text()') HUMIDITY
from weather w
/

SKY_CONDITIONS       TEMPERATURE     HUMIDITY
-------------------- --------------- ----------
mostly cloudy        48 F (9 C)      70%

Noch eine Anmerkung für die Experten:

In der letzten Ausgabe machte die Methode GeoCitiesByCountry (hier abgebildet durch WS_GLOBALWEATHER.GETCITIESBYCOUNTRY) bei Ländern mit vielen Städten Probleme - Grund war das 32k-Limit für VARCHAR2. Schaut man sich das generierte Paket nochmals genau an, so erkennt man, dass die Rückgabe auch hier als VARCHAR2 erfolgt - damit exisitiert das Limit nach wie vor. Dieses Problem kann gelöst werden, allerdings müsste man dazu den durch das JPub-Werkzeug generierten Code so anpassen, dass eine Abbildung auf CLOB erfolgt. Eine detaillierte Beschreibung dieser Vorgehensweise würde hier allerdings zu weit führen.

Die Anwendung: Web Service asynchron aufrufen und Ergebnisse puffern

Nun lässt sich ein Puffern der Wetterdaten ganz einfach realisieren. Erzeugen Sie zunächst eine Tabelle mit den Städten, deren Wetterdaten Sie gerne verwenden würden und füllen Sie diese mit ein paar Zeilen. Lassen Sie das folgende Skript in SQL*Plus oder im SQL Workshop laufen. 

create table ws_wetterdaten(
  id      number(10),
  country varchar2(200),
  city    varchar2(200),
  constraint pk_wetterdaten primary key (id),
  constraint un_wetterdaten_countrycity unique(country, city)
)
/

insert into ws_wetterdaten values (1, 'GERMANY', 'MUNICH');
insert into ws_wetterdaten values (2, 'GERMANY', 'HAMBURG');
insert into ws_wetterdaten values (3, 'UNITED STATES', 'SAN FRANCISCO');
insert into ws_wetterdaten values (4, 'JAPAN', 'TOKYO');

commit
/

Erstellen Sie als nächstes eine PL/SQL-Prozedur, die mit Hilfe des neuen Paketes WS_GLOBALWEATHER die Wetterdaten abruft, das XML mit der Funktion EXTRACT interpretiert und die Wetterdaten in eine Tabelle WS_WETTERDATEN_DETAILS ablegt. 

create table ws_wetterdaten_details(
  country     varchar2(200),
  city        varchar2(200),
  city_lang   varchar2(200),
  zeitstempel varchar2(50),
  temperatur  varchar2(20),
  luftfeuchte varchar2(20),
  wetter      varchar2(100),
  wind        varchar2(100),
  constraint pk_wetterdaten_details primary key(country, city),
  constraint fk_wetterdaten_details_city foreign key (country, city) references ws_wetterdaten (country, city)
)
/

create or replace procedure lade_wetterdaten is
  v_wetterxml xmltype;
begin
  ws_globalweather.sethttpproxy('[proxyhost]', [proxyport]);
  for i in (select city, country from ws_wetterdaten) loop
    begin
      v_wetterxml := xmltype(ws_globalweather.getweather(i.city, i.country));
      delete from ws_wetterdaten_details where city = i.city and country = i.country;
      insert into ws_wetterdaten_details values (
        i.country,
        i.city, 
        extract(v_wetterxml, '/CurrentWeather/Location/text()'),
        extract(v_wetterxml, '/CurrentWeather/Time/text()'),
        extract(v_wetterxml, '/CurrentWeather/Temperature/text()'),
        extract(v_wetterxml, '/CurrentWeather/RelativeHumidity/text()'),
        extract(v_wetterxml, '/CurrentWeather/SkyConditions/text()'),
        extract(v_wetterxml, '/CurrentWeather/Wind/text()')
      );
    exception 
      when others then null;
    end;
  end loop;
end;
/
sho err

Passen Sie vor dem Ausführen des Skripts noch den Aufruf von WS_GLOBALWEATHER.SETHTTPPROXY noch an Ihre Umgebung an. Mit einem Aufruf dieser Prozedur LADE_WETTERDATEN können Sie nun einfach ein Update der Tabelle auslösen - um alles komplett zu machen, richten Sie noch mit DBMS_SCHEDULER einen Job ein, der einmal pro Stunde neue Wetterdaten vom Web Service abruft. 

begin
  dbms_scheduler.create_job(
    job_name            => 'HOLE_WETTERDATEN',
    job_type            => 'stored_procedure',
    job_action          => 'LADE_WETTERDATEN',
    number_of_arguments => 0,
    start_date          => systimestamp,
    repeat_interval     => 'FREQ=HOURLY; INTERVAL=1',
    end_date            => null,
    job_class           => 'DEFAULT_JOB_CLASS',
    enabled             => true,
    auto_drop           => true,
    comments            => null
  );
end;
/
sho err

Damit sind im Hintergrund alle Vorbereitungen gemacht. Sie können nun den Web Service GlobalWeather in Ihrer Datenbank wie ein gewöhnliches PL/SQL-Paket ansprechen und beliebig verwenden. Die Nutzung in einem Application Express Prozeß ist ebenso möglich, wie die Ansprache in einem Trigger. Es ist nun ein Leichtes, die Inhalte der Tabelle WS_WETTERDATEN_DETAILS auf einer Application Express-Seite als Bericht anzuzeigen.

Bericht auf die Tabelle WS_WETTERDATEN_DETAILS

Abbildung 2: Bericht auf die Tabelle WS_WETTERDATEN_DETAILS

Und das beste daran ist: Sie können die Netzwerkaktivität Ihrer Application Express-Datenbank ganz exakt vorhersagen: In diesem Beispiel finden genau ein mal pro Stunde soviele Anfragen statt, wie es Zeilen in der Tabelle WS_WETTERDATEN gibt. Wenn der Web Service mal langsam oder gar nicht verfügbar ist, merken Ihre Endanwender nichts davon - das äußerste ist die Darstellung veralteter Daten.

Mit den hier vorgestellten Web Service Callout Utility können Sie Web Services sehr eng mit Ihrer PL/SQL-Logik integrieren. Der größte Vorteil ist mit Sicherheit die Entkopplung der Web Service-Aufrufe von der Endbenutzeraktivität. Natürlich können Sie mit dieser Methode nicht nur Daten von Web Services abrufen, sondern auch Daten an solche übermitteln. Die Anwendungsmöglichkeiten sind zahlreich - hier sind zwei weitere Beispiele:

Das Beispiel wurde mit Oracle11g getestet - sollte aber auch mit Oracle10g Release 1 oder Oracle10g Release 2 analog laufen. In Oracle9i sind Web Service Callouts ebenfalls möglich; allerdings muss dann ein anderes Callout Utility installiert werden. Mehr Informationen dazu enthält das Web Service Callout Users' Guide im Oracle Technet.

Zurück zur Community-Seite