UTL_DBWS – Consuming Web services in Oracle

Applies to:
Web service – version 10.1.3.1.0 to 10.1.3.3.0Oracle Server – Enterprice Edition – Version: 10.2.0.2 and higher

Purpose:
Using UTL_DBWS to make a Database Callout to a Document Style Web service.
This article provides a method for consuming a Document Style Web Service using the UTL_DBWS package. This package is essentially a PL/SQL wrapper over JPublisher.


UTL_DBWS – Consuming Web services in Oracle:
First thing that must be done is, download the  Latest copu of the UTL_DBWS utility zip file from Oracle Technology Network (OTN). This file, for oracle 10-11g database, is named dbws-callout-utility-10131.zip and can be obtained from here. Extract the jar file from the zip into the /sqlj/lib directory.
The jar file can be loaded into any schema that needs access to the web client.
For loading the jar file into the schema, make user the PATH environment variable has the /bin directory included, and from a command window run the following:
 
cd /sqlj/lib  
(replacing )


For a 10.1.0.x database:
loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb101.jar

For a 10.2.0.x database:
loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb102.jar
For a 11.1 or later database:
loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

In either case, replace the username/password designation with whatever applies in the  database environment. My advice is to create a separated user for the java executables inside the database  instead of using the default users.
In Oracle 10g the UTL_DBWS package is loaded by default, so it is not necessary to run any scripts to initiate this functionality. However, there are a series of grants that have to be performed on the schema containing the jar files you have loaded. The grants only have to be performed once before the functionality is available. From the SYS schema, run the following commands:
 
REM validation of the Jar files into the database

Select owner, status count(1) from dba_objects
where object_type = ‘JAVACLASS’
and owner=''
group by owner, status;

REM SYS grants to the schema

execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','http.proxyHost', 'write');

execute dbms_java.grant_permission(','http.proxyPort', 'write');

execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission'
, 'accessClassInPackage.sun.util.calendar','');


execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission'
,'getClassLoader','');


execute dbms_java.grant_permission('','SYS:java.net.SocketPermission','*'
,'connect,resolve');


execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','*'
,'read,write');


execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission'
,'setFactory','');



The designation should be replaced with the actual schema name being used. It is also worth noting that the first three commands are not necessary if the environment does not use a proxy server.

Security Acces Control List:
From Oracle RDBMS 11g a new security is added to the database on network-related PL/SQL packages. A lot of the network-related  PL/SQL utility packages are created with execute privilege granted to public. Oracle Database 11g offers you fine-grained access control capability so you can control the access of users to external network service from within the Oracle database. Fine grained access means that you can now choose which host computer a user can connect to from the Oracle database. De new Oracle packages  DBMS_NETWORK_ACL_ADMIN and BMS_NETWORK_ACL_UTILITY to create and maintain access control list (ACL) for database users. Another option is creating the access control list through Oracle XML DB.In Oracle 11g database you need to create a ACL for the user to access the external network host.If ACL in RDBMS 11g is not designed than you will get the following error:
 
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: HTTP transport       error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException:
       javax.xml.soap.SOAPException: Message send failed:
       HTTPClient.AuthSchemeNotImplException: NTLM
       ORA-06512: at "SYS.UTL_DBWS", line 404
       ORA-06512: at "SYS.UTL_DBWS", line 401
       ORA-06512: at "SYS.GET_SHAKESPEARE", line 25

 
Example with UTL_DBWS – Consuming Web services in Oracle:The web service example that I use is from a sitehttp://www.xmlme.com/WSShakespeare.asmx
The phrase from the plays of William Shakespeare and returns the associated speech, speaker, and play. The Shakespeare texts used in this Web Service are the XML versions developed by Jon Bosak. Go to http://www.oasis-open.org/cover/bosakShakespeare200.html for more information on the texts. See here for a sample client and additional details about the Shakespeare Web Service.
The function below uses the UTL_DBWS package to access the web service Shakespeare phrase from PL/SQL.
 
create or replace function get_shakespeare(p_hrase in varchar2)
    RETURN varchar2
AS
 
 
l_service                 utl_dbws.SERVICE;
  l_call                    utl_dbws.CALL;
  l_service_qname           utl_dbws.QNAME;
  l_port_qname              utl_dbws.QNAME;
  l_operation_qname         utl_dbws.QNAME;
  l_string_type_qname       utl_dbws.QNAME;
  l_namespace               varchar2(1000);
  l_retx                    sys.xmltype;
  l_xml_string              sys.xmltype;
Begin
  l_namespace       := 'http://xmlme.com/WebServices';
  l_service_qname   := sys.utl_dbws.to_qname(l_namespace, 'Shakespeare');
  l_service         := sys.utl_dbws.create_service(HTTPURITYPE('http://www.xmlme.com/WSShakespeare.asmx?WSDL'), l_service_qname);
  l_port_qname      :=  sys.utl_dbws.to_qname(l_namespace, 'ShakespeareSoap');
  l_operation_qname := sys.utl_dbws.to_qname(l_namespace, 'GetSpeech');
  l_call            := sys.utl_dbws.create_call(l_service, l_port_qname, l_operation_qname);
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property(l_call_, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech');
  sys.utl_dbws.set_property(l_call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
  sys.utl_dbws.set_property(l_call_, 'OPERATION_STYLE', 'document');
  l_string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  sys.utl_dbws.add_parameter(l_call, 'Request', l_string_type_qname, 'ParameterMode.IN');
  sys.utl_dbws.set_return_type(l_call, l_string_type_qname);
  l_xml_string := xmltype(' '||p_phrase||'  ');
  l_retx := sys.utl_dbws.invoke(call_Handle => l_call_,request => l_xml_string);
  return l_retx.extract('/*').getstringval();
  sys.utl_dbws.release_service(l_service);
end;
/

The test scripts
 
select  get_shakespeare (‘To be, or not to be’) from dual;
select  get_shakespeare (‘My kingdom for a horse’) from dual;
select  get_shakespeare (‘Get thee to a nunnery’) from dual;
select  get_shakespeare (‘There are more things in heaven and earth’) from dual;

 
It is also worth pointing out that creating your own UTL_DBWS code by hand is only to be used for contacting simple web services. The recommended approach to use the web service callout functionality once the appropriate jars have been loaded into the database is todetermine the WSDL for the web service and use the JPublisher tool to create the static Java and PL/SQL code to be used to invoke web service from PL/SQL. 

Resources For more information on making web service callouts from the database, see:
·       Callout Users Guide
·       UTL_DBWS (10g)
·       Database Web Services Samples and How-To’s
·       Developer JPUBLISHER - Simplifying Database Access
·       Basic Web service Call-out using PL/SQL UTL_HTTP (For use with 9i R2 databases)
·       Oracle Metalink note 412666.1