Consume a webservice and use in Oracle Form Developer 6i [message #537026] |
Mon, 26 December 2011 04:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mehediu
Messages: 46 Registered: February 2010 Location: Dhaka
|
Member |
![mehediu%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Dear Friends,
I write a Function in PL/SQL to consume a webservice and its working fine when i run in TOAD or SQL terminal.
But when i try it from oracle form developer 6i , it gives me
the following error :
ORA-00600: internal error code, arguments: [26599], [1], [211], [], [], [], [], []
can anyone help me please
Thanks in advance
|
|
|
|
|
|
|
|
|
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537270 is a reply to message #537026] |
Tue, 27 December 2011 08:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mehediu
Messages: 46 Registered: February 2010 Location: Dhaka
|
Member |
![mehediu%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Versions
===============
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
Toad for Oracle 9.7.2
Forms [32 Bit] Version 6.0.8.26.0 (Production)
CREATE OR REPLACE function f_callWebService (
as_serviceUrl in varchar2,
as_targetNamespace in varchar2,
as_serviceName in varchar2,
as_portName in varchar2,
as_wsMethodName in varchar2,
as_parmName1 in varchar2, -- TODO: Update to pass an array of name/value pairs.
as_parmValue1 in varchar2,
---
as_parmName2 in varchar2, -- TODO: Update to pass an array of name/value pairs.
as_parmValue2 in varchar2,
---
as_parmName3 in varchar2, -- TODO: Update to pass an array of name/value pairs.
as_parmValue3 in varchar2
)
return varchar2
is
cs_soapActionUseKey constant varchar2(14) := 'SOAPACTION_USE';
cs_soapActionUseVal constant varchar2(4) := 'TRUE';
cs_soapActionUriKey constant varchar2(14) := 'SOAPACTION_URI';
ls_targetNamespace varchar2(500);
ls_serviceQname sys.utl_dbws.qname;
ls_portQname sys.utl_dbws.qname;
ls_operationQname sys.utl_dbws.qname;
ln_service sys.utl_dbws.service;
ln_call sys.utl_dbws.call;
lx_serviceRequest sys.xmltype;
lx_serviceResponse sys.xmltype;
ls_resultXpath varchar2(100);
begin
--
if as_serviceUrl is null or trim(as_serviceUrl) is null then
raise_application_error( -20001,
'Argument not passed exception. Argument: ''as_serviceUrl''');
end if;
--TODO: Validate other parameters.
--
-- Append url separator to end if one does not exist.
ls_targetNamespace := as_targetNamespace;
if substr(ls_targetNamespace, length(ls_targetNamespace), 1) != '/' then
ls_targetNamespace := ls_targetNamespace || '/';
end if;
--
-- Initialize the service components.
--
ls_serviceQname := sys.utl_dbws.to_qname(
name_Space => ls_targetNamespace,
name => as_serviceName);
--
ls_portQname := sys.utl_dbws.to_qname(
name_Space => ls_targetNamespace,
name => as_portName);
--
ls_operationQname := sys.utl_dbws.to_qname(
name_Space => ls_targetNamespace,
name => as_wsMethodName);
--
-- Create the service and call objects.
ln_service := sys.utl_dbws.create_service (
service_name => ls_serviceQname);
--
ln_call := sys.utl_dbws.create_call (
service_handle => ln_service,
port_name => ls_portQname,
operation_name => ls_operationQname);
--
-- Set values on the call object.
sys.utl_dbws.set_target_endpoint_address(
call_Handle => ln_call,
endpoint => as_serviceUrl);
--
sys.utl_dbws.set_property(
call_Handle => ln_call,
key => cs_soapActionUseKey,
value => cs_soapActionUseVal);
--
sys.utl_dbws.set_property(
call_Handle => ln_call,
key => cs_soapActionUriKey,
value => ls_targetNamespace
|| as_wsMethodName);
--
--
-- Create service request xml.
--
lx_serviceRequest := sys.xmltype(
'<' || as_wsMethodName || ' xmlns="' || ls_targetNamespace || '">'
|| '<'||as_parmName1||'>'|| as_parmValue1 ||'</'||as_parmName1||'>'
|| '<'||as_parmName2||'>'|| as_parmValue2 ||'</'||as_parmName2||'>'
|| '<'||as_parmName3||'>'|| as_parmValue3 ||'</'||as_parmName3||'>'
|| '</' || as_wsMethodName || '>');
--dbms_output.put_line(lx_serviceRequest.getclobval());
--
-- Call the service.
--
BEGIN
lx_serviceResponse :=
sys.utl_dbws.invoke(ln_call, lx_serviceRequest);
EXCEPTION
WHEN OTHERS THEN
return sqlerrm;
END;
sys.utl_dbws.release_call (call_handle => ln_call);
sys.utl_dbws.release_service (service_handle => ln_service);
--return lx_serviceResponse.getstringval();
return extractResult(lx_serviceResponse, ls_targetNamespace, as_wsMethodName,'VARCHAR2');
exception
when others then
--raise_application_error(-20111,sqlerrm);
return sqlerrm;
end f_callWebService;
To call the procedure i use :
select f_callWebService(
'http://192.168.1.141/webservice/Service1.asmx?WSDL',
'http://tempuri.org/',
'Service1',
'',
'GetAmount',
'p_brcod',
'3555',
--
'p_actcod',
'101',
--
'p_acno',
'123240'
) from dual;
it works in Toad but not working in sqlplusw and forms.
Thank you friends...
|
|
|
|
|
|
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537317 is a reply to message #537270] |
Tue, 27 December 2011 13:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
mehediu wrote on Tue, 27 December 2011 09:07Versions
===============
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
Toad for Oracle 9.7.2
Forms [32 Bit] Version 6.0.8.26.0 (Production)
But what version client are you using for sqlplus?
Does Forms 6i even know anything about sys.xmltype?
If I recall, Forms 9i was the first one where its engine matched that of the Database.
|
|
|
|
|
|
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537343 is a reply to message #537328] |
Tue, 27 December 2011 15:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
mehediu wrote on Tue, 27 December 2011 14:17Any suggestions or any idea if you think should work please let me know.
Thank you.....
You can't seem to follow questions when they are right in front of you, so I will make it a puzzle and maybe that will help.
Go to message 537317 and read the line that starts with the word "But."
CUT AND PASTE!
|
|
|
|