Re: Using UTL_DBWS to access a web service

On Feb 6, 1:37 pm, jason.engl..._at_paetec.com wrote:
> 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/soap-
> encoding"><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

Remove the exception handler so you can see what exception is being raised.

That's kind of a dangerous exception handler to have anyway. Received on Wed Feb 07 2007 - 03:10:52 CST