Cursor-for-loop Out Of Bounds Error error in PLSQL code

Vyoma · September 26, 2006

I have been working on some code that has really caused me to loose few of my hair due to excessive scratching. Here is the problem I am facing:

I have table, on which I have a cursor defined:

  CURSOR cur_some_record IS	SELECT *	  FROM some_loaded_table	 ORDER BY some_sort_column;

I am later using it in the PLSQL code as follows:

-- Some intializing code  FOR some_record IN cur_some_record  LOOP	-- Do processing on each of the record	-- referencing it as some_record.<some_column>  END LOOP;

So, it is a pretty straight forward requirement. I have a table. I need to select all the rows from it in a particular order. After processing the record, it should pick the next record, until there are no further records.

Everything seems to be going fine, when there are more than one records to be processed. But if there is one, and only one record in the table (and thus only one record in the result set of the cursor), it throws an 'OUT OF BOUNDS' SLQERROR.

As far as I know, the FOR CURSOR LOOP does the CURSOR handling implicitally and one need not do the OPEN, FETCH, CLOSE and check for exit conditions like in LOOP, or WHILE LOOP.

So, I am having out-of-bounds-cursor error if there is one row. If there are more than one row, I have no problem.

Anyone have any idea here as to what I am missing, and what I need to do?

bakr_2k5 · September 26, 2006

Hello Vyoma,Maybe you could use a blank first row? And skip the first row (the 0 'zero' row) in the loop?Don't know if it's a suitable solution. I used to using that in PHP / MySql can't really rember why bakr_2k5

Vyoma · September 26, 2006

I am not sure I can do that. You see, it is not that the first row is skipped. The first row needs to be processed.

The problem is that the the PLSQL block throws an exception whenever there is just one row in the table. If there are more than one row, it does not throw any exception and processes all the records properly.

But if it is one row, it processes it and then attempts to fetch the next (non-existant) row. This results in it throwing an execption. I am not sure why a cursor-for-loop is throwing exception with that specific SQL error code because the cursor-for-loop is supposed to handle the cursor implicitally.

bakr_2k5 · September 26, 2006

Ok i edited a bit of your second code.
Could this be what you want?

-- Some intializing code  FOR some_record IN cur_some_record  LOOP				 IF some_record < 2				THEN					   -- process the lonely record					  EXIT;				 END IF;	-- Do processing on each of the record	-- referencing it as some_record.<some_column>  END LOOP;

bakr_2k5

Vyoma · September 26, 2006

Ok i edited a bit of your second code.

Could this be what you want?

-- Some intializing code  FOR some_record IN cur_some_record  LOOP				 IF some_record < 2				THEN					   -- process the lonely record					  EXIT;				 END IF;	-- Do processing on each of the record	-- referencing it as some_record.<some_column>  END LOOP;

bakr_2k5

No bakr_2k5. That too is not the case of what I actually want. It has to do something with exception handling, that I cannot place my finger at.

Moreover we cannot reference the 'some_record' on its own - because 'some_record' is technically a record of cur_some_record%ROWTYPE. Thus, giving a 'some_record < 2' would throw up a inappropriate variable type compile time error.

The problem is that the Cursor-For-Loop continues to loop even after end of processing all the records only when the record count is 1. I have been thinking in the following terms now:

  CURSOR cur_some_record IS	SELECT *	  FROM some_loaded_table	 ORDER BY some_sort_column;  v_rowcount  NUMBER;  v_lonerecord cur_some_record%ROWTYPE;

And in the PLSQL body block:

-- Some intializing code  SELECT COUNT(*)	INTO v_rowcount	FROM some_loaded_table;  IF v_rowcount > 1 THEN	FOR some_record IN cur_some_record	LOOP	  -- Do processing on each of the record	  -- referencing it as some_record.<some_column>	END LOOP;  ELSE	SELECT *	  INTO v_lonerecord	  FROM some_loaded_table;	-- Do the same processing for the lone record	-- using the referencing as v_lone_record.<some_column>

