Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Using UTL_DBWS to access a web service

Using UTL_DBWS to access a web service

From: <jason.englert_at_paetec.com>
Date: 6 Feb 2007 05:37:27 -0800
Message-ID: <1170769047.412597.95650@a75g2000cwd.googlegroups.com>


Hi,

I am attempting to use UTL_DBWS in order to access a web service:

CREATE OR REPLACE FUNCTION update_status   RETURN VARCHAR2
AS

  l_service  sys.UTL_DBWS.service;
  l_call     sys.UTL_DBWS.call;
  l_result   SYS.XMLTYPE;

  l_wsdl_url        VARCHAR2(1024);
  l_service_name    VARCHAR2(200);

  l_operation_name VARCHAR2(200);
  l_input_params sys.UTL_DBWS.anydata_list;   xml_string sys.xmltype;
BEGIN
  dbms_output.put_line('1');
  l_wsdl_url := 'http://fai007590.corp.paetec.com:8888/arsys/ WSDL/public/fai007590/AST_CDROMDrive';
  l_service_name := 'AST_CDROMDriveService';   l_operation_name := 'OpSet';

  dbms_output.put_line('2');
  xml_string := xmltype('<?xml version="1.0"?><soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soapencoding"> <soap:Header> <m:Trans xmlns:m="http://www.w3schools.com/ transaction/" soap:mustUnderstand="0">234</m:Trans> <AuthenticationInfo> <userName>admin</userName> <password>admin</ password> <authentication>ARSystem</authentication> <locale>en_US</ locale> <timeZone> </timeZone> </AuthenticationInfo> </soap:Header> <root> <qualification>''Asset_ID'' = ''CDROM1''</qualification> </ root> </soap:Envelope>');

  dbms_output.put_line('3');
  l_service := sys.UTL_DBWS.create_service (     wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),     service_name => l_service_name);   dbms_output.put_line('4');
  l_call := sys.UTL_DBWS.create_call (

    service_handle => l_service,
    port_name      => NULL,
    operation_name => l_operation_name);
  dbms_output.put_line('5');
  sys.UTL_DBWS.SET_PROPERTY(
    l_call,

'OPERATION_STYLE',
'DOCUMENT');

  sys.UTL_DBWS.SET_PROPERTY(
    l_call,

'SOAPACTION_USE',
'TRUE');

  dbms_output.put_line('6');
  l_result := sys.UTL_DBWS.invoke (
    call_handle => l_call,
    request => xml_string);

  dbms_output.put_line('7');

  if l_result is null then
    dbms_output.put_line('NULL');
  else
    dbms_output.put_line('NOT NULL');
  end if;

  sys.UTL_DBWS.release_call (call_handle => l_call);   dbms_output.put_line('8');
  sys.UTL_DBWS.release_service (service_handle => l_service);   dbms_output.put_line('9');

  RETURN SYS.XMLTYPE.GETSTRINGVAL(l_result); -- ANYDATA.AccessNumber(l_result);
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

You will notice that I have added a bunch of dbms_output.put_line statements for debugging. The problem is, the process stops running after outputting '6'. Obviously, the problem is with the utl_dbws.invoke line, but I do not know what is wrong with it. I am not getting any kind of error message, the process just stops. Does anyone see anything that is wrong with the code, or know of a way in which I can see the error message which is being returned by the web service?

Thanks for the help,
Jason Received on Tue Feb 06 2007 - 07:37:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US