Using Utl_dbws to make a Database 11g Callout to a Document Style Web Service

In this Document

Purpose

Scope

Details

Applies To:Oracle database-enterprise edition-version 11.1.0.6 and later
Web services-version 10.1.3.1.0 and later
information in this document applies to any platform.
PURPOSE

This article provides a method for consuming a Document Style Web Service using the Utl_dbws pack in the 11G version of  The database. UTL_DBWS is a PL/SQL database package, contains a series of commands that developers can use to create a runtime pl/sq L procedure or function that would allow for the transfer of information from a Web service to the database.

SCOPE

This article are intended for Web Service developers.

DETAILS

The steps to be used is as follows:

1) Download The LATEST copy of the Utl_dbws utility zip file from the Oracle technology Network (OTN). This file, the for a 11G database, is named Dbws-callout-utility-10131.zip and can are obtained from here.

2) Extract the contents of the zip file to the $ORACLE _home directory. This would place the contents of the zip in the $ORACLE _home/sqlj/lib directory.

3) The 11.1 version of the database contains the UTL_DBWS PL/SQL package in the SYS schema by default, but the 11.2 versio N does not has this loaded automatically.  If you is using the the 11.2 version, you can run the appropriate PL/SQL PAC Kage body and specification in order to install the core PL/SQL code into whatever schema you wish to use.  the code Used for the example would assume that the SYS schema is used, as per the default installation for the 11.1 version, but I T is possible to load this package into the same schema, that's being used for the Loadjava commands performed in step 4 b elow.  the packages to be loaded is named Utl_dbws_body.sql and Utl_dbws_decl.sql and exist in the $ORACLE _HOME/SQLJ /lib directory After the callout utility zip have been unzipped into the $ORACLE _home directory.  Once the In place, was important to perform a couple of checks to ensure the database environment are ready for use with the callout mechanism. 

A) Make sure the initialization parameters shared_pool_size and java_pool_size is equal to or greater than 96M and 80M, I . E.,

shared_pool_size=96m
java_pool_size=80m

b) Check to ensure, the JVM objects in the SYS schema is valid. You can check the here logging into the database as SYS and running the following query:

SELECT owner, status, Count (*) from dba_objects
WHERE object_type= ' JAVA CLASS '
GROUP by owner, status;

If There is classes that is listed as being in a ' INVALID ' state, you can run the following script to attempt to make T hese objects VALID:

$ORACLE _home/rdbms/admin/utlrp.sql

Once This has been run, recheck the status of your Java objects. If there is still any that is INVALID, it may is necessary to contact Oracle support to determine how to make these Obje CTS VALID before continuing.

4) load the necessary core Web services callout jar files into the database.  This step are to Load the core Java comp Onents and is a completely separate action from loading the PL/SQL package as described on the previous steps.  this means the considerations for completing this step is entirely different from the loading of the PL/SQL COMPONENTS.&N Bsp In the 11gR2 version of the database,  it had been determined that loading the jars into ths SYS schema would cause Co Nflicts with existing classes already loaded in this schema by default so a user created schema would need to be used FOR&N Bsp Loading of these jars (CONNECT, RESOURCE and CREATE public synonym is the minimum grants that has to is performed on thi s schema) .  for 11.1.0.x, this Java loading restrication does not exist, and it's possible to load the jars into the SYS Scheama, although it is strongly recommended that a user defined schema being created for the loading of the jars in thi S version as Well.  by separating the loading of the callout classes to its own schema, even for the 11gR1 version, it should ENS Ure that no conflicts would occur with classes that has been already loaded into the database in other schemas, so it is g Ood practice in all cases to create a user defined schema for the loading of the jar files necessary for the use of the UT L_DBWS package.

When loading the jar files on the desired schema, make sure the PATH environment variable includes the $ORACLE _home/bin di  Rectory. From a terminal window, run the following commands:

CD $ORACLE _home/sqlj/lib (replacing $ORACLE _home with the proper directory structure)
Loadjava-u username/password-r-v-f-s-grant public-genmissing Dbwsclientws.jar Dbwsclientdb11.jar

Replace the Username/password designation with whatever applies in your particular database environment.

5) There is a series of permission grants that must be performed. These grants only has the to being performed once before the functionality is available. From the SYS schema, run the following commands:

Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.util.PropertyPermission ', ' http.proxyset ', ' write ') ;
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.util.PropertyPermission ', ' http.proxyhost ', ' write ' );
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.util.PropertyPermission ', ' http.proxyport ', ' write ' );
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.lang.RuntimePermission ', ' AccessClassInPackage.sun.util.calendar ', ');
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.lang.RuntimePermission ', ' getclassloader ', ');
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.net.SocketPermission ', ' * ', ' connect,resolve ');
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.util.PropertyPermission ', ' * ', ' read,write ');
Execute dbms_java.grant_permission (' <SCHEMA> ', ' SYS:java.lang.RuntimePermission ', ' setfactory ', ');

The <SCHEMA> designation is to be replaced with the actual SCHEMA name being used. The name must also is designated in caps.

If your environment does not use a proxy server, the first three dbms_java.grant_permission statements listed does not need To is executed and can be ignored.

6) as a test, the following UTL_DBWS function can be used as a test to ensure the callout mechanism is installed correctly . The function calls an external Web service, the converts Celsius temperatures to Fahrenheit.

