29. Mai 2012

Tabellen- oder andere Metadaten als XML ausgeben ...

Get table (or object) metadata ... as XML

Jüngst erreichte mich die Frage, wie man eine Tabellendefinition als XML ausgeben kann - für eine gegebene Tabelle (bspw. EMP) wird also eine Ausgabe in etwa wie folgt benötigt ...

<table schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

Und sowas ist einfacher, als man denkt: Denn man muss gerade nicht selbst das Data Dictionary durcharbeiten, es gibt fertige Funktionen dafür. Das PL/SQL-Package der Wahl ist DBMS_METADATA. Das kennt nicht nur die GET_DDL-Funktion zum Erzeugen von SQL-Skripts, sondern auch die Funktionen GET_XML und GET_SXML. Beide Varianten erzeugen ein XML-Format ...

  • GET_XML generiert ein recht umfangreiches XML, welches auch wirklich alle Informationen zum Datenbankobjekt enthält. Allerdings sind viele Details intern kodiert - die Datentypen der Tabellenspalten sind bspw. nicht als Typ-Bezeichnungen, sondern als Oracle-Interne Type-IDs enthalten. Dieses Format ist vor allem für die Datenbank selbst gedacht.
    <?xml version="1.0"?>
    <ROWSET><ROW>
      <TABLE_T>
     <VERS_MAJOR>1</VERS_MAJOR>
     <VERS_MINOR>3 </VERS_MINOR>
     <OBJ_NUM>78581</OBJ_NUM>
     <SCHEMA_OBJ>
      <OBJ_NUM>78581</OBJ_NUM>
      <DATAOBJ_NUM>78581</DATAOBJ_NUM>
      <OWNER_NUM>96</OWNER_NUM>
      :
    
  • GET_SXML generiert ein XML-Dokument, welches wie eine XML-Repräsentation eines DDL-Skripts aussieht und dementsprechend die Informationen enthält, die man auch in einem CREATE TABLE Kommando verwendet. Mit einem solchen XML kann man weiterarbeiten ...
    <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
     <SCHEMA>TESTIT</SCHEMA>
     <NAME>EMP</NAME>
     <RELATIONAL_TABLE>
        <COL_LIST>
           <COL_LIST_ITEM>
              <NAME>EMPNO</NAME>
              <DATATYPE>NUMBER</DATATYPE>
              <PRECISION>4</PRECISION>
              <SCALE>0</SCALE>
              <NOT_NULL></NOT_NULL>
           </COL_LIST_ITEM>
       :
    

DBMS_METADATA macht also schon fast die ganze Arbeit. Übrigens arbeitet DBMS_METADATA intern immer mit XML. Auch wenn man mit GET_DDL ein SQL-Skript anfordert, wird zunächst ein XML generiert (GET_XML) und danach wird dieses XML in ein SQL-Skript umgewandelt. GET_DDL macht das alles auf einmal. Wenn man das Generieren der SQL-Skripte genau konfigurieren möchte und mit den Aufrufen von OPEN, ADD_TRANSFORM_PARAM, ADD_REMAP_PARAM, FETCH und CLOSE des Pakets DBMS_METADATA arbeitet, dann legt man mit ADD_TRANSFORM fest, ob man die Metadaten als XML, SXML oder als DDL-Skript haben möchte.

