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 -> Re: Using UTL_DBWS to access a web service

Re: Using UTL_DBWS to access a web service

From: J Englert <jason.englert_at_paetec.com>
Date: 7 Feb 2007 10:54:46 -0800
Message-ID: <1170874486.806934.158180@s48g2000cws.googlegroups.com>


On Feb 7, 11:27 am, DA Morgan <damor..._at_psoug.org> wrote:
> J Englert wrote:
> > 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
>
> EXCEPTION
> > > WHEN OTHERS THEN
> > > RETURN NULL;
>
> This is a bad ideas about 99% of the time. Your case is one of them.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

I can't believe I missed that. I guess I was just so concerned with the actual UTL_DBWS calls that I couldn't see the forest for the trees.

Thanks for the help,
Jason Received on Wed Feb 07 2007 - 12:54:46 CST

Original text of this message

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