CREATE OR REPLACE FUNCTION Celciustofahrenheit (temperature number) RETURN VARCHAR2 as

Service_ Sys.utl_dbws. SERVICE;
Call_ Sys.utl_dbws. Call;
Service_qname Sys.utl_dbws. QNAME;
Port_qname Sys.utl_dbws. QNAME;
Response sys. XMLTYPE;
Request Sys. XMLTYPE;

BEGIN
Sys.utl_dbws.set_http_proxy (' myproxyserver:8080 ');
Service_qname: = Sys.utl_dbws.to_qname (null, ' Celciustofahrenheit ');
Service_: = Sys.utl_dbws.create_service (Service_qname);
Call_: = Sys.utl_dbws.create_call (Service_);
Sys.utl_dbws.set_target_endpoint_address (Call_, ' http://webservices.daehosting.com/services/TemperatureConversions.wso ');
Sys.utl_dbws.set_property (Call_, ' Operation_style ', ' document ');
Request: = sys. XMLTYPE (' <celciustofahrenheit xmlns= ' http://webservices.daehosting.com/temperature ' ><nCelcius> ' | | temperature| | ' </nCelcius></CelciusToFahrenheit> ');
Response: =sys.utl_dbws.invoke (Call_, request);
Return Response.extract ('//celciustofahrenheitresult/child::text () ', ' xmlns= ' http://webservices.daehosting.com/ Temperature "'). Getstringval ();
END;
/

Note:  The SYS prefix for the UTL_DBWS calls assumes, the PL/SQL package are loaded into SYS. If This isn't the case in the particular environment being used, and the the package is loaded to another schema, please MA Ke the adjustments necessary to the code to reflect the schema of that holds the UTL_DBWS package.

BACKGROUND of CODE used
------------------------

The Sys.utl_dbws.set_http_proxy call was used take to account no proxy server that would exist in the database Environme Nt. The value provided is a example of the syntax, and must be replaced with the values that suit the database environment. This line of code can is removed entirely if no proxy server exists in the environment.

The Sys.utl_dbws.to_qname call provides a mechanism to provide the service and call name necessary for the operations to B E performed. Due to the simplicity of the Web service being used for testing, NULL are all that's necessary to provide for the service Name. In a production environment, this would likely is a particular value that would has to is determined and provided in Plac E of NULL. The operation name is Celciustofarenheit. This value can is found by examining the WSDL, and looking for the <operation> tag and determining the name Paramete R. For the example above, the WSDL can is found at the following URL:

Http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL

The request parameter is created as a XMLTYPE, as Document Style Web services use XML parameters both for input and OUTPU T. It is necessary to determine what the Web service was expecting to receive in It's entirety as a request in order to SUP Ply the correct information to this parameter. Determine the syntax contained in the <soap:Body> tag for the request envelope of the service to get the value to be Passed in the XMLTYPE variable. For the same reason, the response is a XMLTYPE as well. This makes it-simple-to-use the extract method of the-this type-to-get the result in a form that can be used for whatever purp OSes desired.

The output below shows the function in action.

Sql> SELECT Celciustofahrenheit (+) from dual;


Celciustofahrenheit (30)
------------------------------------------------------------------------------
86

Sql>

This example is built using a third-party Web service. If you find in testing, it's fails to function, it is due to the Web service being taken offline. By executing the following URL in a browser window:

Http://webservices.daehosting.com/services/TemperatureConversions.wso

You should being able to determine if this is the case.

If you would prefer does not have UTL_DBWS for your Web services callout, there are another approach can be taken. This uses the Jpublisher utility to create a Java Stored Procedure with a PL/SQL wrapper that can is used to access the CO  De. This approach are outline in the following note, available on MetaLink:

Note 469588.1-dbws Callout Utilities User ' s Guide for RDBMS 11.1

For more information in making Web service callouts from the database, see:

Callout Users Guide
UTL_DBWS Package Reference
Developing a Web Service Client in the Database

Join the Java Development MOS Community Forum for general discussions, questions, best practices, and other valuable infor Mation on:oracle JDeveloper and ADF, Oracle Weblogic-jee Programming (EJB, JMS etc), Oracle JDBC, Oracle Web Services ( incl. dbws Callout Utility), Oracle Web Services Manager (OWSM), Oracle Service Registry (OSR), Oracle Toplink (Eclipselin k), Sun NetBeans Ide/java Studio Creator & Java Studio Enterprise, oc4j, KODO.

Using Utl_dbws to make a Database 11g Callout to a Document Style Web Service