Steve's Blog Technical Information on Oracle Middleware.

I am going to show a simple example that will call my web service from my database. I will be using JDev 10.1.3.x to create and run my web service. My database and web service will all be running from my local machine.

1. My first step is to upgrade my database to 10.2.0.2 or later. UTL_DBWS will not work on 10.2.0.1. While its upgrading, I need to download the latest UTL_DBWS from
http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html

* I am going to use 10.1.3.1 Callout Utility for 10g and 11g RDBMS (ZIP, ~13MB)

Extract the downloaded file like:

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME
unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj
unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

2. Ensure that I have $ORACLE_HOME/bin in my path. First, I need to run the following command to load the libraries (run this from the $ORACLE_HOME/sqlj/lib folder).

loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb102.jar

The username and password is this example will be the sys user.


Check my database is upgraded/correct and load the jar files.

NOTE: This may take a while to run.

3. Create a new user to use web services utl_dbws package and give grant privileges.

create user wsuser identified by wsuser
default tablespace users
temporary tablespace temp
/

grant connect, resource to wsuser;



4. Once the packages had been loaded (step 2), the next step is to provide grants from the schema where the jar files have been loaded to our 'WSUSER'. These grants will have to run from where the libraries are installed. 'SYS' user.


execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission', 

'accessClassInPackage.sun.util.calendar','');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'getClassLoader','');



execute dbms_java.grant_permission('<user schema>','SYS:java.net.SocketPermission','*',

'connect,resolve');



execute dbms_java.grant_permission('<user schema>','SYS:java.util.PropertyPermission','*',

'read,write');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'setFactory','');





5. Now using Jdev, I create my java class as follows.

package ws;

public class HelloWorld {
    public HelloWorld() {
    }
    
    public String sayHelloWorld(){
        return "Hello World";
    }
    
    public String sayHelloName(String n){
        return "Hello " + n;
    }
}

6. I then create a web service from this class.



* Select "J2EE 1.4 (JAX-RPC) Web Service"

I then click on finished at Step 1 of 8 of the 'Create Java J2EE 1.4 Web Service' wizard so it uses all the default setting.

7. Then I ran the web service in the embedded server.



And checked my WS WSDL to ensure it is running.

http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort
http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort?WSDL



This is how my deployed WSDL looks like:

This XML file does not appear to have any style information associated with it. The document tree is shown below.

-<definitions name="MyWebService1" targetNamespace="http://ws/">
 -<types>
  -<schema targetNamespace="http://ws/types/" elementFormDefault="qualified">
   -<element name="sayHelloNameElement">
    -<complexType>
      -<sequence>
         <element name="n" type="string" nillable="true"/>
      -</sequence>
     </complexType>
    </element>
   -<element name="sayHelloNameResponseElement">
    -<complexType>
     -<sequence>
       <element name="result" type="string" nillable="true"/>
      </sequence>
     </complexType>
    </element>
   -<element name="sayHelloWorldElement">
    -<complexType>
      <sequence/>
     </complexType>
    </element>
   -<element name="sayHelloWorldResponseElement">
    -<complexType>
     -<sequence>
       <element name="result" type="string" nillable="true"/>
      </sequence>
     </complexType>
    </element>    
   </schema>
  </types>
 -<message name="MyWebService1_sayHelloName">
   <part name="parameters" element="tns0:sayHelloNameElement"/>
  </message>
 -<message name="MyWebService1_sayHelloNameResponse">
   <part name="parameters" element="tns0:sayHelloNameResponseElement"/>
  </message>
 -<message name="MyWebService1_sayHelloWorld">
   <part name="parameters" element="tns0:sayHelloWorldElement"/>
  </message>
 -<message name="MyWebService1_sayHelloWorldResponse">
   <part name="parameters" element="tns0:sayHelloWorldResponseElement"/>
  </message> 
 -<portType name="MyWebService1">
  -<operation name="sayHelloName">
    <input message="tns:MyWebService1_sayHelloName"/>
    <output message="tns:MyWebService1_sayHelloNameResponse"/>    
   </operation>
   <operation name="sayHelloWorld">
    <input message="tns:MyWebService1_sayHelloWorld"/>
    <output message="tns:MyWebService1_sayHelloWorldResponse"/>
   </operation> 
  </portType>
 -<binding name="MyWebService1SoapHttp" type="tns:MyWebService1">
   <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
  -<operation name="sayHelloName">
    <soap:operation soapAction="http://ws//sayHelloName"/>
   -<input>
     <soap:body use="literal"/>
    </input>
   -<output>
     <soap:body use="literal"/>
    </output>
   </operation>
   -<operation name="sayHelloWorld">
    <soap:operation soapAction="http://ws//sayHelloWorld"/>
   -<input>
     <soap:body use="literal"/>
    </input>
   -<output>
     <soap:body use="literal"/>
    </output>
   </operation>
  </binding>
 -<service name="MyWebService1">
  -<port name="MyWebService1SoapHttpPort" binding="tns:MyWebService1SoapHttp">
    <soap:address 
      location="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort"/>
   </port>
  </service>
</definitions>

8. Now using the UTL_DBWS package, I will create my PL/SQL code.

CREATE OR REPLACE PACKAGE HelloWorld 
AS

FUNCTION SayHelloWorld RETURN VARCHAR2;
FUNCTION sayHelloName(yourname IN VARCHAR2) RETURN VARCHAR2;

END HelloWorld;
/

CREATE OR REPLACE PACKAGE BODY HelloWorld AS

FUNCTION SayHelloWorld 
RETURN VARCHAR2 
IS

  service_           sys.utl_dbws.SERVICE;
  call_              sys.utl_dbws.CALL;
  service_qname      sys.utl_dbws.QNAME;
  port_qname         sys.utl_dbws.QNAME;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.XMLTYPE;
  request            sys.XMLTYPE;

BEGIN  
  service_qname := sys.utl_dbws.to_qname(null, 'SayHelloWorld');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_
    , 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_USE'
                           , 'TRUE'
                           );

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_URI'
                           , 'http://ws//sayHelloWorld'
                           );
 
  sys.utl_dbws.set_property( call_
                         , 'OPERATION_STYLE'
                         , 'document'
                          );

  request := sys.XMLTYPE('<sayHelloWorldElement xmlns="http://ws/types/" />');

  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//child::text()'
    , 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
    MyWebService1SoapHttpPort"').getstringval();

END SayHelloWorld;

FUNCTION sayHelloName(yourname IN VARCHAR2) 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;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.XMLTYPE;
  request            sys.XMLTYPE;

BEGIN  
  service_qname := sys.utl_dbws.to_qname(null, 'SayHelloName');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_
    , 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_USE'
                           , 'TRUE'
                           );

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_URI'
                           , 'http://ws//sayHelloName'
                           );
 
  sys.utl_dbws.set_property( call_
                         , 'OPERATION_STYLE'
                         , 'document'
                          );

  request := sys.XMLTYPE('<sayHelloNameElement xmlns="http://ws/types/">'
                        ||'<n>' || yourname || '</n> </sayHelloNameElement>');

  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//child::text()'
    , 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
    MyWebService1SoapHttpPort"').getstringval();

END SayHelloName;

END HelloWorld;
/

9. I then ran my package:

select helloworld.sayhelloworld from dual;

select helloworld.sayhelloname('Steve') from dual;