Using UTL_DBWS to access a web service
in [Oracle Help]
|
Prev: JDBC execute statement with Brackets - NullPointerException
Next: Trouble with UTL_DBWS / SOAPACTION_URI
From: jason.englert on 6 Feb 2007 08:37 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
From: William Robertson on 7 Feb 2007 04:10 On Feb 6, 1:37 pm, jason.engl...(a)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.
From: J Englert on 7 Feb 2007 09:27 On Feb 7, 4:10 am, "William Robertson" <williamr2...(a)googlemail.com> wrote: > On Feb 6, 1:37 pm, jason.engl...(a)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
From: DA Morgan on 7 Feb 2007 11:27 J Englert wrote: > On Feb 7, 4:10 am, "William Robertson" <williamr2...(a)googlemail.com> > wrote: >> On Feb 6, 1:37 pm, jason.engl...(a)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 damorgan(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
From: J Englert on 7 Feb 2007 13:54
On Feb 7, 11:27 am, DA Morgan <damor...(a)psoug.org> wrote: > J Englert wrote: > > On Feb 7, 4:10 am, "William Robertson" <williamr2...(a)googlemail.com> > > wrote: > >> On Feb 6, 1:37 pm, jason.engl...(a)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...(a)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 |