Home » SQL & PL/SQL » SQL & PL/SQL » Cursor is Out of Scope (Oracle using Toad 9.5)
Cursor is Out of Scope [message #334875] Fri, 18 July 2008 08:44 Go to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
I keep getting this error everytime and I really don't know how to fix it. Can someone please help

here's error
****************************************************************
ORA-06550: line 43, column 21:
PLS-00225: subprogram or cursor 'cREC' reference is out of scope
****************************************************************

See the attached file for the code refered. Please someone help
  • Attachment: orAl.txt
    (Size: 0.96KB, Downloaded 269 times)
Re: Cursor is Out of Scope [message #334877 is a reply to message #334875] Fri, 18 July 2008 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

How does the LOOP know when to end?
Re: Cursor is Out of Scope [message #334878 is a reply to message #334875] Fri, 18 July 2008 09:00 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Read this VERY carefully
However, you might want to consider collections and bulk operations
Re: Cursor is Out of Scope [message #334882 is a reply to message #334875] Fri, 18 July 2008 09:21 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Anacedent,
In the cursor, it only pulls so many records..its about 11, so when it starts writing them out in the for loop, whenever there is nothing else to pull from the cursor the loop ends.
Re: Cursor is Out of Scope [message #334885 is a reply to message #334875] Fri, 18 July 2008 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>whenever there is nothing else to pull from the cursor the loop ends

Does the LOOP end before or after the error is thrown?
Hows does LOOP know/detect/decide it is appropriate to end?
Re: Cursor is Out of Scope [message #334886 is a reply to message #334882] Fri, 18 July 2008 09:33 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
How about you post your actual code.
Re: Cursor is Out of Scope [message #334887 is a reply to message #334875] Fri, 18 July 2008 09:36 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Assuming I'm not missing the point here (given its Friday afternoon of a poor week) should it not be
REC.COMPANY


instead of
CREC.COMPANY


FOR REC IN cREC LOOP

        V_COUNT := V_COUNT + 1;
        
      		SELECT COMPANY
		INTO V_COMPANY
		FROM myTransactions
		WHERE COMPANY = cREC.COMPANY;    /*It does not like this line*/

	DBMS_OUTPUT.PUT_LINE(' REPORTS CREATED FOR - ' ||V_COMPANY);

	END LOOP;
Re: Cursor is Out of Scope [message #334888 is a reply to message #334882] Fri, 18 July 2008 09:41 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Anacedent,
I guess I could say if cRec%NOTFOUND get out or something like that. However my main concern right now is that it complains that
WHERE COMPANY = cREC.COMPANY;

is out of scope.

I have tested the sql statements and it only gives me so many records. So can you or anyone else please help with the out of scope error and how I would refer to cRec within the "for loop" so I can pass the records into the for loop?

Thank you.
Re: Cursor is Out of Scope [message #334889 is a reply to message #334888] Fri, 18 July 2008 09:45 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Assuming that you are only using this code as a learning tool and do not intend using it in a production environment, surely you would be better served to actually try to learn what is required rather than looking for someone to spoon feed you the answer. By carefullyreading the link that I provided, the answer should become apparent.
Re: Cursor is Out of Scope [message #334890 is a reply to message #334886] Fri, 18 July 2008 09:52 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Scopio biker,
You are nuts man..that was it..man, thanks man. Crazy error TGIF. This is my first time in this forum, its cool thanks
Re: Cursor is Out of Scope [message #334892 is a reply to message #334888] Fri, 18 July 2008 10:00 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Pablolee,
I see what are you saying, this code is not as small as I provided there, its a massive production in an actual work environment. Code was written in separate files by different people and I get to put it all together and automate the process in Oracle and sometimes a small error can seem big at least to me especially when you are dealing with someone else's code. Everyone has their own style and I have never dealt with cursors before....so i was not looking to be spoon fed and I bet you've had to ask for help before.
Re: Cursor is Out of Scope [message #334893 is a reply to message #334875] Fri, 18 July 2008 10:01 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Glad that helped, but it's definately worth reading the link posted by pablolee (which I missed before I posted) as that explains how to do a cursor for loop and should have helped you spot your problem before I butted in Very Happy
Re: Cursor is Out of Scope [message #334896 is a reply to message #334893] Fri, 18 July 2008 10:08 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Thanks for the tip
Re: Cursor is Out of Scope [message #334898 is a reply to message #334892] Fri, 18 July 2008 10:13 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
this code is not as small as I provided there
Little note to allude to that at the start may prove useful.
Quote:
its a massive production in an actual work environment
Then unless you are working in an old and unsupported version, a cursor loop would probably be the wrong way to go.
Quote:
so i was not looking to be spoon fed
Sorry, but I disagree, I posted a link to a resource that would have helped you solve the issue yourself, but you decided to ignore it and continue asking for assistance.
Quote:
and I bet you've had to ask for help before.
Many, many times, but when I do, I post an accurate representation of what I am actually trying to do, and as soon as someone posts a link to a resource that should point me in the direction of what I need to do, I will stop, read through the link, try myself and if I am still unable to resolve, come back and show what I have attempted and ask for further clarification.

[Updated on: Fri, 18 July 2008 10:14]

Report message to a moderator

Re: Cursor is Out of Scope [message #334905 is a reply to message #334898] Fri, 18 July 2008 10:45 Go to previous messageGo to next message
AlKing
Messages: 11
Registered: July 2008
Location: Bakersfield
Junior Member
Thanks a lot I will remember this next time. I genuinely thank you.
Re: Cursor is Out of Scope [message #334907 is a reply to message #334905] Fri, 18 July 2008 10:53 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Smile
Previous Topic: UTL FILE issue
Next Topic: Index creation error
Goto Forum:
  


Current Time: Fri Dec 09 23:30:06 CST 2016

Total time taken to generate the page: 0.08730 seconds