Home » SQL & PL/SQL » SQL & PL/SQL » multiple cursors in one stored procedure
multiple cursors in one stored procedure [message #145113] Mon, 31 October 2005 23:13 Go to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
I have a stored procedure with 2 cursors to deal with DB to DB
data transactions.
...
CURSOR c1 IS SELECT * FROM T1;
CURSOR c2 IS SELECT * FROM T2;
BEGIN
   ...
   FOR each_data IN c1
   LOOP
   /* some business logic */
   END LOOP;
   CLOSE c1;

   /* start another cursor */
   FOR each_row IN c2
   LOOP
      /* compare each row in T1 */
      /* remove non-matched row from T2 */
   END LOOP;
   CLOSE c2;
   ...
END;

As showed above, after the first cursor completed, I got "invalid
cursor" in second cursor. The second concern about removing data from T2. Since c2 is built up from T2, if the data is
deleted where the current cursor pointed to T2. Doesn't effect
anything? Is this the correct way to handle cursors?

Thanks,
Lucas
Re: multiple cursors in one stored procedure [message #145122 is a reply to message #145113] Mon, 31 October 2005 23:59 Go to previous messageGo to next message
chj733
Messages: 11
Registered: October 2005
Location: CHINA
Junior Member
You can create a array to store the data coming from t1,and then do what you want,because you have close the cursor of t1 when you want to use it

don't worry about the fallow rows when you open the cursor,you'll not see the rows,because of the consistency read
Re: multiple cursors in one stored procedure [message #145126 is a reply to message #145113] Tue, 01 November 2005 00:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Because you use an implicit cursor (for row in cursor instead of open - fetch - close) you don't have to close the cursor. In fact, you cannot close it.
By the time the 'close c1' is reached, cursor c1 is already closed, resulting in the error 'Invalid cursor'.

You should use 1 cursor to handle this. As chj733 mentioned, the results from c1 are not available in your c2-loop, unless you store them in an array.

hth
Re: multiple cursors in one stored procedure [message #145170 is a reply to message #145126] Tue, 01 November 2005 08:18 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
You should use 1 cursor to handle this. 
As chj733 mentioned, the results from c1 
are not available in your c2-loop, unless 
you store them in an array.


I don't think I can use one cursor to hanle them because the
cursor2 has to compare to Table 1, and remove non-matched
from Table 2 which cursor2 is built from.

I am familiar in using array in PL/SQL, could you give me some
examples/hints please? And do you suggest that using two
stored procedures to handle these two different cases, and
put both of them into one stored procedure?

Thanks,
Lucas

[Updated on: Tue, 01 November 2005 08:21]

Report message to a moderator

Re: multiple cursors in one stored procedure [message #145172 is a reply to message #145170] Tue, 01 November 2005 08:53 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
First of all, whenever you are using a cursor FOR LOOP,
you must strictly keep this in mind that Oracle implicitly
opens,fetches in a loop, and finally closes the cursor.
you should comment the line in your code which closes cursor c1,
that's the reason it is giving you an error as INVALID CURSOR
because oracle implicitly has already closed that cursor.

You can create a index by table /pl sql table type and store the data of t1 into it and then can easily use a for loop
in cursor c2 to find the matched/unmatched rows and delete
them accordingly.

If you still need clarification, type the code and specify what you want exactly.

Regards,
Rajiv.
Re: multiple cursors in one stored procedure [message #145246 is a reply to message #145170] Wed, 02 November 2005 00:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
lucas4394 wrote on Tue, 01 November 2005 15:18


I don't think I can use one cursor to hanle them because the
cursor2 has to compare to Table 1, and remove non-matched
from Table 2 which cursor2 is built from.



SQL is al about comparing one table to another. Maybe 'NOT EXISTS' will do for you?

hth
Re: multiple cursors in one stored procedure [message #151407 is a reply to message #145113] Wed, 14 December 2005 12:53 Go to previous messageGo to next message
donlcs
Messages: 62
Registered: October 2005
Location: VA, USA
Member
First, I hope the problem has been resolved. If my understanding of the problem is correct. Then, I would think at least two possible solutions exist.

*) The first one would seem more elegant, as Frank suggested use EXIST function, along the line of
Delete
From t2
Where EXISTS
t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3
...
/* exactly match? the two tables are truly identical */
)


*) nested loops, in other words, the second loop should be inside the first one, I don't think that's going to be efficient.

Re: multiple cursors in one stored procedure [message #151470 is a reply to message #151407] Wed, 14 December 2005 23:59 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
I have done something like that
in one OF my 3rd table insert.

THIS WORKS PERFECTLY WELL,
might be helpfull:

DECLARE
CURSOR c1 IS SELECT * FROM merlin ORDER BY stkcd;
CURSOR c2 IS SELECT * FROM omega ORDER BY stkcd;
v_loc T.LOC%TYPE;
v_stkcd T.STKCD%TYPE;
v_oldcode T.OLDCODE%TYPE;
v_qty T.QTY%TYPE;
v_wac T.WAC%TYPE;
v_val T.VAL%TYPE;
v_pair_num T.PAIR_NUM%TYPE;
BEGIN
OPEN c1;
OPEN c2;
LOOP
FETCH c1 INTO v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val;
FETCH c2 INTO v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val;
IF (c1%FOUND) AND (c2%FOUND) THEN
v_pair_num := v_pair_num +1;
INSERT INTO T VALUES(v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val,v_pair_num);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c1;
CLOSE c2;
END;
/
Re: multiple cursors in one stored procedure [message #151576 is a reply to message #145113] Thu, 15 December 2005 13:38 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm really at a loss of understanding on this one. As far as the last poster goes, what in the world are you doing? Fetching a random row out of a table. Then fetching a random row out of another table, but overwriting the first row? Then if both fetches were successful, insert the random row from the second table into a third table with a count of the number of fetches that happened so far? What is the point/goal of doing the above, and what is the goal you are trying to accomplish?

As far as the OP is concerned, you haven't very clearly explained your purpose in english; can you? But as was hinted by Frank, this totally seems like something that should at the least use implicicit cursors, but most probably be in a single query. Are you just trying to examine the rows in t2 and delete those that are not in t1?

In both cases you guys are doing this row at a time style which is the slowest way possible.
Previous Topic: Do you know if we can convert XMLType in some way into a BLOB instead?
Next Topic: How to force to use indexes [solved]
Goto Forum:
  


Current Time: Wed Dec 17 17:28:49 CST 2014

Total time taken to generate the page: 0.11467 seconds