3 Replies Latest reply: Jul 17, 2008 5:48 AM by 650386 RSS

    Oracle 10gR2 WebService Call-In & Call-Out via PL/SQL (Step-by-Step Guide)

    605614
      Hi Everyone,
      This guide is for those of you who tried to Call-In and Call-Out WebService via Oracle 10gR2 using PL/SQL but failed a couple of times, re-installed a couple of times and retry, got it to work but don't know what made it work scenarios.

      Hope this helps ;-)

      Thanks,
      Henry Wu



      Instructions on how to use Oracle10gR2 Webservice Call-Out feature (Oracle JAX-RPC DII - Dynamic Invocation (DII) APIs)

      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 <wsdl:operation name="getCity">
      -- it is blank, so we make it ''
      utl_dbws.set_property(call_, 'SOAPACTION_URI', '');

      -- search for 'encodingstyle' under <wsdl:operation name="getCity">
      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 <wsdl:message name="getCityResponse"> you will see the line <wsdl:part name="getCityReturn" type="xsd:string" />
      -- 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 <wsdl:operation name="getCity" parameterOrder="zip">
      -- 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 <operation name="NumberToWords">
      -- 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 <xs:element name="NumberToWords"> you will see the line <xs:element name="ubiNum" type="xs:unsignedLong" />
      -- 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 <xs:element name="NumberToWords"> you will see the line <xs:element name="ubiNum" type="xs:unsignedLong" />
      -- 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

      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      Instructions on how to use Oracle10gR2 Webservice Call-In feature

      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 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

      2.1 Extract to C:\oracle\product\10.2.0\db_1\oc4j_extended_101330

      STEP-BY-STEP PROCEDURES
      =================================================================
      1. Connect to SYS account and create user STORE
      CREATE USER store IDENTIFIED BY store;
      GRANT connect, resource TO store;

      2. Connect to Oracle using the user STORE and password STORE
      CONNECT store/store;

      3. Execute the following objects for this excercise
      CREATE SEQUENCE order_sq;

      CREATE TABLE product_types (
      product_type_id INTEGER
      CONSTRAINT product_types_pk PRIMARY KEY,
      name VARCHAR2(10) NOT NULL
      );

      CREATE TABLE products (
      product_id INTEGER
      CONSTRAINT products_pk PRIMARY KEY,
      product_type_id INTEGER
      CONSTRAINT products_fk_product_types
      REFERENCES product_types(product_type_id),
      name VARCHAR2(30) NOT NULL,
      description VARCHAR2(50),
      price NUMBER(5, 2)
      );

      CREATE TABLE customers (
      customer_id INTEGER
      CONSTRAINT customers_pk PRIMARY KEY,
      first_name VARCHAR2(10) NOT NULL,
      last_name VARCHAR2(10) NOT NULL,
      dob DATE,
      phone VARCHAR2(12)
      );

      CREATE TABLE orders (
      order_id INTEGER
      CONSTRAINT orders_pk PRIMARY KEY,
      product_id INTEGER
      CONSTRAINT purchases_fk_products
      REFERENCES products(product_id),
      customer_id INTEGER
      CONSTRAINT purchases_fk_customers
      REFERENCES customers(customer_id),
      quantity INTEGER NOT NULL
      );

      INSERT INTO customers (
      customer_id, first_name, last_name, dob, phone
      ) VALUES (
      1, 'John', 'Brown', '01-JAN-1965', '800-555-1211'
      );

      INSERT INTO product_types (
      product_type_id, name
      ) VALUES (
      1, 'Book'
      );

      INSERT INTO products (
      product_id, product_type_id, name, description, price
      ) VALUES (
      1, 1, 'Modern Science', 'A description of modern science', 19.95
      );

      COMMIT;

      CREATE OR REPLACE PACKAGE dbfunc AS
      FUNCTION place_order (
      p_product_id INTEGER,
      p_customer_id INTEGER,
      p_quantity INTEGER
      )
      RETURN VARCHAR2;
      END dbfunc;
      /

      CREATE OR REPLACE PACKAGE BODY dbfunc AS
      FUNCTION place_order (
      p_product_id INTEGER,
      p_customer_id INTEGER,
      p_quantity INTEGER
      )
      RETURN VARCHAR2 IS
      v_customer_count INTEGER;
      v_product_count INTEGER;
      v_order_id INTEGER;
      BEGIN
      -- count the number of products with the
      -- supplied p_product_id (should be 1 if the product exists)
      SELECT COUNT(*)
      INTO v_product_count
      FROM products
      WHERE product_id = p_product_id;

      IF v_product_count = 0 THEN
      RETURN 'No such product';
      END IF;

      -- count the number of customers with the
      -- supplied p_customer_id (should be 1)
      SELECT COUNT(*)
      INTO v_customer_count
      FROM customers
      WHERE customer_id = p_customer_id;

      IF v_customer_count = 0 THEN
      RETURN 'No such customer';
      END IF;

      -- get the next value from orders_sq
      SELECT order_sq.nextval
      INTO v_order_id
      FROM dual;

      -- place the order
      INSERT INTO orders (
      order_id, product_id, customer_id, quantity
      ) VALUES (
      v_order_id, p_product_id, p_customer_id, p_quantity
      );
      COMMIT;
      RETURN 'Order placed with id of ' || v_order_id;

      EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      RETURN 'Order not placed';
      END place_order;
      END dbfunc;

      4. Install and Configure OC4J

      4.1 Change Directory
      4.1.1 Open command prompt (cmd.exe)
      4.1.2 CD C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\bin

      4.2 Configure Environment variables (type in command prompt)
      4.2.1 set JAVA_HOME=C:\oracle\product\10.2.0\db_1\jdk
      4.2.2 set ORACLE_HOME=C:\oracle\product\10.2.0\db_1\oc4j_extended_101330

      4.3 Starting OC4J for the first time
      4.3.1 oc4j -start
      4.3.2 If this is not your first time to "start" oc4j, go to step 4.3.4
      4.3.3 Set oc4jadmin password (use the password "oracle" w/o the quotation marks)
      08/03/21 17:13:54 Set OC4J administrator's password (password text will not be displayed as it is entered)
      Enter password:
      Confirm password:
      Confirm password: The password for OC4J administrator "oc4jadmin" has been set.
      08/03/21 17:14:01 The OC4J administrator "oc4jadmin" account is activated.
      4.3.4 OC4J has started if you see the message :
      08/03/21 17:14:16 Oracle Containers for J2EE 10g (10.1.3.3.0) initialized
      4.3.5 Leave this command prompt open

      4.4 Configure Data Source
      4.4.1 Open a new command prompt via the cmd.exe command
      4.4.2 type "hostname" and then press enter
      4.4.3 Keep this command prompt open for later use
      4.4.4 Open Windows Explorer and navigate to the folder C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\config
      4.4.5 Open the file data-sources.xml
      4.4.6 Modify and Save the file so that it will look something like the following :
      4.4.6.1 Note that "hpv2710us" is my hostname found in step 4.4.2
      4.4.6.2 Note that "ORCLDEV" is the name of my Oracle Instance. (Default installation uses ORCL only)
      <connection-pool name="Example Connection Pool">
      <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
      user="store"
      password="store"
      url="jdbc:oracle:thin:@//hpv2710us:1521/ORCLDEV">
      </connection-factory>
      </connection-pool>
      4.4.7 Close the text editor
      4.4.8 Close the Windows Explorer
      4.4.9 Close this command prompt (type exit)

      5. Publishing a PL/SQL Package as a Database Web Service

      5.1 Create the config.xml file
      5.1.1 Open Notepad.exe
      5.1.2 Copy and Paste the following :
      5.1.2.1 Note that "hpv2710us" is my hostname found in step 4.4.2
      5.1.2.2 Note that "ORCLDEV" is the name of my Oracle Instance. (Default installation uses ORCL only)
      <web-service>
      <display-name>PLSQL Web Service</display-name>
      <description>PLSQL Sample</description>
      <destination-path>./dbfunc.ear</destination-path>
      <temporary-directory>/tmp</temporary-directory>
      <context>/plsqlsample</context>
      <stateless-stored-procedure-java-service>
      <jar-generation>
      <schema>store/store</schema>
      <db-url>jdbc:oracle:thin:@hpv2710us:1521:ORCLDEV</db-url>
      <prefix>db.func.place.order</prefix>
      <db-pkg-name>dbfunc</db-pkg-name>
      </jar-generation>
      <uri>/dbfunc</uri>
      <database-JNDI-name>jdbc/OracleDS</database-JNDI-name>
      </stateless-stored-procedure-java-service>
      <wsdl-gen>
      <wsdl-dir>wsdl</wsdl-dir>
      <option name="force">true</option>
      <option name="httpServerURL">http://localhost:8888</option>
      </wsdl-gen>
      <proxy-gen>
      <proxy-dir>proxy</proxy-dir>
      <option name="include-source">true</option>
      </proxy-gen>
      </web-service>
      5.1.3 Save file as config.xml in the directory C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\config
      5.1.3.1 Note it should be config.xml and not config.txt
      5.1.4 Close the text editor

      5.2 Publish dbfunc Oracle Package as a Web Service
      5.2.1 Open a new command prompt via the cmd.exe command
      5.2.2 CD C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home
      5.2.3 mkdir ear
      5.2.4 CD ear
      5.2.5 set ORACLE_HOME=C:\oracle\product\10.2.0\db_1\oc4j_extended_101330
      5.2.6 set CLASSPATH=.;%ORACLE_HOME%\webservices\lib\wsdl.jar;%ORACLE_HOME%\lib\xmlparserv2.jar;%ORACLE_HOME%\soap\lib\soap.jar
      5.2.7 java -jar %ORACLE_HOME%\webservices\lib\WebServicesAssembler.jar -config C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\config\config.xml
      Please wait ...
      STORE.DBFUNC
      5.2.8 java -jar %ORACLE_HOME%\j2ee\home\admin_client.jar deployer:oc4j:localhost:23791 oc4jadmin oracle -deploy -file C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home\ear\dbfunc.ear -deploymentName dbfunc
      08/03/21 17:46:23 Notification ==>Application Deployer for dbfunc COMPLETES. Operation time: 296 msecs
      5.2.9 java -jar %ORACLE_HOME%\j2ee\home\admin_client.jar deployer:oc4j:localhost:23791 oc4jadmin oracle -bindWebApp -appname dbfunc -webModuleName dbfunc_web
      5.2.10 java -jar %ORACLE_HOME%\j2ee\home\admin_client.jar deployer:oc4j:localhost:23791 oc4jadmin oracle -bindAllWebApps -appname dbfunc
      5.2.11 Close this command prompt (type exit)

      6. Test dbfunc Web Service

      6.1 Restart OC4J
      6.1.1 Open the command prompt you used to start OC4J in step 4.1.1/4.3.1
      6.1.2 In the keyboard, hit Ctrl+C
      6.1.2.1 Type "Y" and then hit Enter
      08/03/21 18:02:51 Shutting down OC4J...
      Terminate batch job (Y/N)? Y
      6.1.2.2 Note that the command prompt should still be open
      6.1.2.2.1 If you accidentally closed the command prompt just do the following
      6.1.2.2.1.1 Do 4.1.1 to 4.2.2 and then proceed to 6.1.3
      6.1.3 oc4j -start
      Starting OC4J from C:\oracle\product\10.2.0\db_1\oc4j_extended_101330\j2ee\home...
      08/03/21 18:07:57 Oracle Containers for J2EE 10g (10.1.3.3.0) initialized
      6.1.4 Leave this command prompt open

      6.2 Use the Web Service to "Order an Item"
      6.2.1 Connect to Oracle using the user STORE and password STORE
      6.2.2 select * from orders
      6.2.2.1 Zero row should return
      6.2.3 Open Internet Explorer
      6.2.4 Navigate to http://localhost:8888/plsqlsample/dbfunc
      6.2.5 Input the following :
      6.2.5.1 param0 value: 1
      6.2.5.2 param1 value: 1
      6.2.5.3 param2 value: 10
      6.2.6 Click the Invoke button
      6.2.7 A pop-up window will appear having the following contents :
      <?xml version="1.0" encoding="UTF-8" ?>
      - <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      - <SOAP-ENV:Body>
      - <ns1:placeOrderResponse xmlns:ns1="http://db.func.place.order/dbfunc.wsdl" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
      <return xsi:type="xsd:string">Order placed with id of 1</return>
      </ns1:placeOrderResponse>
      </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>
      6.2.8 Go back to SQLPLUS or Toad and execute "select * from orders" again
      6.2.8.1 There should be one row
      ORDER_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY
      1, 1, 1, 10

      NOTES AND REFERENCES
      =================================================================
      This detailed step-by-step guide was produced with the following article by Jason Price http://www.oracle.com/technology/pub/articles/price_10gws.html
        • 1. Re: Oracle 10gR2 WebService Call-In & Call-Out via PL/SQL (Step-by-Step Guide)
          645871
          Wow it finally works!

          Strange, not much has changed between your setup and my own. Only thing I did differently was put the utl_dbws into sys.
          For the load java operations, you had two options that I did not: -s -grant public

          This was also different. I initially ran these scripts as a user other than SYS:
          @"C:\oracle\product\10.2.0\db_1\sqlj\lib\utl_dbws_decl.sql";
          @"C:\oracle\product\10.2.0\db_1\sqlj\lib\utl_dbws_body.sql";
          CREATE PUBLIC SYNONYM utl_dbws FOR sys.utl_dbws;


          Thanks!
          • 2. Re: Oracle 10gR2 WebService Call-In & Call-Out via PL/SQL (Step-by-Step Gui
            650386
            Hi!
            I have problem with utl_dbws and I hope you will be able to help me. When i load Oracle JAX-RPC client and utl_dbws into SYS everything is ok. But when I load it into another schema and try to run it I get error:

            ORA-29532: Java call terminated by uncaught Java exception: java.lang.IllegalAccessException: java.lang.NoClassDefFoundError ORA-06512: at "WS_TEST.UTL_DBWS", line 178.

            WS_TEST it is my "another user" of course. In line 178, as you can check, is create_service function, but I really don't know what might be wrong. Class oracle.jpub.runtime.dbws.DbwsProxy.createService, which is used in create_service, is loaded into the database and it has valid status.

            Can you help me to resolve it?
            voytec001
            • 3. Re: Oracle 10gR2 WebService Call-In & Call-Out via PL/SQL (Step-by-Step Gui
              650386
              Success!!!
              I had to grant this permissions:

              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar', '' );
              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.lang.RuntimePermission', 'setFactory', '' )
              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write' );
              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.net.SocketPermission', 'localhost', 'resolve' );
              call dbms_java.grant_permission( 'WS_TEST', 'SYS:java.net.SocketPermission', '127.0.0.1:8084', 'connect,resolve' );

              As I wrote earlier, WS_TEST is my user into which I load JAX-RPC client. Note that, if you load JAX-RPC client into user another than SYS, you have NOT to use -s -grant public option (wassam wrote about this too). Another thing is that I (finally) didn't load OC4J. To consume WebService I use utl_dbws.invoke(call_Handle CALL, request SYS.XMLTYPE) function.

              voytec001