21-MAR-08 Instructions on how to use Oracle10gR2 Webservice Call-Out feature (Oracle JAX-RPC DII - Dynamic Invocation (DII) APIs) by Henry C. Wu PREREQUISITE ================================================================= 1. Download Oracle Database 10g Release 2 (10.2.0.1.0) (ZIP, ~655MB) http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201winsoft.html http://download.oracle.com/otn/nt/oracle10g/10201/10201_database_win32.zip 1.1 Choose Enterprise Edition 2. Download 10.1.3.1 Callout Utility for 10g (R1 +R2) RDBMS (ZIP, ~13MB) http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip 2.1 Extract to C:\oracle\product\10.2.0\db_1\dbws_callout_utility_10131 3. Download OC4J Standalone - Oracle Containers for J2EE (OC4J) 10g Release 3 (10.1.3.3) (ZIP, ~93MB; Pure Java—runs on all certified platforms) http://www.oracle.com/technology/software/products/ias/index.html (Download Site) http://download.oracle.com/otn/java/oc4j/101330/oc4j_extended_101330.zip 3.1 Extract to C:\oracle\product\10.2.0\db_1\oc4j_extended_101330 STEP-BY-STEP PROCEDURES ================================================================= 1. Prepare the database 1.1 Connect to SYS account (or any account that can connect as SYSDBA) 1.2 alter system set shared_pool_size=96M scope=both 1.3 alter system set java_pool_size=80M scope=both 1.4 ALTER USER SCOTT ACCOUNT UNLOCK; 1.5 ALTER USER SCOTT IDENTIFIED BY "tiger"; 1.6 ALTER USER SCOTT QUOTA UNLIMITED ON USERS; 1.7 GRANT PUBLIC TO SCOTT; 1.8 GRANT Create Public Synonym TO SCOTT; 2. Load Oracle SOAP client to SCOTT (for Oracle 10g Release 2 Database) 2.1 Commands 2.1.1 Open command prompt (cmd.exe) 2.1.2 loadjava -u scott/tiger -r -v -f -genmissing -s -grant public C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\webservices\lib\soap.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\lib\dms.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\jlib\javax-ssl-1_1.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\lib\servlet.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\lib\mail.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\lib\activation.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\lib\http_client.jar C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\lib\ejb.jar 2.1.3 It will take around 3 minutes and you will see "something" like the following when it completes (count may not be exact) : Classes Loaded: 909 Resources Loaded: 75 Sources Loaded: 0 Published Interfaces: 0 Classes generated: 0 Classes skipped: 0 Synonyms Created: 984 Errors: 0 2.1.3 Close this command prompt 3. Load Oracle JAX-RPC client to SYS (for Oracle 10g Release 2 Database) 3.1 Commands 3.1.1 Open command prompt (cmd.exe) 3.1.2 loadjava -u sys/oracle -r -v -f -genmissing -s -grant public C:\oracle\product\10.2.0\db_1\dbws_callout_utility_10131\sqlj\lib\dbwsclientws.jar C:\oracle\product\10.2.0\db_1\dbws_callout_utility_10131\sqlj\lib\dbwsclientdb102.jar 3.1.2.1 It will take around 15 minutes and you will see "something" like following when it completes (count may not be exact) : Classes Loaded: 4027 Resources Loaded: 81 Sources Loaded: 0 Published Interfaces: 0 Classes generated: 61 Classes skipped: 0 Synonyms Created: 4108 Errors: 0 3.1.3 Close this command prompt 4. Execute UTL_DBWS packages to SYS (for Oracle 10g Release 2 Database) 4.1 Commands 4.1.1 Connect to SYS account only 4.1.2 Execute the following (in TOAD you should execute them as Script, press the "thunder" button) 4.2.1 @"C:\oracle\product\10.2.0\db_1\dbws_callout_utility_10131\sqlj\lib\utl_dbws_decl.sql" 4.2.2 @"C:\oracle\product\10.2.0\db_1\dbws_callout_utility_10131\sqlj\lib\utl_dbws_body.sql" 4.2.3 CREATE PUBLIC SYNONYM utl_dbws FOR sys.utl_dbws; 5. Call-out a free Web Service using SCOTT 5.1 Commands 5.1.1 Connect to user SCOTT with password of TIGER 5.1.2 Copy and Paste the following "Anonymous Block" (sample #1) and execute it : 5.1.2.1 It sould return "PL/SQL DII client return ===> Redwood City" declare service_ utl_dbws.SERVICE; call_ utl_dbws.CALL; service_qname utl_dbws.QNAME; port_qname utl_dbws.QNAME; operation_qname utl_dbws.QNAME; string_type_qname utl_dbws.QNAME; retx ANYDATA; retx_string VARCHAR2(1000); retx_double number; retx_len number; params utl_dbws.ANYDATA_LIST; l_input_params utl_dbws.anydata_list; l_result ANYDATA; l_namespace VARCHAR2(1000); begin -- open internet explorer and navigate to http://webservices.imacination.com/distance/Distance.jws?wsdl -- search for 'targetNamespace' in the wsdl l_namespace := 'http://webservices.imacination.com/distance/Distance.jws'; -- search for 'service name' in the wsdl service_qname := utl_dbws.to_qname(l_namespace, 'DistanceService'); -- this is just the actual wsdl url service_ := utl_dbws.create_service(HTTPURITYPE('http://webservices.imacination.com/distance/Distance.jws?wsdl'), service_qname); -- search for 'portType name' in the wsdl port_qname := utl_dbws.to_qname(l_namespace, 'Distance'); -- search for 'operation name' in the wsdl -- there will be a lot, we will choose 'getCity' operation_qname := utl_dbws.to_qname(l_namespace, 'getCity'); -- bind things together call_ := utl_dbws.create_call(service_, port_qname, operation_qname); -- default is 'FALSE', so we make it 'TRUE' utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE'); -- search for 'operation soapAction' under -- it is blank, so we make it '' utl_dbws.set_property(call_, 'SOAPACTION_URI', ''); -- search for 'encodingstyle' under utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/'); -- search for 'binding style' utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc'); -- search for 'xmlns:xs' to know the value of the first parameter -- under you will see the line -- thus the return type is 'string", removing 'xsd:' string_type_qname := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string'); -- in the line -- the parameterOrder is 'zip', thus we put in 'zip' -- the 'ParameterMode.IN' is used to specify that we will be passing an "In Parameter" to the web service -- the 'ParameterMode.IN' is a constant variable in the sys.utl_dbws package utl_dbws.add_parameter(call_, 'zip', string_type_qname, 'ParameterMode.IN'); utl_dbws.set_return_type(call_, string_type_qname); -- supply the In Parameter for the web service params(0) := ANYDATA.convertvarchar('94065'); -- invoke the web service retx := utl_dbws.invoke(call_, params); -- access the returned value and output it to the screen retx_string := retx.accessvarchar2; dbms_output.put_line('PL/SQL DII client return ===> ' || retx_string); -- release the web service call utl_dbws.release_service(service_); end; / 5.1.3 Copy and Paste the following "Anonymous Block" (sample #2) and execute it : 5.1.3.1 It should return "PL/SQL DII client return ===> twelve thousand three hundred and forty five" declare service_ utl_dbws.SERVICE; call_ utl_dbws.CALL; service_qname utl_dbws.QNAME; port_qname utl_dbws.QNAME; operation_qname utl_dbws.QNAME; string_type_qname utl_dbws.QNAME; retx ANYDATA; retx_string VARCHAR2(1000); retx_double number; retx_len number; params utl_dbws.ANYDATA_LIST; l_input_params utl_dbws.anydata_list; l_result ANYDATA; l_namespace VARCHAR2(1000); begin -- open internet explorer and navigate to http://www.dataaccess.com/webservicesserver/numberconversion.wso?WSDL -- search for 'targetNamespace' in the wsdl l_namespace := 'http://www.dataaccess.com/webservicesserver/'; -- search for 'service name' in the wsdl service_qname := utl_dbws.to_qname(l_namespace, 'NumberConversion'); -- this is just the actual wsdl url service_ := utl_dbws.create_service(HTTPURITYPE('http://www.dataaccess.com/webservicesserver/numberconversion.wso?WSDL'), service_qname); -- search for 'portType name' in the wsdl port_qname := utl_dbws.to_qname(l_namespace, 'NumberConversionSoap'); -- search for 'operation name' in the wsdl -- there will be a lot, we will choose 'NumberToWords' operation_qname := utl_dbws.to_qname(l_namespace, 'NumberToWords'); -- bind things together call_ := utl_dbws.create_call(service_, port_qname, operation_qname); -- default is 'FALSE', so we make it 'TRUE' utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE'); -- search for 'operation soapAction' under -- it is blank, so we make it '' utl_dbws.set_property(call_, 'SOAPACTION_URI', ''); -- search for 'encodingstyle' -- cannot find xml tag 'encodingstyle', so we just use the following as the generic encoding style utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/'); -- search for 'binding style' -- although 'document' was used, it produced an error, thus we will use 'rpc' -- this value is generally only 'document' or 'rpc' utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc'); -- search for 'xmlns:xs' to know the value of the first parameter -- under you will see the line -- thus the return type is 'unsignedlong', removing 'xs:' -- however, upon testing, using 'unsignedlong' produced an error, while 'string' did not, so we will use 'string' string_type_qname := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string'); -- under you will see the line -- the element name is 'ubiNum', thus we put in 'ubiNum' -- the 'ParameterMode.IN' is used to specify that we will be passing an "In Parameter" to the web service -- the 'ParameterMode.IN' is a constant variable in the utl_dbws package utl_dbws.add_parameter(call_, 'ubiNum', string_type_qname, 'ParameterMode.IN'); utl_dbws.set_return_type(call_, string_type_qname); -- supply the In Parameter for the web service params(0) := ANYDATA.convertvarchar('12345'); -- invoke the web service retx := utl_dbws.invoke(call_, params); -- access the returned value and output it to the screen retx_string := retx.accessvarchar2; dbms_output.put_line('PL/SQL DII client return ===> ' || retx_string); -- release the web service call utl_dbws.release_service(service_); end; / NOTES AND REFERENCES ================================================================= This detailed step-by-step guide was produced with the following guide http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm I could still not load Oracle JAX-RPC client to SCOTT (step 4) as it produces an error. See the following for more details : http://forums.oracle.com/forums/thread.jspa?threadID=633219&tstart=0 Also, I could not consume some of the other free Web Services out there for reasons I don't know, hope someone can help clarify. See the following for more details : http://forums.oracle.com/forums/thread.jspa?threadID=630733&tstart=0