Wednesday, November 15, 2006

Calling a BPEL process with UTL_DBWS PLSQL package

After struggeling with low level SOAP calls from PLSQL. I was able to use the UTL_DBWS package. This package gives you an abstract layer on top of the low-level SOAP calls.

On OTN the latest version of this package can be downloaded.
You have to execute the following steps:

  1. Download the CallOut WebService zip file.
  2. Execute note: 276554.1 (located at metalink.oracle.com)
  3. Load under 'sys' account the files dbwsa.jar then dbwsclient.jar (from the previous donwloaded zip files)
  • loadjava -u sys/change_on_install -r -v -f -genmissing –s –grant public dbwsa.jar
  • loadjava -u sys/change_on_install -r -v -f -genmissing –s –grant public dbwsclient.jar
After this step, apart of errors during the loading of the jar files, you must load the two sqlfiles; utl_dbws_body.sql, utl_dbws_decl.sql as owner sys.

Now you should able to call a web service from the database, here is an example of calling a BPEL process based on a document style (rpc is also working).




DECLARE
service_ utl_dbws.SERVICE;
call_ utl_dbws.CALL;
service_qname utl_dbws.QNAME;
port_qname utl_dbws.QNAME;
xoperation_qname utl_dbws.QNAME;
xstring_type_qname utl_dbws.QNAME;
response sys.XMLTYPE;
request sys.XMLTYPE;
begin
-- Set a proxy if needed
-- utl_dbws.set_http_proxy('www-proxy.oracle.com:80');
service_qname := utl_dbws.to_qname(null, 'HelloWorld');
service_ := utl_dbws.create_service(service_qname);
--
call_ := utl_dbws.create_call(service_);
--
utl_dbws.set_target_endpoint_address(call_,
'http://oracle.nl:7779/orabpel/default/HelloWorld/1.0');
utl_dbws.set_property(call_,'SOAPACTION_USE','TRUE');
utl_dbws.set_property(call_,'SOAPACTION_URI','process');
utl_dbws.set_property(call_,'OPERATION_STYLE','document');
--
-- Set the input
--
request := sys.XMLTYPE('
<HelloWorldProcessRequest
xmlns="http://oracle.nl/HelloWorld">
<input xmlns="http://oracle.nl/HelloWorld">
1234abcd
</input>
</helloworldprocessrequest>');
--
response := utl_dbws.invoke(call_, request);
--
dbms_output.put_line(response.getstringval());
end;
/




The UTL_DBWS package is described here.

Post a Comment