Home » Developer & Programmer » Forms » Consume a webservice and use in Oracle Form Developer 6i (Oracle Form Developer 6i)
Consume a webservice and use in Oracle Form Developer 6i [message #537026] Mon, 26 December 2011 04:06 Go to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

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 Smile
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537032 is a reply to message #537026] Mon, 26 December 2011 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried looking that error up on My Oracle Support?
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537034 is a reply to message #537026] Mon, 26 December 2011 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

Regards
Michel
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537035 is a reply to message #537034] Mon, 26 December 2011 04:48 Go to previous messageGo to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

Thanks Mr. Michel and cookiemonster,

i read Troubleshooting Internal Errors (TIE) and mail for oracle support.

i dont get anything in TIE for arguments: [26599].

if you get any solution please let me know through your quick response.

again thanks for yours quick response.
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537098 is a reply to message #537035] Mon, 26 December 2011 13:00 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Move code you wrote into a stored procedure. Then call that procedure from a form.

Any improvement?
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537168 is a reply to message #537026] Tue, 27 December 2011 01:30 Go to previous messageGo to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

Sorry for lately answer.

Actually, i make the function 1st and this function returns value when i use it in TOAD but when i use it in SQLPLUSW or form it gives the error as i describe before.

If you want then i can give the function details. i got it from net and i use it.

Thanks to all.
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537249 is a reply to message #537168] Tue, 27 December 2011 06:54 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Post the code, if you wish. Don't forget to specify Oracle database version, TOAD version, Forms version, SQL*Plus client version (if different than a database version).
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 messageGo to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

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 #537274 is a reply to message #537270] Tue, 27 December 2011 08:18 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it work in SQL*Plus? A command line tool (accessible from Windows' command prompt)?

Saying that it doesn't work in Forms: did you create that function in your form, or did you create it in a database and only called it from a form?
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537280 is a reply to message #537274] Tue, 27 December 2011 08:27 Go to previous messageGo to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

no it does not work in SQL*Plus also it only runs in TOAD.

i create back end function and trying to call it from everywhere. actually i need to call it from Form when i check that is it run from SQL*Plus i see that it does not.

i was thinking that if i create function in back end then i can call it from everywhere, but it not working.

Re: Consume a webservice and use in Oracle Form Developer 6i [message #537283 is a reply to message #537280] Tue, 27 December 2011 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since TOAD and sqlplus will just issue that select to the DB for it to process I can only see one explanation for why it works in one and not the other. The tools are connecting to different DBs or as different users.
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 messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
mehediu wrote on Tue, 27 December 2011 09:07
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)



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 #537320 is a reply to message #537317] Tue, 27 December 2011 13:03 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, but if it is a stored procedure (it is, as far as I understood), then form simply calls that procedure which works OK in the database. That *should* work (at least, I hope so).
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537323 is a reply to message #537320] Tue, 27 December 2011 13:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Oh, I completely ignored that OP created a Function in the DB. I thought it was a program unit in the Form itself.
An old or buggy client could also give errors even if it is in a Function.
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537328 is a reply to message #537026] Tue, 27 December 2011 13:17 Go to previous messageGo to next message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

Any suggestions or any idea if you think should work please let me know.

Thank you.....
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 messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
mehediu wrote on Tue, 27 December 2011 14:17
Any 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!
Re: Consume a webservice and use in Oracle Form Developer 6i [message #537502 is a reply to message #537026] Thu, 29 December 2011 01:08 Go to previous message
mehediu
Messages: 46
Registered: February 2010
Location: Dhaka
Member

Thank you all.

I got my solutions. There was no coding problems.
my archive folder was full that's why it was not worked.

Again Thank you all.
Previous Topic: how to create a table with multiple columns using forms
Next Topic: How To insert Column values into text items?
Goto Forum:
  


Current Time: Sun Sep 08 07:26:41 CDT 2024