Home » SQL & PL/SQL » SQL & PL/SQL » stored package (11i apps, 10g db)
stored package [message #358781] Wed, 12 November 2008 08:38 Go to next message
foxcaller53
Messages: 2
Registered: November 2008
Junior Member
I have 2 cursors defined in my pkg and get a compile error on the 2nd cursor when I try to link the 2 together using the key from cursor 1 to the key from cursor 2.
Please review the uploaded file and let me know if you see something not correct. I inserted the error at the 2nd cursor showing the error.

thanks.
Dave
Re: stored package [message #358783 is a reply to message #358781] Wed, 12 November 2008 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do that.

Regards
Michel
Re: stored package [message #358784 is a reply to message #358781] Wed, 12 November 2008 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The Record type cur_810_rec only exists inside the For Loop that it is defined in.

Remove the reference from the second cursor, and replace it with a cursor parameter.
Then pass the value cur?_810_rec.a810_seq_id into the second cursor when you start looping through it.

Or, just remove both cursor For Loops and do a single Insert Into ... Select, which will be far faster.
Re: stored package [message #358834 is a reply to message #358784] Wed, 12 November 2008 10:48 Go to previous messageGo to next message
foxcaller53
Messages: 2
Registered: November 2008
Junior Member
JRow,
thanks for the info. I changed it per your suggestion but it still fails. I've uploaded the cursor info and marked where it's failing. do you see something else I missed?
thanks a bunch from Pennsylvania.

Dave
Re: stored package [message #358838 is a reply to message #358781] Wed, 12 November 2008 11:05 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom suggested you use cursor parameters, which would look something like this:

CURSOR c1 (p1 tab1.col1%TYPE) IS
SELECT columns
FROM tab1
WHERE col1 = p1;

BEGIN

FOR rec IN c1(variable1) LOOP
....



Doing that would sort your problem.
That aside you can't refer to a variable in code until AFTER you've declared it.
Previous Topic: Trigger problem
Next Topic: Using Fields Again in the Same Script
Goto Forum:
  


Current Time: Mon Dec 05 11:16:04 CST 2016

Total time taken to generate the page: 0.07945 seconds