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 |
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 #309139 is a reply to message #308946] |
Wed, 26 March 2008 09:45 |
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><SPEECH><PLAY>HAMLET</PLAY><SPEAKER>HAMLET</SPEAKER>To be, or not to be: that is the question: Whether '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, 'tis a consummation Devoutly to be wish'd. To die, to sleep; To sleep: perchance to dream: ay, there'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's the respect That makes calamity of so long life; For who would bear the whips and scorns of time, The oppressor's wrong, the proud man's contumely, The pangs of despised love, the law'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'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'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'd.</SPEECH></GetSpeechResult>
</GetSpeechResponse>
|
|
|
Re: UTL_DBWS (Oracle 10gR2 WebService) [message #454857 is a reply to message #309139] |
Fri, 07 May 2010 15:15 |
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
|
|
|
Goto Forum:
Current Time: Fri Dec 27 02:50:36 CST 2024
|