Accesing web services from Oracle

This problem represent a tough challenge even in modern programming languages(Java, C#) if you don't have a stub generator based on wsdl. I must say that Oracle 10g2 has a nice feature implemented in utl_dbws. This PL/SQL package has lots of function which allow programmer to use web services without using any java wrapper class. Unfortunately, this package is not very well documented by Oracle(it's not the only package in this situation). When I had to write a PL/SQL package which create a SOAP request for a web service and returns the result I spent many hours on google. Enough talking.

In the first part of this tutorial, I create a web service using J2EE tehnology(it's very easy).

ro.anaf.ws.HelloWorldWS

package ro.anaf.ws;

import javax.ejb.Remote;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.jws.soap.SOAPBinding;
import javax.jws.soap.SOAPBinding.Style;

@WebService
@SOAPBinding(style = Style.RPC)
@Remote
public interface HelloWorldWS
{
@WebMethod
public String helloWorld();

@WebMethod
public String receiveBinary(String s);
}



package ro.anaf.ws;

import javax.ejb.Stateless;
import javax.jws.WebService;

@Stateless
@WebService(endpointInterface = "ro.anaf.ws.HelloWorldWS")
public class HelloWorldWSBean implements HelloWorldWS
{
public String helloWorld()
{
return Hello fromWS";
}

public String receiveBinary(String s)
{
return "I have your message: \"" + s + "\"";
}
}


Now, you deploy your webservice on JBoss/Weblogic or whatever J2EE container you like. After this, we describe the Oracle stuff.

You have to download dbws-callout-utility-10131.zip. After this, you have to load the package into Oracle database.

# 10gR2
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar
# 11g
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar

If you don't have Oracle Developer Suite, then it's very probable that you won't be
able to run loadjava. We will suppose that you have Oracle Developer Suite and everything worked fine till now.

declare
 webservice utl_dbws.service;
 webserviceCall utl_dbws.call;

 wsdlURL VARCHAR2(1000) := 'http://10.18.14.32:8080/TestareServiciiEAR-ServiciiWeb/HelloWorldWSBean?wsdl';
 webserviceNS VARCHAR2(1000) := 'http://ws.anaf.ro/';
 webserviceName utl_dbws.qname;
 webservicePort utl_dbws.qname;
 webserviceOperation utl_dbws.qname;
 params utl_dbws.anydata_list;
 results anydata;
begin
 webserviceName := utl_dbws.to_qname(webserviceNS, 'HelloWorldWSBeanService');
 webservicePort := utl_dbws.to_qname(webserviceNS, 'HelloWorldWSBeanPort');
 webserviceOperation := utl_dbws.to_qname(webserviceNS, 'helloWorld');

 webservice := utl_dbws.create_service(wsdl_document_location => HTTPURITYPE(wsdlURL),
                                       service_name => webservicename);
                                                          
 webserviceCall := utl_dbws.create_call(service_handle => webservice,
                                        port_name => webservicePort,

operation_name => webserviceOperation);
                                                                            
 results := utl_dbws.invoke(call_handle => webserviceCall, input_params => params);

 utl_dbws.release_service(webservice);
 utl_dbws.release_call(webserviceCall);

 DBMS_OUTPUT.PUT_LINE(ANYDATA.AccessVarchar2(results));
end;


Above script shows an example of how you can access the webservice you have created in the first part of this tutorial.
Before the end, you have to know that in some cases you'll want to pass certain parameters to the webservice. In these cases
params(0):=ANYDATA.ConvertVarchar2('Cosnita Radu Viorel'); and of course you can add other parameters.