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