Nun haben wir mit DBMS_METADATA.GET_SXML schon ein XML-Dokument für unsere Tabelle; allerdings sieht das XML-Format noch nicht so aus, wie es oben gefordert wurde. Aber in der Welt von XML und der Oracle-Datenbank ist das ein Kinderspiel: Ein XML in ein anderes XML transformieren - dazu sind XSLT-Stylesheets gut geeignet. Und die Funktion XMLTRANSFORM macht eine solche XSLT-Transformation direkt in der Datenbank. Also zunächst das Stylesheet ...

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:ku="http://xmlns.oracle.com/ku" version="1.0">
<xsl:output method="xml"/>
 <xsl:template match="/">
  <xsl:for-each select="/ku:TABLE">
  <table>
   <xsl:attribute name="schema">
    <xsl:value-of select="ku:SCHEMA/text()"/>
   </xsl:attribute>
   <xsl:attribute name="name">
    <xsl:value-of select="ku:NAME/text()"/>
   </xsl:attribute>
   <xsl:for-each select="ku:RELATIONAL_TABLE/ku:COL_LIST/ku:COL_LIST_ITEM">
    <column>
     <xsl:variable name="current-column">
      <xsl:value-of select="ku:NAME"/>
     </xsl:variable> 
     <xsl:attribute name="name">
      <xsl:value-of select="ku:NAME"/>
     </xsl:attribute>
     <xsl:attribute name="type">
      <xsl:choose>
       <xsl:when test="ku:DATATYPE = 'VARCHAR2'">
        <xsl:value-of select="concat(ku:DATATYPE,'(', ku:LENGTH, ')')"/>
       </xsl:when>
       <xsl:otherwise>
        <xsl:choose>
         <xsl:when test="ku:DATATYPE = 'NUMBER'">
          <xsl:choose>
           <xsl:when test="ku:PRECISION">
            <xsl:value-of select="concat(ku:DATATYPE,'(', ku:PRECISION, ',', ku:SCALE, ')')"/>
           </xsl:when>
           <xsl:otherwise>
            <xsl:value-of select="ku:DATATYPE"/>
           </xsl:otherwise>
          </xsl:choose>
         </xsl:when>
         <xsl:otherwise>
          <xsl:value-of select="ku:DATATYPE"/>
         </xsl:otherwise>
        </xsl:choose>
       </xsl:otherwise>
      </xsl:choose>
     </xsl:attribute>
     <xsl:for-each select="../../ku:PRIMARY_KEY_CONSTRAINT_LIST/ku:PRIMARY_KEY_CONSTRAINT_LIST_ITEM/ku:COL_LIST/ku:COL_LIST_ITEM">
      <xsl:choose>
       <xsl:when test="ku:NAME/text() = $current-column">
        <xsl:attribute name="primary-key">
         <xsl:value-of select="position()"/>
        </xsl:attribute>
       </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </column>
   </xsl:for-each>
  </table>
   </xsl:for-each>
 </xsl:template>
</xsl:stylesheet> 

Dieses XSLT könnte man nun in eine Tabelle speichern. Man kann nun mit BFILE, UTL_FILE oder dem XML DB Repository arbeiten. Letzteres ist ein sehr einfacher und eleganter Weg, Dateien in die Datenbank zu bringen. Schaltet zunächst den FTP-Port der Datenbank mit DBMS_XDB.SETFTPPORT(2100) frei. Danach bringt Ihr das Stylesheet so in die Datenbank.

D:\>ftp -n
ftp> open sccloud030 2100
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> put table.xsl
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 2041 bytes sent in 0,00Seconds 2041000,00Kbytes/sec.
ftp>

Nun ist alles da: Mit DBMS_METADATA.GET_SXML holen wir die Metadaten der Tabelle EMP als XML (Oracle "SXML") und danach wandeln wir dieses mit dem XSLT-Stylesheet in ein anderes XML um. Achtet auf die Anwendung der XDBURITYPE-Funktion - mit der holen wir das Stylesheet aus dem XML DB Repository (wohin wir es per FTP hochgeladen hatten) wieder heraus.

select 
 xmltransform(
  xmltype(dbms_metadata.get_sxml('TABLE','EMP')), 
  xdburitype('/public/table.xsl').getxml()
) from dual;

