Cursor-for-loop Out Of Bounds Error

Vyoma

Sep 26 2006, 01:45 PM

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:

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?

Comment/Reply (w/o sign-up)

bakr_2k5

Sep 26 2006, 02:02 PM

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

Comment/Reply (w/o sign-up)

Vyoma

Sep 26 2006, 02:19 PM

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.

Comment/Reply (w/o sign-up)

bakr_2k5

Sep 26 2006, 02:34 PM

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

Comment/Reply (w/o sign-up)

Vyoma

Sep 26 2006, 03:19 PM

QUOTE(bakr_2k5 @ Sep 26 2006, 08:04 PM)

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

CODE

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:

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.

Comment/Reply (w/o sign-up)

bakr_2k5

Sep 26 2006, 04:14 PM

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

Comment/Reply (w/o sign-up)

Vyoma

Sep 26 2006, 06:05 PM

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.

Comment/Reply (w/o sign-up)

bakr_2k5

Sep 26 2006, 06:43 PM

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!

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

Comment/Reply (w/o sign-up)

FeedBacker

Jan 23 2008, 04:56 AM

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

Comment/Reply (w/o sign-up)

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

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