Utl_dbws-consuming Web Services in Oracle 10g onward

from:http://oracle-base.com/articles/10g/utl_dbws-10g.php

In a previous article I presented a method for consuming Web Services using a basic SOAP implementation. This article provides similar functionality, but this time using UTL_DBWS the package, which is essentially a PL/SQL wrapper Over Jpublisher.

First, download the latest copy of the Dbwsclient.jar file:

• Pre 10g:dbws-callout-utility.zip (10.1.2)
• 10g:dbws-callout-utility-10r2.zip (10.1.3.0)
• 10g, 11g & 12c Latest:dbws-callout-utility-10131.zip (10.1.3.1)

Extract the jar file from the zip file into the "$ORACLE _home/sqlj/lib" directory.

the jar file can be loaded into the SYS schema for everyone to access, or into an individual s Chema that needs access to the Web client. To make sure-avoid errors during the load, set the  java_pool_size  initialization parameter to a t least 150M.

export oracle_home=/u01/app/oracle/product/10.2.0/db_1# Load into the SYS schema.export path= $ORACLE _home/bin:$ PATHCD $ORACLE _home/sqlj/lib# 10gr2loadjava-u sys/password-r-v-f-genmissing-s-grant public Dbwsclientws.jar dbwscli entdb102.jar# 11g and 12cloadjava-u sys/password-r-v-f-genmissing-s-grant public Dbwsclientws.jar Dbwsclientdb11.ja r# Load into an individual schema.export path= $ORACLE _home/bin: $PATHcd $ORACLE _home/sqlj/lib# 10gr2loadjava-u scott/  Tiger-r-v-f-genmissing dbwsclientws.jar dbwsclientdb102.jar# 11g & 12cloadjava-u scott/tiger-r-v-f-genmissing Dbwsclientws.jar Dbwsclientdb11.jar 

In Oracle 10g, the UTL_DBWS loaded by default. In Oracle 9i, 11g and 12c the package must is loaded using the specification and body provided in the zip file. The EXECUTE privilege should is granted on the package to any of the ULT_DBWS users needing access to the functionality.

$ cd $ORACLE _home/sqlj/lib$ sqlplus/as sysdbasql> @utl_dbws_declSQL > @utl_dbws_bodySQL > CREATE public synonym U Tl_dbws for SYS.UTL_DBWS; Sql> GRANT EXECUTE on Sys.utl_dbws to test;

the function below uses the  utl_dbws  package to access a web services from PL/SQL. The URL of the WDSL file describing the Web service is shown here (HTTP://ORACLE-BASE.COM/WEBSERVICES/SERVER.PHP?WSDL). The Web service accepts the parameters and returns the sum of those values.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1 in number, p_int_2 in number) Retu  RN Numberas L_service Utl_dbws.service;    L_call Utl_dbws.call;  L_wsdl_url VARCHAR2 (32767);  L_namespace VARCHAR2 (32767);  L_service_qname Utl_dbws.qname;  L_port_qname Utl_dbws.qname;  L_operation_qname Utl_dbws.qname; L_xmltype_in SYS.  XMLTYPE; L_xmltype_out SYS.  XMLTYPE; L_return number;  BEGIN l_wsdl_url: = ' http://oracle-base.com/webservices/server.php?wsdl ';  L_namespace: = ' http://oracle-base.com/webservices/';  L_service_qname: = Utl_dbws.to_qname (L_namespace, ' Calculator ');  L_port_qname: = Utl_dbws.to_qname (L_namespace, ' calculatorport ');  L_operation_qname: = Utl_dbws.to_qname (L_namespace, ' ws_add ');           L_service: = Utl_dbws.create_service (wsdl_document_location = Urifactory.geturi (l_wsdl_url), service_name  = L_service_qname); L_call: = Utl_dBws.create_call (Service_handle = l_service, Port_name = l_port_qname, Operation_name = L_opera  Tion_qname); L_xmltype_in: = SYS. XMLTYPE (' <?xml version= ' 1.0 "encoding=" Utf-8 "?> <ws_add xmlns=" ' | | | l_namespace | | ' > <int1> ' | | p_int_1 | | ' </int1> <int2> ' | | p_int_2 | |  ' </int2> </ws_add> '); L_xmltype_out: = Utl_dbws.invoke (Call_handle = l_call, request = L_xmltype_i    n);  Utl_dbws.release_call (call_handle = L_call);  Utl_dbws.release_service (service_handle = L_service);  L_return: = L_xmltype_out.extract ('//return/text () '). Getnumberval (); RETURN L_return; end;/

The output below shows the function in action.

SELECT add_numbers (1, 5) from dual; Add_numbers (1,5)----------------               6sql>select add_numbers (from dual); Add_numbers (10,15)------------------                25sql>

For more information see:

• Consuming Web Services (9i)
• Apex_web_service:consuming SOAP and REST WEB Services
• Utl_http and SSL (HTTPS) using Oracle wallets
• Fine-grained Access to Network Services in Oracle Database 11g Release 1
• UTL_DBWS (10g)
• Database Web Services
• Virtualize Your Oracle Database with Web Services

Utl_dbws-consuming Web Services in Oracle 10g onward