Home » SQL & PL/SQL » SQL & PL/SQL » UTL_DBWS (Oracle 10gR2 WebService) (Oracle 10gR2, Windows XP SP2)
UTL_DBWS (Oracle 10gR2 WebService) [message #308784] Tue, 25 March 2008 08:01 Go to next message
henrycortezwu
Messages: 2
Registered: March 2008
Junior Member
Hi,

I'm trying to consume a web service, but I'm receiving an error message which I can't seem to decipher where the problem lies.

The code I'm using is the following.

declare
service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
operation_qname sys.utl_dbws.QNAME;
string_type_qname sys.utl_dbws.QNAME;
retx ANYDATA;
retx_string VARCHAR2(100);
retx_len number;
params sys.utl_dbws.ANYDATA_LIST;
begin
service_qname := sys.utl_dbws.to_qname('http://www.webserviceX.NET', 'periodictable');
service_ := sys.utl_dbws.create_service(HTTPURITYPE('http://www.webservicex.net/periodictable.asmx?WSDL'), service_qname);
port_qname := sys.utl_dbws.to_qname('http://www.webserviceX.NET', 'periodictableSoap');
operation_qname := sys.utl_dbws.to_qname('http://www.webserviceX.NET', 'GetElementSymbol');
call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
sys.utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE');
sys.utl_dbws.set_property(call_, 'SOAPACTION_URI', 'http://www.webserviceX.NET/GetElementSymbol');
sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
sys.utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc');
string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
sys.utl_dbws.add_parameter(call_, 'ElementName', string_type_qname, 'ParameterMode.IN');
sys.utl_dbws.set_return_type(call_, string_type_qname);
params(0) := ANYDATA.convertvarchar('copper');
retx := sys.utl_dbws.invoke(call_, params);
retx_string := retx.accessvarchar2;
dbms_output.put_line('PL/SQL DII client return ' || retx_string);
sys.utl_dbws.release_service(service_);
end;
/

Error is:
ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Procedure or function 'GetElementSymbol' expects parameter '@ElementName', which was not supplied.
at WebServicex.periodictable.GetElementSymbol(String ElementName)
--- End of inner exception stack trace ---
ORA-06512: at "SYS.UTL_DBWS", line 388
ORA-06512: at "SYS.UTL_DBWS", line


Note that my installation or setup is OKAY as the code below works. Is there any limitation I've hit in my code above??

Also, for the SECOND parameter of utl_dbws.add_parameter ,... can I just put there anything I want?? It doesnt seem to care what I define there.


This code below works
==============================

declare
service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
operation_qname sys.utl_dbws.QNAME;
string_type_qname sys.utl_dbws.QNAME;
retx ANYDATA;
retx_string VARCHAR2(100);
retx_double number;
retx_len number;
params sys.utl_dbws.ANYDATA_LIST;
l_input_params SYS.UTL_DBWS.anydata_list;
l_result ANYDATA;
begin
service_qname := sys.utl_dbws.to_qname('http://webservices.imacination.com/distance/Distance.jws', 'DistanceService');
service_ := sys.utl_dbws.create_service(HTTPURITYPE('http://webservices.imacination.com/distance/Distance.jws?wsdl'), service_qname);
port_qname := sys.utl_dbws.to_qname('http://webservices.imacination.com/distance/Distance.jws', 'Distance');
operation_qname := sys.utl_dbws.to_qname('http://webservices.imacination.com/distance/Distance.jws', 'getCity');
call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
sys.utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE');
sys.utl_dbws.set_property(call_, 'SOAPACTION_URI', '');
sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
sys.utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc');
string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
sys.utl_dbws.add_parameter(call_, 'zip', string_type_qname, 'ParameterMode.IN');
sys.utl_dbws.set_return_type(call_, string_type_qname);
params(0) := ANYDATA.convertvarchar('94065');
retx := sys.utl_dbws.invoke(call_, params);
retx_string := retx.accessvarchar2;
dbms_output.put_line('PL/SQL DII client return ' || retx_string);
sys.utl_dbws.release_service(service_);
end;
/

output should be:
PL/SQL DII client return Redwood City



This one works as well
==============================

declare
service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
operation_qname sys.utl_dbws.QNAME;
string_type_qname sys.utl_dbws.QNAME;
retx ANYDATA;
retx_string VARCHAR2(100);
retx_double number;
retx_len number;
params sys.utl_dbws.ANYDATA_LIST;
l_RET_QNAME_LIST sys.utl_dbws.QNAME_LIST;
l_input_params SYS.UTL_DBWS.anydata_list;
l_result ANYDATA;
l_namespace varchar2(1000);
begin
l_namespace := 'http://www.dataaccess.com/webservicesserver/';
service_qname := sys.utl_dbws.to_qname(l_namespace, 'NumberConversion');
service_ := sys.utl_dbws.create_service(HTTPURITYPE('http://www.dataaccess.com/webservicesserver/numberconversion.wso?WSDL'), service_qname);
port_qname := sys.utl_dbws.to_qname(l_namespace, 'NumberConversionSoap');
operation_qname := sys.utl_dbws.to_qname(l_namespace, 'NumberToWords');
call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
sys.utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE');
sys.utl_dbws.set_property(call_, 'SOAPACTION_URI', '');
sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
sys.utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc');
string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
sys.utl_dbws.add_parameter(call_, 'ubiNum', string_type_qname, 'ParameterMode.IN');
sys.utl_dbws.set_return_type(call_, string_type_qname);
params(0) := ANYDATA.convertvarchar('12345');
retx := sys.utl_dbws.invoke(call_, params);
retx_string := retx.accessvarchar2;
dbms_output.put_line('PL/SQL DII client return ' || retx_string);
sys.utl_dbws.release_service(service_);
end;
/

output should be:
PL/SQL DII client return twelve thousand three hundred and forty five


But this one does not work
==============================

declare
service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
operation_qname sys.utl_dbws.QNAME;
string_type_qname sys.utl_dbws.QNAME;
retx ANYDATA;
retx_string VARCHAR2(100);
retx_len number;
params sys.utl_dbws.ANYDATA_LIST;
begin
service_qname := sys.utl_dbws.to_qname('http://xmlme.com/WebServices', 'Shakespeare');
service_ := sys.utl_dbws.create_service(HTTPURITYPE('http://www.xmlme.com/WSShakespeare.asmx?WSDL'), service_qname);
port_qname := sys.utl_dbws.to_qname('http://xmlme.com/WebServices', 'ShakespeareSoap');
operation_qname := sys.utl_dbws.to_qname('http://xmlme.com/WebServices', 'GetSpeech');
call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
sys.utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE');
sys.utl_dbws.set_property(call_, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech');
sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
sys.utl_dbws.set_property(call_, 'OPERATION_STYLE', 'rpc');
string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
sys.utl_dbws.add_parameter(call_, 'Request', string_type_qname, 'ParameterMode.IN');
sys.utl_dbws.set_return_type(call_, string_type_qname);
params(0) := ANYDATA.convertvarchar('To be, or not to be');
retx := sys.utl_dbws.invoke(call_, params);
retx_string := retx.accessvarchar2;
dbms_output.put_line('PL/SQL DII client return ' || retx_string);
sys.utl_dbws.release_service(service_);
end;
/


Error is:
ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at WSShakespeare.Shakespeare.GetSpeech(String Request)
--- End of inner exception stack trace ---
ORA-06512: at "SYS.UTL_DBWS", line 388
ORA-06512: at "SYS.UTL_DBWS", line 385
ORA-06512: at line 26



So, I'm getting mix results, some works, some doesn't. Any clues on how to go about resolving this?

You may find the complete detailed step-by-step procedure I've performed in the attached file.

Thanks,
Henry Wu
Re: UTL_DBWS (Oracle 10gR2 WebService) [message #308946 is a reply to message #308784] Wed, 26 March 2008 01:00 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
please check the string size.

regards,
Re: UTL_DBWS (Oracle 10gR2 WebService) [message #309139 is a reply to message #308946] Wed, 26 March 2008 09:45 Go to previous messageGo to next message
henrycortezwu
Messages: 2
Registered: March 2008
Junior Member
I got it now.

So there are to types of utl_dbws.invoke, one is to pass a "parameter", and the other is to pass an XML. Apprently, those that didnt work for me, in my previous posts, wanted an XML input. So, I gave it one and it worked.

For those who wants needs an example of such scenario, please see the code below.


declare
  service_              sys.utl_dbws.SERVICE;
  call_                 sys.utl_dbws.CALL;
  service_qname         sys.utl_dbws.QNAME;
  port_qname            sys.utl_dbws.QNAME;
  operation_qname       sys.utl_dbws.QNAME;
  string_type_qname     sys.utl_dbws.QNAME;
  l_namespace           VARCHAR2(1000);
  retx                  sys.xmltype;
  xml_string            sys.xmltype;
  l_str long;
begin
  l_namespace := 'http://xmlme.com/WebServices';
  service_qname := sys.utl_dbws.to_qname(l_namespace, 'Shakespeare');
  service_ := sys.utl_dbws.create_service(HTTPURITYPE('http://www.xmlme.com/WSShakespeare.asmx?WSDL'), service_qname);
  port_qname :=  sys.utl_dbws.to_qname(l_namespace, 'ShakespeareSoap');
  operation_qname := sys.utl_dbws.to_qname(l_namespace, 'GetSpeech');
  call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
  sys.utl_dbws.set_property(call_, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property(call_, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech'); 
  sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
  sys.utl_dbws.set_property(call_, 'OPERATION_STYLE', 'document');
  string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  sys.utl_dbws.add_parameter(call_, 'Request', string_type_qname, 'ParameterMode.IN');
  sys.utl_dbws.set_return_type(call_, string_type_qname);
  
  -- navigate to http://www.xmlme.com/WSShakespeare.asmx?op=GetSpeech and see how I came up with this
  -- at first I included <soap:Envelope> stuff and <soap:Body> stuff, but it didn't work
  -- so after some trial and error, I came up with the following 
  xml_string := xmltype('<?xml version="1.0" encoding="utf-8"?>
    <GetSpeech xmlns="http://xmlme.com/WebServices">
      <Request>To be, or not to be</Request>
    </GetSpeech>');
  retx := sys.utl_dbws.invoke(call_Handle => call_
                             ,request     => xml_string);

  l_str := retx.extract('/*').getstringval();
  loop        
    exit when l_str is null;        
    dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) - 1));        
    l_str := substr (l_str, instr (l_str, chr(10)) + 1);     
  end loop;
  sys.utl_dbws.release_service(service_);
end;
/



It should return :
<GetSpeechResponse xmlns="http://xmlme.com/WebServices">
<GetSpeechResult>&lt;SPEECH&gt;&lt;PLAY&gt;HAMLET&lt;/PLAY&gt;&lt;SPEAKER&gt;HAMLET&lt;/SPEAKER&gt;To be, or not to be: that is the question: Whether &apos;tis nobler in the mind to suffer The slings and arrows of outrageous fortune, Or to take arms against a sea of troubles, And by opposing end them? To die: to sleep; No more; and by a sleep to say we end The heart-ache and the thousand natural shocks That flesh is heir to, &apos;tis a consummation Devoutly to be wish&apos;d. To die, to sleep; To sleep: perchance to dream: ay, there&apos;s the rub; For in that sleep of death what dreams may come When we have shuffled off this mortal coil, Must give us pause: there&apos;s the respect That makes calamity of so long life; For who would bear the whips and scorns of time, The oppressor&apos;s wrong, the proud man&apos;s contumely, The pangs of despised love, the law&apos;s delay, The insolence of office and the spurns That patient merit of the unworthy takes, When he himself might his quietus make With a bare bodkin? who would fardels bear, To grunt and sweat under a weary life, But that the dread of something after death, The undiscover&apos;d country from whose bourn No traveller returns, puzzles the will And makes us rather bear those ills we have Than fly to others that we know not of? Thus conscience does make cowards of us all; And thus the native hue of resolution Is sicklied o&apos;er with the pale cast of thought, And enterprises of great pith and moment With this regard their currents turn awry, And lose the name of action.--Soft you now! The fair Ophelia! Nymph, in thy orisons Be all my sins remember&apos;d.&lt;/SPEECH&gt;</GetSpeechResult>
</GetSpeechResponse>
Re: UTL_DBWS (Oracle 10gR2 WebService) [message #454857 is a reply to message #309139] Fri, 07 May 2010 15:15 Go to previous message
lezhou
Messages: 1
Registered: May 2010
Junior Member
Hi,

In your example, your input has the same name as operation.

In your wsdl file:
<wsdl:message name="GetSpeechSoapIn">
<wsdl:part name="parameters" element="tns:GetSpeech" />
</wsdl:message>

<wsdl:operation name="GetSpeech">

But if operation name is different from input name, how do you define utl_dbws.invoke ( )'s xml request parameter?

If I do: request : = XMLTYPE('<operation_name_defined_in_wsdl..... />); I get web service outpt in xml format with error message: element mismatch, expected: input_defined_in_wsdl@name_space, got operation_name_defined_in_wsdl@name_space.
If I do: request := XMLTYPE('<input_defined_in_wsdl..... />); I get pl/sql error: port does not contain operation: input_defined_in_wsdl.

Can you help?

thanks
Lei
Previous Topic: LIKE clause help
Next Topic: update table
Goto Forum:
  


Current Time: Fri Dec 27 02:50:36 CST 2024