Re: Using UTL_DBWS to access a web service

On Feb 7, 4:10 am, "William Robertson" <williamr2..._at_googlemail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Thanks for the help, but I'm going to have to reveal just how little I know about all this UTL_DBWS stuff. Where do I have an exception handler in there? I guess I must have misunderstood the purpose of one line of code or another, because I didn't even realize I have an exception handler!

Thanks,
Jason Received on Wed Feb 07 2007 - 08:27:06 CST