This is definetly a round about and crude method as far as I can see and defeats the purpose of using the FOR loop. The loop should be able to handle the case of zero or one or more rows. I can only think of either that or something in terms of the following lines:

  BEGIN	-- All the old FOR LOOP code in the first post	-- (Not the one I have given in this post)  EXCEPTION	WHEN OTHERS THEN	  -- Just put "I am fine! Everything OK.  Just one row in table." 	  -- to the log table.  END;

I would not even like to do the above thing because then, I would not be able to identify any other exception from with in that nameless/anonymus block - any other excecption other than the out of bounds may be thrown and I would not want those to be reported wrongly.

bakr_2k5 · September 26, 2006

Hmm ok, please not that i'm not a PLSQL user or developper or whatsoever, i just look at the code and try to help :)But I found something I think it could be usefull. I'm currently trying to figure out how PLSQL works (the part where youre problem is)Later tonight (The Netherlands) I come up with a new solution, I HOPE I'm allready working on the code a bit, but just need too know a bit more about PLSQL.But so far what I understand is that you want to use the EXCEPTION command to handle the lonely row?I got to eat now. bakr_2k5

Vyoma · September 26, 2006

Yes, in a way. My present code, finds that exception, and aborts fatally. Using it thus would make it handle it and then continue with the rest of the program.

But during this time, I have had a bit of talk with SP Rao, and he suggests I use the WHILE loop infinetly, and manually check for end of rows, and break out of the loop. I will try it and let you all know.

The only thing that irks me is that the cursor-for-loop is not working as it has to. Either that, or I am not understanding it properly.

bakr_2k5 · September 26, 2006

Well I'm back and came up with something again
It took a while and I gues it isn't what you look for but eh I did it without any knowlage about PLSQL!

PROCEDURE process_records() -- or what ever you want to call itIS	CURSOR cur_some_record IS		SELECT *		FROM some_loaded_table		ORDER BY some_sort_column;BEGIN	SELECT COUNT(*)		INTO v_rowcount		FROM some_loaded_table;	FOR some_record IN cur_some_record	LOOP		-- Do processing on each of the record		-- referencing it as some_record.<some_column>		EXIT WHEN v_rowcount < 2; -- Will process the first row and then shut the loop down IF there is 1 row	END LOOP;END;

I used this tutorial along side:
http://docstore.mik.ua/orelly/oracle/prog2/

Otherwise I wouldn't get this code

I HOPE you can use this

bakr_2k5

iGuest · January 23, 2008

i have problem to solve this program. I want to store this item in database which every item getselected. Please kindly send answer for this so that i can process this with the help of servlets.

Cursor-for-loop Out Of Bounds Error

</head>

<body>

<tr>

</tr>

<br>

<br>

<CENTER><B><ins>MAGAZINES - 17</ins></B></CENTER>

<tr>

Choose your Magazines

</td>

</tr>

<tr>

<hr> ***** MANAGEMENT -9 *****</hr>

<hr>

</td>

</tr>

<tr> <TD><font size=2%px><ins>TITLES</ins></font></TD>

<TD><font size=2%px><ins><center>PERIOD</center></ins></font></TD>

<TD><font size=2%px><ins><center>PRICE</center></ins></font></TD>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="Effective Executive" onClick="calc()">Effective Executive</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="Global CEO" onClick="calc()">Global CEO</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="E-Business" onClick="calc()">E-Business</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="Mastermind" onClick="calc()">Mastermind</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="Advertising Express" onClick="calc()">Advertising Express</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="HRM Review" onClick="calc()">HRM Review</font></td>

</td>

</tr>

<tr><TD><font size=2%px><input type=checkbox name="MBA Review" onClick="calc()">MBA Review</font></td>

</td>

</tr>

-santosh

Sign In

Cursor-for-loop Out Of Bounds Error error in PLSQL code

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Important Information