Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g
EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407650] Wed, 10 June 2009 23:49 Go to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Hi,
I am facing a problem while executing a stored procedure having IN and out parameters.
Please tell me how to resolve this problem.
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407652 is a reply to message #407650] Wed, 10 June 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please tell me how to resolve this problem.

Read The Fine Manual in URL below
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407653 is a reply to message #407650] Thu, 11 June 2009 00:04 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Hi,
I am facing a problem with EXECUTE IMMEDIATE while executing a stored proc having IN and OUT parameters.
Please tell me how to EXECUTE IMMEDIATE the stored procdures having IN and OUT parameters

vDynSql := 'SP_'||vRuleName||'('||:pA,:pB,:poutC,:poutD ||');';

EXECUTE IMMEDIATE vDynSql USING pA,pB ;
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407654 is a reply to message #407653] Thu, 11 June 2009 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
stop spamming

http://www.orafaq.com/forum/t/146322/136107/
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407660 is a reply to message #407654] Thu, 11 June 2009 00:16 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Look for USING and INTO for EXECUTE IMMEDIATE
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407663 is a reply to message #407653] Thu, 11 June 2009 00:21 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
:pA,:pB,:poutC,:poutD
Quote:
USING pA,pB
Who will pas the other two?

By
Vamsi
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters [message #407666 is a reply to message #407663] Thu, 11 June 2009 00:26 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
That's the problem. How to pass the out parameters? and how to return the data?
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407670 is a reply to message #407650] Thu, 11 June 2009 00:50 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
Here is a simple example. Hope this helps you.


SQL> create or replace procedure p(in_a in integer, out_a out integer)
  2  as
  3  begin
  4    out_a := in_a;
  5  end;
  6  /

Procedure created.

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_in_a integer;
  3    l_in_b integer;
  4  begin
  5    l_in_a := 10;
  6
  7    execute immediate 'begin p(:1,:2); end;' using in l_in_a, out l_in_b;
  8
  9    dbms_output.put_line(l_in_b);
 10
 11  end;
 12  /
10

PL/SQL procedure successfully completed.

SQL>
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407678 is a reply to message #407670] Thu, 11 June 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please do not encourage lazyness, OP could easily find the answer in the link posted by BlackSwan.

Regards
Michel
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407681 is a reply to message #407650] Thu, 11 June 2009 01:32 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
I am applying the ame idea suggested by Swan but I am getting an error "IN bind variable bound to an "OUT" position.
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407684 is a reply to message #407681] Thu, 11 June 2009 01:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Swan has suggested to read the documentation.
May be you have followed Karthick's suggestion.

What is your new code?
Is it something like
Quote:
USING pA,pB,pA,pB
Read Karthick's post again.
I suspect you have given same variables to both IN and OUT.

By
Vamsi
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407685 is a reply to message #407684] Thu, 11 June 2009 01:52 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
This is the real code

vDynSql:= ' BEGIN SP_'|| vRule_name ||'( :pmsginternalid,
:pourtheirside,
:pbackoffice_grp,
:vT2OurTheir,
:vdeal2deal,
:vProceedY_N,
:vclear_agent1,
:vclear_agent2,
:poutmsginternalid,
:poutmsgtype,
:poutpartyaaliasint,
:poutpartybaliasint,
:poutmbbranchcode,
:pouttypeofoperation,
:poutbuyrecagent,
:poutsellrecagent,
:poutbusinesssegment,
:poutlinkid,
:poutmsgstatus);'||' END; ' ;

Execute Immediate vDynSql /*INTO vMatchRecType(1).msginternalid,
vMatchRecType(1).msgtype ,
vMatchRecType(1).partyaaliasint,
vMatchRecType(1).partybaliasint,
vMatchRecType(1).mbbranchcode ,
vMatchRecType(1).typeofoperation ,
vMatchRecType(1).RecAgent_A ,
vMatchRecType(1).RecAgent_B,
vMatchRecType(1).businesssegment,
vMatchRecType(1).linkid ,
vMatchRecType(1).msgstatus*/
USING IN pmsginternalid,pourtheirside , pbackoffice_grp , vT2OurTheir , vdeal2deal, vProceedY_N, vclear_agent1, vclear_agent2,
OUT vMatchRecType(1).msginternalid,
vMatchRecType(1).msgtype ,
vMatchRecType(1).partyaaliasint,
vMatchRecType(1).partybaliasint,
vMatchRecType(1).mbbranchcode ,
vMatchRecType(1).typeofoperation ,
vMatchRecType(1).RecAgent_A ,
vMatchRecType(1).RecAgent_B,
vMatchRecType(1).businesssegment,
vMatchRecType(1).linkid ,
vMatchRecType(1).msgstatus ;
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407687 is a reply to message #407685] Thu, 11 June 2009 01:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Crosscheck your procedure code.
Are you passing correct variables as IN and OUT?

By
Vamsi
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407688 is a reply to message #407687] Thu, 11 June 2009 02:07 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Finally I've got it.
Actually we should use IN and OUT before each parameter.

Thanks Karthik and Vamsi for looking into this.
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #407689 is a reply to message #407685] Thu, 11 June 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g [message #408022 is a reply to message #407688] Sat, 13 June 2009 00:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bad code. Why not move all the SP_<rule> procedures to a package, make them all private, add one public procedure with the rulename as parameter.
Then in this procedure create a big if-then-else calling STATICALLY the correct procedure. It will be easier to maintain, easier to code, less error-prone.
Previous Topic: Email the output spool file name having date as filename
Next Topic: int to varchar problem ?
Goto Forum:
  


Current Time: Sat Dec 10 22:42:12 CST 2016

Total time taken to generate the page: 0.12250 seconds