Search This Blog

Thursday, February 01, 2007

How to use UTL_DBWS in 10.2 Database

Set the following in the init.ora file

1. java_pool_size big integer 96M
shared_pool_size big integer 104M

2. Download the LATEST copy of the UTL_DBWS utility zip file from the Oracle Technology Network (OTN).
This file, for a 10g database, is named dbws-callout-utility-10131.zip and can be obtained from here.
http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

3. Load this packages to the database using the command ,

loadjava -u sys/welcome -r -v -f -s -grant public -genmissing sqlj/lib/dbwsclientws.jar sqlj/lib/dbwsclientdb102.jar


4. As a sys user run the following commands ,

execute dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');

execute dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');

execute dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');

execute dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');

execute dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');

execute dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');

execute dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');

execute dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');

commit;

5. Start the 10.1.3.1 J2EE Container and deploy the application javacallout.ear

6. Run the following pl/sql command to check the pl/sql web services

set serveroutput on size 999999
declare
service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
operation_qname sys.utl_dbws.QNAME;
string_type_qname sys.utl_dbws.QNAME;
retx ANYDATA;
retx_string VARCHAR2(100);
retx_len number;
params sys.utl_dbws.ANYDATA_LIST;
begin
service_qname := sys.utl_dbws.to_qname(null, 'HelloServiceEJB');
service_ := sys.utl_dbws.create_service(service_qname);
port_qname := sys.utl_dbws.to_qname(null, 'HttpSoap11');
operation_qname := sys.utl_dbws.to_qname('http://oracle.j2ee.ws/javacallout/Hello/types', 'sayHelloElement');
call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
sys.utl_dbws.set_target_endpoint_address(call_, 'http://indl224ad.idc.oracle.com:8082/javacallout/javacallout');
--sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
sys.utl_dbws.add_parameter(call_, 'String_1', string_type_qname, 'ParameterMode.IN');
sys.utl_dbws.set_return_type(call_, string_type_qname);
params(0) := ANYDATA.convertvarchar('hello');
retx := sys.utl_dbws.invoke(call_, params);
retx_string := retx.accessvarchar2;
dbms_output.put_line('PL/SQL DII client return ' || retx_string);
end;
/

1 comment:

anish said...

I am trying to use utl_dbws utility .
I have to use a webservice that validate the address that i have passed and return me some boolean value whther its correct or not , if its not correct it should return possible matches for that address.

For writing the code in pl/sql - do we need to install any library files from the webservice vendor?
I know if you are calling any webservice from .net , you have to install some libraries from that webservice .