Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed in Ref Cursors (Oracle 10g)
Help Needed in Ref Cursors [message #390081] Wed, 04 March 2009 21:14 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hi

Hi I have 3 Procedures below which outputs ref cursors.
Proc1(Table_name IN VArchar2,result_set_1 OUT ref_cur_1) is
Begin
Open result_set_1 For
      Select Id,Name From Table_name;
End;
 
Proc2(Table_name_2  IN VArchar2,result_set_2 OUT ref_cur_2) is 
Begin
Open result_set_2 For
      Select dept,sal From Table_name;
End;
 
Proc3(Table_name_3  IN VArchar2,result_set_3 OUT ref_cur_3) is 
Begin
Open result_set_3 For
      Select Pension_plan,Loan,deductions From Table_name;
End;


Now I need to write a main proc which will use these three procs and outputs a ref cursor which will hold the
contents of the other proc's output.
Proc_main (Table_in_1 IN VArchar2,Table_in_2  IN VArchar2,Table_in_3  IN VArchar2, result_set OUT ref_cur_type)


Here the result_set in the Proc_Main is the combination of all the result sets in those three proc's.
Can you help me in writing the logic??
Re: Help Needed in Ref Cursors [message #390086 is a reply to message #390081] Wed, 04 March 2009 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Here the result_set in the Proc_Main is the combination of all the result sets in those three proc's.
What do you mean by "combination"?

What problem are you really trying to solve?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Help Needed in Ref Cursors [message #390099 is a reply to message #390081] Wed, 04 March 2009 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: Help Needed in Ref Cursors [message #390524 is a reply to message #390099] Fri, 06 March 2009 23:46 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I may not be understanding all of your situations however, that has never stopped me from opening my mouth to talk. Your solution is not in using multiple refcursors. Instead try using manually partitioned views (or manually partitioned sql). But you have a problem to get over first.

In order to combine results of multiple select statements, they must all be "UNION COMPATIBLE". That essentially means each select statement must have the same number of columns and positionally each column must have the same datatype in every statement. This means you three sql statements are not compatible because one has two columns and one has three. However, here is an example of how to do it:

select a,b,c from t1 where 'xyz' = :somevar
union all
select a,b,c from t1 where 'pdq' = :somevar
union all
select a,b,c form t1 where 'abc' = :somevar
/


depending upon the value of your variable somevar, only one of these three queries will execute. Thus you can have one procedure that conditionally executes one of three different sql statements and returns results as a refcursor. Of course you could also have used if statements and opened the same refcursor variable with a different sql as well with the same end result. It all depends upon how you see things. I prefer the partitioned method because it can be done using a traditional view and thus becomes highly reusable. Of course with the advent of table functions, view solutions based on plsql blurr this distinction.

Anyway, good luck, Kevin
Previous Topic: TIME COLUMN
Next Topic: ORA-12801
Goto Forum:
  


Current Time: Fri Dec 09 15:26:24 CST 2016

Total time taken to generate the page: 0.95438 seconds