UTL_DBWS - Consuming Web Services in Oracle 10g - 11g

Using the UTL_DBWS package is essentially a PL/SQL wrapper over JPublisher. ahmed hazzaf

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 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 schema that needs access to the web client. To make sure you avoid errors during the load, set theJAVA_POOL_SIZE initialization parameter to at least 150M.

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

# Load into the SYS schema.
export PATH=$ORACLE_HOME/bin:$PATH
cd $ORACLE_HOME/sqlj/lib
# 10gR2
loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar
# 11g
loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

# Load into an individual schema.
export PATH=$ORACLE_HOME/bin:$PATH
cd $ORACLE_HOME/sqlj/lib
# 10gR2
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar
# 11g
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar

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

$ cd $ORACLE_HOME/sqlj/lib
$ sqlplus / as sysdba

SQL> @utl_dbws_decl
SQL> @utl_dbws_body

SQL> CREATE PUBLIC SYNONYM utl_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://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,
                                        p_int_2 IN NUMBER)
  RETURN NUMBER
AS
  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://www.oracle-base.com/webservices/server.php?wsdl';
  l_namespace       := 'http://www.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_operation_qname);

  l_xmltype_in := SYS.XMLTYPE('
    
      ' || p_int_1 || '
      ' || p_int_2 || '
    ');
  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
                                   request     => l_xmltype_in);
  
  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)
----------------
               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)
------------------
                25

SQL>