XMLTRANSFORM(XMLTYPE(DBMS_METADATA.GET_SXML('TABLE','EMP')),XDBURITYPE('/PUBLIC/
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<table xmlns:ku="http://xmlns.oracle.com/ku" schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

Voilá! Mit dem "richtigen" XSLT kann man sich jede beliebige XML-Struktur erzeugen; sogar das Generieren von Code wäre denkbar, denn mit XSLT können auch Textdateien erzeugt werden. XML-Dokumente lassen sich auch recht gut vergleichen; allerdings muss man das nicht selbst machen; man kann das Paket DBMS_METADATA_DIFF verwenden. Viel Spaß beim Ausprobieren und hier noch ein paar Links zum Thema:

Some time ago I got a question about how to retrieve table metadata as XML - the colleague needed the definition of a table in the Oracle database (say: EMP) as follows ...

<table schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

And this is much simpler as most people think. It is not necessary to walk through the data dictionary and to generate XML using the appropriate SQL funktions or string concatenation. We have ready-to-use functionality for that in DBMS_METADATA. This does not only contain GET_DDL to generate SQL scripts, but also the functions GET_XML and GET_SXML. Both functions generate metadata in XML format.

  • GET_XML produces an XML document containing all information about the database object. This XML is complex, opaque and contains binary and instance-specific information. So you'll find the information that a table column has the data type "2" instead of VARCHAR2. So this XML format is only for Oracle internal use.
    <?xml version="1.0"?>
    <ROWSET><ROW>
      <TABLE_T>
     <VERS_MAJOR>1</VERS_MAJOR>
     <VERS_MINOR>3 </VERS_MINOR>
     <OBJ_NUM>78581</OBJ_NUM>
     <SCHEMA_OBJ>
      <OBJ_NUM>78581</OBJ_NUM>
      <DATAOBJ_NUM>78581</DATAOBJ_NUM>
      <OWNER_NUM>96</OWNER_NUM>
      :
    
  • GET_SXML generates an XML document which looks like an XML representation of a DDL statement. The tag names and structure corresponds to the names and structure used in DDL statements. So this format is suitable to work on with ...
    <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
     <SCHEMA>TESTIT</SCHEMA>
     <NAME>EMP</NAME>
     <RELATIONAL_TABLE>
        <COL_LIST>
           <COL_LIST_ITEM>
              <NAME>EMPNO</NAME>
              <DATATYPE>NUMBER</DATATYPE>
              <PRECISION>4</PRECISION>
              <SCALE>0</SCALE>
              <NOT_NULL></NOT_NULL>
           </COL_LIST_ITEM>
       :
    

DBMS_METADATA does most of the work for us. BTW: It works the same way internally: Metadata is firstly being retrieved as XML and then converted to a SQL script. When GET_DDL is being used, the package does it all at once. Those who customize DBMS_METADATA by using calls to OPEN, ADD_TRANSFORM_PARAM, ADD_REMAP_PARAM, FETCH and CLOSE, may use ADD_TRANSFORM to generate their metadata as DDL, XML or SXML.

So we have an XML document representing our table, but this is not the XML format we need. The tag names and structure introduced at the beginning is different from Oracle's SXML format. But in the XML worlds this is not really a problem. We can transform XML documents into another tag structure by using XSLT Stylesheets. And the Oracle database provides the SQL function XMLTRANSFORM to execute such a transformation directly in the database. So here is the XSLT Stylesheet ...

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:ku="http://xmlns.oracle.com/ku" version="1.0">
<xsl:output method="xml"/>
 <xsl:template match="/">
  <xsl:for-each select="/ku:TABLE">
  <table>
   <xsl:attribute name="schema">
    <xsl:value-of select="ku:SCHEMA/text()"/>
   </xsl:attribute>
   <xsl:attribute name="name">
    <xsl:value-of select="ku:NAME/text()"/>
   </xsl:attribute>
   <xsl:for-each select="ku:RELATIONAL_TABLE/ku:COL_LIST/ku:COL_LIST_ITEM">
    <column>
     <xsl:variable name="current-column">
      <xsl:value-of select="ku:NAME"/>
     </xsl:variable> 
     <xsl:attribute name="name">
      <xsl:value-of select="ku:NAME"/>
     </xsl:attribute>
     <xsl:attribute name="type">
      <xsl:choose>
       <xsl:when test="ku:DATATYPE = 'VARCHAR2'">
        <xsl:value-of select="concat(ku:DATATYPE,'(', ku:LENGTH, ')')"/>
       </xsl:when>
       <xsl:otherwise>
        <xsl:choose>
         <xsl:when test="ku:DATATYPE = 'NUMBER'">
          <xsl:choose>
           <xsl:when test="ku:PRECISION">
            <xsl:value-of select="concat(ku:DATATYPE,'(', ku:PRECISION, ',', ku:SCALE, ')')"/>
           </xsl:when>
           <xsl:otherwise>
            <xsl:value-of select="ku:DATATYPE"/>
           </xsl:otherwise>
          </xsl:choose>
         </xsl:when>
         <xsl:otherwise>
          <xsl:value-of select="ku:DATATYPE"/>
         </xsl:otherwise>
        </xsl:choose>
       </xsl:otherwise>
      </xsl:choose>
     </xsl:attribute>
     <xsl:for-each select="../../ku:PRIMARY_KEY_CONSTRAINT_LIST/ku:PRIMARY_KEY_CONSTRAINT_LIST_ITEM/ku:COL_LIST/ku:COL_LIST_ITEM">
      <xsl:choose>
       <xsl:when test="ku:NAME/text() = $current-column">
        <xsl:attribute name="primary-key">
         <xsl:value-of select="position()"/>
        </xsl:attribute>
       </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </column>
   </xsl:for-each>
  </table>
   </xsl:for-each>
 </xsl:template>
</xsl:stylesheet> 

Before using XMLTRANSFORM we need to bring the Stylesheet into the database. There are many approaches for this - using UTL_FILE, BFILE, SQL*Loader and others. An easy and elegant approach is to use the XML DB repository and the FTP protocol server. First enable it by executing DBMS_XDB.SETFTPPORT(2100) (as SYS). Afterwards you can use a plain FTP client to upload the XSLT file into your database.

D:\>ftp -n
ftp> open sccloud030 2100
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> put table.xsl
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 2041 bytes sent in 0,00Seconds 2041000,00Kbytes/sec.
ftp>

Now all components are present. The table metadata is being retrieved in "SXML" format using DBMS_METADATA.GET_SXML and transformed to another XML format with our stylesheet and the XMLTRANSFORM function. Note the usage of the XDBURITYPE function: This grabs the stylesheet from the XML DB repository, where we uploaded it with the FTP client.

select 
 xmltransform(
  xmltype(dbms_metadata.get_sxml('TABLE','EMP')), 
  xdburitype('/public/table.xsl').getxml()
) from dual;

XMLTRANSFORM(XMLTYPE(DBMS_METADATA.GET_SXML('TABLE','EMP')),XDBURITYPE('/PUBLIC/
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<table xmlns:ku="http://xmlns.oracle.com/ku" schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

That's it. The target XML structure can be changed by changing the stylesheet. It's even possible to generate code using stylesheets since they can render plain text as well. The SXML format is also very useful in order to detect differences between database objects - but there's also a ready-to-use package for this: DBMS_METADATA_DIFF.

Have fun trying this out.

9. Mai 2012

SOAP-Zugang zu PL/SQL: Database Native Webservices

SOAP access to PL/SQL objects: Database Native Webservices

Heute möchte ich etwas über die Database Native Webservices schreiben, die zwar bereits mit der Datenbankversion 11g Release 1 eingeführt wurden, die aber immer noch kaum bekannt sind. Worum geht es? Mit den Database Native Webservices kann eine SQL-Query oder PL/SQL-Funktion ohne weiteren Aufwand als SOAP-Style Webservice bereitgestellt werden. Und das geht so:

  • Zunächst muss die XML DB in der Datenbank vorhanden sein. Das prüft man am einfachsten mit einer Abfrage auf die View DBA_REGISTRY.
    select comp_id, version from dba_registry where comp_id='XML'
    
    COMP_ID                        VERSION
    ------------------------------ ---------------------
    XML                            11.2.0.2.0
    
    Wenn hier keine Zeile zurückgegeben wird, ist die XML DB nicht vorhanden und es können auch keine Native Webservices genutzt werden. Mit dem PL/SQL Skript catqm.sql in $ORACLE_HOME/rdbms/admin kann die XML DB nachinstalliert werden.
  • In einer 11.2.0.4-Datenbank muss zusätzlich ein Patch eingespielt werden: 16199543. Dieser kann von der Oracle-Supportplattform support.oracle.com unter Patches & Updates heruntergeladen werden.
  • Der HTTP-Protokollserver der XML DB muss aktiviert sein. Normalerweise sind die FTP- und HTTP-Protokollserver deaktiviert. Nutzer des Embedded PL/SQL Gateway auf OracleXE arbeiten typischerweise mit bereits aktiviertem HTTP-Protokollserver, denn der wird hier für APEX genutzt. Wenn Ihr nicht auf OracleXE arbeitet oder euch generell nicht sicher seid, ob der HTTP-Protokollserver aktiv ist, könnt Ihr das mit einem lsnrctl status nachprüfen:
    $ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-MAY-2012 10:58:27
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    :
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
    :
    Services Summary...
    
    Ist diese Zeile nicht vorhanden, so ist der Protokollserver inaktiv. Dann aktiviert Ihr ihn einfach (als DBA) wie folgt. Verwendet nach Möglichkeit einen TCP/IP-Port über 1024. Für die Portnummern darunter muss auf UNIX-Systemen der Listener besonders eingerichtet werden.
    begin
      dbms_xdb.sethttport({http-port});
    end;
    
  • Die Database Native Webservices müssen (einmalig) innerhalb der XML DB aktiviert werden. Hierzu muss (als DBA) folgendes SQL-Kommando abgesetzt werden:
    DECLARE
      SERVLET_NAME VARCHAR2(32) := 'orawsv';
    BEGIN
      DBMS_XDB.deleteServletMapping(SERVLET_NAME);
      DBMS_XDB.deleteServlet(SERVLET_NAME);
      DBMS_XDB.addServlet(
        NAME     => SERVLET_NAME,
        LANGUAGE => 'C',
        DISPNAME => 'Oracle Query Web Service',
        DESCRIPT => 'Servlet for issuing queries as a Web Service',
        SCHEMA   => 'XDB'
      );
      DBMS_XDB.addServletSecRole(
        SERVNAME => SERVLET_NAME,
        ROLENAME => 'XDB_WEBSERVICES',
        ROLELINK => 'XDB_WEBSERVICES'
      );
      DBMS_XDB.addServletMapping(
        PATTERN => '/orawsv/*',
        NAME    => SERVLET_NAME
      );
    END;
    /
    

Damit sind die datenbankweiten Vorbereitungen erledigt. Zum Ausprobieren können wir den Datenbankuser SCOTT hernehmen. Zuerst bauen wir eine kleine PL/SQL-Funktion wie folgt:

create or replace function get_sal(
  p_empno in emp.empno%type
) return emp.sal%type is
  l_sal emp.sal%type := null;
begin
  begin
    select sal into l_sal from emp where empno = p_empno;
  exception
    when no_data_found then null;
    when others then raise;
  end;
  return l_sal;
end;

Diese Funktion GET_SAL wollen wir nun als Native WebService aufrufen. Die Datenbank ist soweit vorbereitet. Allerdings kann man nicht mit jedem Datenbankuser einfach so Webservices aufrufen - es sind Rollen nötig. Für Database Native Webservices gibt es dero drei:

  • XDB_WEBSERVICES: Ein Datenbankbankuser, der diese Rolle hat, ist prinzipiell in der Lage, PL/SQL Objekte und SQL-Abfragen als SOAP-Webservices auszuführen.
  • XDB_WEBSERVICES_OVER_HTTP: Der Datenbankuser kann die Webservices mit dieser Rolle auch über ungesichertes HTTP aufrufen - es wird dann also kein HTTPS benötigt. Ohne dieses Privileg wird der Webservice-Call nur über SSL erlaubt.
  • HTTP_WEBSERVICES_WITH_PUBLIC: Der Datenbankuser kann auch auf Objekte zugreifen, die "nur" über einen "Public" Grant bereitstehen. Ohne diese Rolle muss der Datenbankuser Eigentümer der Objekte sein oder explizite Privilegien haben.

Also bekommt unser Datenbankuser SCOTT zwei Privilegien:

grant xdb_webservices to scott
/

grant xdb_webservices_over_http to scott
/

Damit sind alle Vorbereitungen gemacht. Um einen Webservice per SOAP aufrufen zu können, braucht es das WSDL-Dokument, also die "Schnittstellenbeschreibung" für den Web Service. Die Database Native Webservices generieren dieses automatisch. Probiert im Browser einfach folgende URL aus:

http://{host}:{port}/orawsv/SCOTT/GET_SAL?wsdl

Im Browser werdet Ihr dann zuerst nach Usernamen und Passwort gefragt. Hier loggt Ihr euch mit SCOTT und seinem Passwort ein. Wichtig ist: Der Login muss mit dem Datenbankaccount erfolgen, welcher die XDB_WEBSERVICES-Rollen hat - es muss nicht zwingend der Eigentümer der aufgerufenen Funktion sein. Nach dem Login zeigt euch der Browser das WSDL-Dokument für den Webservice an.

Mit Hilfe dieser URL kann nun jeder SOAP-Client mit der Datenbankfunktion GET_SAL arbeiten. Der folgende Screenshot zeigt ein Beispiel - typischerweise wird zunächst die URL zum WSDL-Dokument angegeben; das Werkzeug liest danach die Informationen aus und generiert einen SOAP-Request als Vorlage. Dort kann man dann eine der EMPNOs aus der Tabelle EMP eintragen und den Request zum Server senden - die Antwort kommt wiederum als SOAP-Response.

Hier habe ich noch einen Screenshot aus Application Express - es ist zwar eine seltsame Idee, eine PL/SQL-Funktion aus APEX heraus per Webservice-Schnittstelle aufzurufen, aber es funktioniert.

Es ist also sehr einfach, eine PL/SQL-Funktion oder SQL-Query als SOAP-Webservice verfügbar zu machen - ein paar Dinge gibt es aber noch zu sagen:

Die oben genannten Privilegien erlauben zunächst keine feingranulare Steuerung, welche PL/SQL-Funktionen oder -Prozeduren als Web Service verfügbar gemacht werden sollen. Man vergibt nur die Rollen an einen Datenbankuser - und alle Objekte, die diesem User gehören, können dann als Web Service aufgerufen werden. Das scheint erstmal sehr grob - aber nur auf den ersten Blick. Denn die XDB_WEBSERVICES-Rollen solle man niemals dem Eigentümer der Objekte geben. Vielmehr sollte man einen eigenen "Webservice-Connect-User" einrichten - dieser bekommt eine "Minimalausstattung" an Privilegien ...

create user webservice_connect identified by {password};

grant create session to webservice_connect;
grant xdb_webservices to webservice_connect;
grant xdb_webservices_over_http to webservice_connect;

Und an diesen User werden nun die EXECUTE-Rechte an den PL/SQL-Objekten, die man als Webservice bereitstellen möchte, explizit und einzeln vergeben. Als SCOTT wird also folgendes ausgeführt ...

grant execute on scott.get_sal to webservice_connect;

Zum Abrufen des WSDL wird nun die gleiche URL verwendet, wie vorhin; der Login muss allerdings nun als WEBSERVICE_CONNECT erfolgen und nicht mehr als SCOTT. Das WSDL wird danach ganz genauso aussehen und der Webservice lässt sich auch genauso nutzen - nur kann man nun auf Objektebene festlegen, welche Funktionen und Prozeduren als Webservice bereitstehen. Eben nur diejenigen, an denen WEBSERVICE_CONNECT Rechte hat.

Packages können auf den ersten Blick nur als Ganzes freigegeben werden - das ist zunächst auch richtig. Auch mit einem Grant des Execute-Privilegs an WEBSERVICE_CONNECT wird das ganze Package freigeschaltet. Allerdings würde ich für ein Webservice-Szenario ein eigenes "Wrapper-Paket" schreiben, welches nur die für den Webservice freigegebene Schnittstelle enthält. Hier kann man auch gleich PL/SQL-eigene Datentypen wie boolean und record, die von den Native Webservices nicht unterstützt werden, auf SQL-Typen abbilden.

Möchte man nicht nur einen skalaren Datentypen (wie NUMBER oder VARCHAR2), sondern ein ganzes Objekt als Parameter übergeben, so können Objekttypen eingesetzt werden. Objekttypen können als Input- oder als Output-Parameter verwendet werden. Eine Funktion CREATE_EMP könnte dann so aussehen ...

create type emp_t as object(
 EMPNO      NUMBER(4),
 ENAME      VARCHAR2(10),
 JOB        VARCHAR2(9),
 MGR        NUMBER(4),
 HIREDATE   DATE,
 SAL        NUMBER(7,2),
 COMM       NUMBER(7,2),
 DEPTNO     NUMBER(2)
)
/

create or replace procedure create_emp(
  p_emp emp_t 
) as
begin
 :
end;
/

Hierzu lässt sich wiederum direkt ein WSDL abrufen (wie oben). Und der Objekttyp EMP_T wird hier auch als komplexer XML-Datentyp erkannt ...

<xsd:element name="EMP_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="EMPNO" type="xsd:double"/>
      <xsd:element name="ENAME">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="10"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="JOB">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="9"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="MGR" type="xsd:double"/>
      <xsd:element name="HIREDATE" type="xsd:date"/>
      <xsd:element name="SAL" type="xsd:double"/>
      <xsd:element name="COMM" type="xsd:double"/>
      <xsd:element name="DEPTNO" type="xsd:double"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

Auch Arrays (VARRAY oder Nested Table-Types) können verwendet werden, aber nicht auf "oberster" Ebene. So führt folgendes bereits beim Versuch, das WSDL abzurufen, zu einer Fehlermeldung:

create type varchar_table_t as table of varchar2(4000)
/

create or replace procedure my_func(
  p_vtab varchar_table_t 
) as
begin
 :
end;
/

Die Lösung ist einfach: Auf der obersten Ebene (also als Funktionsparamater) darf kein VARRAY oder TABLE-Typ verwendet werden. Es ist aber durchaus erlaubt, solche in einem Objekttypen zu nutzen. Wir wandeln das Beispiel also ein wenig um ...

create type varchar_table_t as table of varchar2(4000)
/

create type param_t as object(
  varchar_table varchar_table_t
);
/

create or replace procedure my_func(
  p_vtab param_t 
) as
begin
  :
end;
/

Nun wird das WSDL korrekt generiert und der Webservice kann verwendet werden. Hier der Auszug aus dem WSDL, welcher den komplexen Typ PARAM_T beschreibt.

<xsd:element name="PARAM_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="VARCHAR_TABLE">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="VARCHAR2" type="xsd:string" maxOccurs="unbounded" minOccurs="0"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

Alles in allem sind die Native Webservices ein sehr eleganter und einfacher Weg, SQL-Abfragen und PL/SQL-Objekte als SOAP-Style Webservices im Unternehmen bereitzustellen. Macht man es mit Java- oder .NET-Mitteln, muss hierfür meist aufwändig Code geschrieben werden. Mehr dazu findet Ihr in der Oracle-Dokumentation:

Oracle XML DB Developers' Guide: 33 Using Native Oracle XML DB Web Services

This blog posting is about Database native webservices; a feature, which was introduced with Oracle11g Release 1 (11.1). Database Native Webservices allow to publish a SQL Query or a PL/SQL unit (Function, Procedure, Package) as a SOAP Style Webservice without additional programming.

  • Database native Webservices are Part of the XML DB functionaliry. So make sure that XML DB is part of your database. The dictionary view DBA_REGISTRY allows to check this:
    select comp_id, version from dba_registry where comp_id='XML'
    
    COMP_ID                        VERSION
    ------------------------------ ---------------------
    XML                            11.2.0.2.0
    
    If this query returns no rows, then XML DB is not installed in your database - and you cannot use the webservice feature. XML DB can be installed into an existing database by starting the SQL script catqm.sql in $ORACLE_HOME/rdbms/admin. Of course, this must be done by a DBA.
  • On a 11.2.0.4 database, you also need to apply an additional oneoff-Patch: 16199543. It is available in the Patches & Updates Section on the Oracle Support Platform - support.oracle.com.
  • Nou need the XML DB protocol server for HTTP being activated. After a normal database creation these are disabled by default. OracleXE users probably will have an activated HTTP protocol server on port 8080 (it is being used for working with APEX). If you are not sure, simply check on the database server with the lsnrctl status command.
  • $ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-MAY-2012 10:58:27
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    :
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
    :
    Services Summary...
    
    If the red line is not present, the HTTP protocol server is disabled. It needs to be enabled with the following command (again: you need DBA privileges, at least the XDBADMIN role). On Linux or Unix systems choose a TCP/IP port above 1024; ports below require additional listener configuration.
    begin
      dbms_xdb.sethttport({http-port});
    end;
    
  • Now, having XML DB present and the protocol server enabled, you need to enable the webservice feature as such. The following command needs to be executed once - again with DBA privileges or the XDBADMIN role.
    DECLARE
      SERVLET_NAME VARCHAR2(32) := 'orawsv';
    BEGIN
      DBMS_XDB.deleteServletMapping(SERVLET_NAME);
      DBMS_XDB.deleteServlet(SERVLET_NAME);
      DBMS_XDB.addServlet(
        NAME     => SERVLET_NAME,
        LANGUAGE => 'C',
        DISPNAME => 'Oracle Query Web Service',
        DESCRIPT => 'Servlet for issuing queries as a Web Service',
        SCHEMA   => 'XDB'
      );
      DBMS_XDB.addServletSecRole(
        SERVNAME => SERVLET_NAME,
        ROLENAME => 'XDB_WEBSERVICES',
        ROLELINK => 'XDB_WEBSERVICES'
      );
      DBMS_XDB.addServletMapping(
        PATTERN => '/orawsv/*',
        NAME    => SERVLET_NAME
      );
    END;
    /
    

Now the database is ready to provide database native webservices. For testing we use a plain database account (say: SCOTT). First we create a little PL/SQL function as follows ...

create or replace function get_sal(
  p_empno in emp.empno%type
) return emp.sal%type is
  l_sal emp.sal%type := null;
begin
  begin
    select sal into l_sal from emp where empno = p_empno;
  exception
    when no_data_found then null;
    when others then raise;
  end;
  return l_sal;
end;

This function GET_SAL will now be executed as a native web service. The database has been prepared and the function is ready. Before actually doing it, we need some privileges (of course, this feature is being protected). There are three different roles.

  • XDB_WEBSERVICES: This role enables the database native web service feature in general. A user having this role can access own objects and objects with explicit privileges as SOAP style webservices. But for our test we will also need the next role.
  • XDB_WEBSERVICES_OVER_HTTP: With this role, the database user can access his webservices also over "plain" HTTP, i.e. SSL communication is not required. Without this role, webservice calls only work over HTTPS (the protocol server must be configured for HTTPS in that case).
  • .
  • HTTP_WEBSERVICES_WITH_PUBLIC: This role enables also executing public objects as web services. Without it, only own objects and objects with explicit privileges can be accessed.

So our database user SCOTT gets two additional roles ...

grant xdb_webservices to scott
/

grant xdb_webservices_over_http to scott
/

Now all requirements are met. The first thing a client needs in order to call the web service, is the WSDL document. The WSDL is the interface description in XML format. It contains information about the name of the webservice, its input and output parameters and so on. The Oracle database generates the WSDL automatically. Just open your browser and visit the URL as follows ...

http://{database-host}:{http-port}/orawsv/SCOTT/GET_SAL?wsdl

The browser not prompts you for username and password. Log in as wou would do in SQL*Plus. Keep in mind that you need the credentials of the database user having the XDB_WEBSERVICES roles. The object owner might be different. After logging in you'll see the WSDL as follows ...

Having that URL every SOAP client can call the PL/SQL function as a web service. Following you'll see some screen shots. The flow of operation is basically the same for all client: First, get the WSDL from the database, then send a SOAP request (which can be generated from the WSDL) and then receive the SOAP response. How each step is being performed, depends on the SOAP client you are actually using. The second screen shot is from Oracle Application Express (which also has a webservice interface).

Looking at the mentioned XDB_WEBSERVICES privileges it seems that only complete schemas can be enabled or disabled for webservices - the privileges can only be granted to database users. There is no PL/SQL package to work at the procedure or function level. But we can use standard database methods here. The XDB_WEBSERVICES roles should never be granted to the object owners directly. It's a better idea to create a separate "webservice connect user" with a limited set of privileges ...

create user webservice_connect identified by {password};

grant create session to webservice_connect;
grant xdb_webservices to webservice_connect;
grant xdb_webservices_over_http to webservice_connect;

Then the object (functiom procedure) owner does explicit and individual grants on the desired objects to the "webservice connect user". So, in our example, SCOTT issues the following GRANT statements ...

grant execute on scott.get_sal to webservice_connect;

We get the WSDL document we use the same URL as previously. But when the browser prompts for a login, we don't use SCOTT anymore - now we use the WEBSERVICE_CONNECT user. If the grants have been implemented correctly, we can just execute the one procedure SCOTT.GET_SAL as a native webservice.

When it's about packages there's a bit more of work to do: With the described approach we can enable or disable web service access only at the package level. When webservice access is being enabled for a particular package, all public functions and procedures in that package can be executed. If this should be limited to a reduced set of procedures and functions, we need a "wrapper package" on top of the functional one. The wrapper package contains just the functions and procedures to be enabled for webservice access. And execute privileges are then being granted on the wrapper package. Such a wrapper package can also be used to handle PL/SQL-only data types like boolean or record which are not supported by the webservice feature.

If object structures are needed as webservice arguments (in pure PL/SQL we might use records), then we need to create Object Types for this. Object types can be used as input or output arguments. A function CREATE_EMP with an object type argument could look like this:

create type emp_t as object(
 EMPNO      NUMBER(4),
 ENAME      VARCHAR2(10),
 JOB        VARCHAR2(9),
 MGR        NUMBER(4),
 HIREDATE   DATE,
 SAL        NUMBER(7,2),
 COMM       NUMBER(7,2),
 DEPTNO     NUMBER(2)
)
/

create or replace procedure create_emp(
  p_emp emp_t 
) as
begin
 :
end;
/

Now we can -again- retrieve the WSDL using the URL .../orawsv/SCOTT/GET_EMP?wsdl. Our input argument has been detected and "translated" to an XML structure. A SOAP client will now be able to contruct a matching SOAP request.

<xsd:element name="EMP_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="EMPNO" type="xsd:double"/>
      <xsd:element name="ENAME">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="10"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="JOB">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="9"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="MGR" type="xsd:double"/>
      <xsd:element name="HIREDATE" type="xsd:date"/>
      <xsd:element name="SAL" type="xsd:double"/>
      <xsd:element name="COMM" type="xsd:double"/>
      <xsd:element name="DEPTNO" type="xsd:double"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

We can also use VARRAY and nested table types to pass arrays or lists to the web service. But here we have one restriction. VARRAY or NESTED table types cannot be used at the "top-level", directly as procedure or function arguments. The database would then not be able to generate a WSDL and the web service cannot be called.

create type varchar_table_t as table of varchar2(4000)
/

create or replace procedure my_func(
  p_vtab varchar_table_t 
) as
begin
 :
end;
/

But there is a simple workaround: Just embed the VARRAY or table type into another object type - as illustrated in the following example:

create type varchar_table_t as table of varchar2(4000)
/

create type param_t as object(
  varchar_table varchar_table_t
);
/

create or replace procedure my_func(
  p_vtab param_t 
) as
begin
  :
end;
/

Now the WSDL can be generated and the webservice is callable again. Here is the WSDL part describing the input argument PARAM_T.

<xsd:element name="PARAM_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="VARCHAR_TABLE">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="VARCHAR2" type="xsd:string" maxOccurs="unbounded" minOccurs="0"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

As we have seen: It's very easy to publish PL/SQL objects as SOAP style webservices - just using the onboard utilities of the Oracle database: All we have to do is to enable the feature once and to grant privileges. In advanced scenarios we'll need additional wrapper packages or optimized data types for the procedure arguments.

Oracle XML DB Developers' Guide: 33 Using Native Oracle XML DB Web Services

Beliebte Postings