Index
 |
Cursor-for-loop Out Of Bounds Error - error in PLSQL code | ||
Discussion by Vyoma with 9 Replies.
Last Update: January 23, 2008, 4:56 am | |||
 |
|||
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?
I have table, on which I have a cursor defined:
CODE
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:
CODE
-- 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?
Tue Sep 26, 2006 Reply New Discussion
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
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
Tue Sep 26, 2006 Reply New Discussion
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.
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.
Tue Sep 26, 2006 Reply New Discussion
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
Could this be what you want?
CODE
-- 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
Tue Sep 26, 2006 Reply New Discussion
QUOTE (bakr_2k5)
Ok i edited a bit of your second code.
Could this be what you want?
CODE
-- 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
Link: view Post: 87980
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:
CODE
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:
CODE
-- 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:
CODE
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.
Tue Sep 26, 2006 Reply New Discussion
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
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
Tue Sep 26, 2006 Reply New Discussion
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.
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.
Tue Sep 26, 2006 Reply New Discussion
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 it
IS
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://www.unix.org.ua/orelly/oracle/prog2/
Otherwise I wouldn't get this code
I HOPE you can use this
bakr_2k5
It took a while and I gues it isn't what you look for but eh I did it without any knowlage about PLSQL!
CODE
PROCEDURE process_records() -- or what ever you want to call it
IS
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://www.unix.org.ua/orelly/oracle/prog2/
Otherwise I wouldn't get this code
I HOPE you can use this
bakr_2k5
Tue Sep 26, 2006 Reply New Discussion
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>
<td width="800" valign="top"><img src="logo left.Jpg" width="150" height="80" border="0" align="left" usemap="#Map"><img src="iup-logo.Jpg" width="200" height="100" align="right" border="0" usemap="#Map"></div></td>
</tr>
<form name=figs>
<br>
<br><br>
<br>
<CENTER><B><ins>MAGAZINES - 17</ins></B></CENTER>
<table cellspacing=10>
<tr>
<td colspan=3 bgcolor=#0FFFF>
Choose your Magazines
</td>
</tr>
<tr>
<td colspan=6>
<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>QTY</center></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><font size=2%px><div align="center"><select name="numX" size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update1(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text1" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Global CEO" onClick="calc()">Global CEO</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update2(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text2" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="E-Business" onClick="calc()">E-Business</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update3(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text3" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Mastermind" onClick="calc()">Mastermind</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update4(this)">
<option value="940">1 Year</option>
<option value="2475">3 Year</option>
<option value="3000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text4" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Advertising Express" onClick="calc()">Advertising Express</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update5(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text5" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="HRM Review" onClick="calc()">HRM Review</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update6(this)">
<option value="940">1 Year </option>
<option value="2475">3 Year</option>
<option value="3000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text6" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="MBA Review" onClick="calc()">MBA Review</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value=1>1</option>
<option value=2>2</option>
<option value=3>3</option>
<option value=4>4</option>
<option value=5>5</option>
<option value=6>6</option>
<option value=7>7</option>
<option value=8>8</option>
<option value=9>9</option>
<option value=10>10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update7(this)">
<option value="340">1 Year</option>
<option value="875">3 Year</option>
<option value="1100">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text7" size=3></div></font>
</td>
</tr>
-santosh
Cursor-for-loop Out Of Bounds Error
</head>
<body>
<tr>
<td width="800" valign="top"><img src="logo left.Jpg" width="150" height="80" border="0" align="left" usemap="#Map"><img src="iup-logo.Jpg" width="200" height="100" align="right" border="0" usemap="#Map"></div></td>
</tr>
<form name=figs>
<br>
<br><br>
<br>
<CENTER><B><ins>MAGAZINES - 17</ins></B></CENTER>
<table cellspacing=10>
<tr>
<td colspan=3 bgcolor=#0FFFF>
Choose your Magazines
</td>
</tr>
<tr>
<td colspan=6>
<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>QTY</center></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><font size=2%px><div align="center"><select name="numX" size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update1(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text1" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Global CEO" onClick="calc()">Global CEO</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update2(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text2" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="E-Business" onClick="calc()">E-Business</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update3(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text3" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Mastermind" onClick="calc()">Mastermind</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update4(this)">
<option value="940">1 Year</option>
<option value="2475">3 Year</option>
<option value="3000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text4" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="Advertising Express" onClick="calc()">Advertising Express</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update5(this)">
<option value="625">1 Year</option>
<option value="1650">3 Year</option>
<option value="2000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text5" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="HRM Review" onClick="calc()">HRM Review</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update6(this)">
<option value="940">1 Year </option>
<option value="2475">3 Year</option>
<option value="3000">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text6" size=3></div></font>
</td>
</tr>
<tr><TD><font size=2%px><input type=checkbox name="MBA Review" onClick="calc()">MBA Review</font></td>
<TD><font size=2%px><div align="center"><select name=numX size=1>
<option value=1>1</option>
<option value=2>2</option>
<option value=3>3</option>
<option value=4>4</option>
<option value=5>5</option>
<option value=6>6</option>
<option value=7>7</option>
<option value=8>8</option>
<option value=9>9</option>
<option value=10>10</option></select></div></font></td>
<TD><div align="center"><font size=2%px><select name=numY onclick="update7(this)">
<option value="340">1 Year</option>
<option value="875">3 Year</option>
<option value="1100">5 Year</option></select></div></font></td>
<td><div align="center"><input type="text" name="Text7" size=3></div></font>
</td>
</tr>
-santosh
Wed Jan 23, 2008 Reply New Discussion
 |
 |
|
 |
 |
| Reply / Comment | Ask a Question? | Share / Bookmark | E-Mail a Friend |
 MySQL For EasyPHP Users Does anyone use this program? (6)
|
(8) Flashmyadmin - Flash Based Sql A Flash GUI for SQL 
|