Home » SQL & PL/SQL » SQL & PL/SQL » output of a refcursor which is inside a loop (Oracle 11g)
output of a refcursor which is inside a loop [message #616408] Mon, 16 June 2014 11:40 Go to next message
ashish_2005
Messages: 4
Registered: June 2014
Location: Bangalore
Junior Member
I have a written a proc. The input parameter of the proc is a string of batch ids which comes like '1234,6788,8888,9999' and now
I want that output of this proc for each execution of passed batch ids should get the records for all the passed batches. As of now I am only getting the records for the last passed batch id.

for eg in this case I am getting the records only for the last processed batch 354538. I want the records for both batch ids '280757,354538'
Code is right here

execution
set serveroutput on 
Variable V Refcursor;
Exec Rptco.Dub_Bat_Comm_Select('280757,354538' , :v);
print :v;



[EDITED by LF: applied [spoiler] & some [code] tags]

[Updated on: Tue, 17 June 2014 02:53] by Moderator

Report message to a moderator

Re: output of a refcursor which is inside a loop [message #616410 is a reply to message #616408] Mon, 16 June 2014 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is completely unreadable.
Please How to use [code] tags and make your code easier to read.
Anyway, you don't expect we reverse engineer your code.
So create a simple test case that reproduce your issue and post it.

Re: output of a refcursor which is inside a loop [message #616411 is a reply to message #616408] Mon, 16 June 2014 12:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. Why do you use batch ids as string? It should be NUMBER data type. Avoid using to_number and let it be a number type.
2. I don't know the rules of your business logic, but cursor for loop is row by row aka slow by slow operation.
Re: output of a refcursor which is inside a loop [message #616435 is a reply to message #616410] Mon, 16 June 2014 19:10 Go to previous messageGo to next message
ashish_2005
Messages: 4
Registered: June 2014
Location: Bangalore
Junior Member
Hi all,

I just want if it is possible to store the output of refcursor for each batch id which I am passing. Also we are calling this procedure from a front end screen from which we will pass the values to the batch like '12344,5667,6666,34444'

If the code is readable we can create two tables for eg emp , emp1 having same data and then we can return the data in a loop for both the table's records.

My problem is that if I am getting the data only for the last batch id.

Thanks
Ashish
Re: output of a refcursor which is inside a loop [message #616436 is a reply to message #616435] Mon, 16 June 2014 19:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: output of a refcursor which is inside a loop [message #616437 is a reply to message #616411] Mon, 16 June 2014 20:17 Go to previous messageGo to next message
ashish_2005
Messages: 4
Registered: June 2014
Location: Bangalore
Junior Member
Lalit,

I am just looking for storing the output of a refursor. Is there any way to that? Also what if if the refcursor statement is also inside a loop.

You can also create a sample example on some tables at your end too

Thanks
Ashish
Re: output of a refcursor which is inside a loop [message #616438 is a reply to message #616437] Mon, 16 June 2014 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am just looking for storing the output of a refursor.
The data is already stored inside the database, why do you want to duplicate the data.
Why not just CREATE VIEW?

>You can also create a sample example on some tables at your end too
You are the one with the problem; not us.
We don't care if your question gets answered or not.

I don't know what your desired solution should be.
Re: output of a refcursor which is inside a loop [message #616446 is a reply to message #616438] Tue, 17 June 2014 01:58 Go to previous messageGo to next message
ashish_2005
Messages: 4
Registered: June 2014
Location: Bangalore
Junior Member
Hi it is not duplicating the data. I need to get the corresponding data for different batch ids which I am passing as a input string. VIEW is also not possible as of now.

I am thinking of inserting the data in a temporary table and then that table data can be read. Please advice if any other solution is there.
Re: output of a refcursor which is inside a loop [message #616450 is a reply to message #616446] Tue, 17 June 2014 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Provide what has been asked.

Re: output of a refcursor which is inside a loop [message #616454 is a reply to message #616450] Tue, 17 June 2014 02:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you simply combine the cursor and ref cursor into a single ref cursor?
Re: output of a refcursor which is inside a loop [message #616457 is a reply to message #616454] Tue, 17 June 2014 02:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
But what is the use of fetching data from DB and then storing it back into a different table using ref cursor? That's what OP is saying.
Re: output of a refcursor which is inside a loop [message #616460 is a reply to message #616457] Tue, 17 June 2014 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No. OP is asking if he should store the data from all the different executions of the ref cursor in the loop in a temp table so that the caller can then get all the data back in one go.
Re: output of a refcursor which is inside a loop [message #616464 is a reply to message #616460] Tue, 17 June 2014 03:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
This is what i understand about OP's requirement :
1. Some batch ids are input parameter as a string.
2. Based on each batch id data needs to be fetched.
3. OP used a loop over these batch ids hoping to combine the result set of each loop in the ref corsor --> This is where he went wrong. Since the result set that the ref cursor would hold is only that of the last loop.

I agree with CM, just open cursor for all the queries for each batch id using UNION, keep the cursor open.
Re: output of a refcursor which is inside a loop [message #616467 is a reply to message #616464] Tue, 17 June 2014 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
UNION implies a known number of batch ids, which isn't the case, so UNION isn't helpful here.
Re: output of a refcursor which is inside a loop [message #616468 is a reply to message #616467] Tue, 17 June 2014 04:08 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hmm, I missed that point. Well, if OP manages to fetch data based on different batch ids using a single query(which doesn't seem in his case as he has an IF-ELSE condition based on batch ids) then instead of converting the batch ids into rows and doing it in loop, an IN condition for batch ids would have sufficed.

I am thinking of having the result set in a GTT and then open cursor for select * from GTT.
Previous Topic: Days Between Start date/today, unless Date finished.
Next Topic: IN Condition
Goto Forum:
  


Current Time: Thu Mar 28 07:23:04 CDT 2024