Welcome Guest ( Log In | Register )



 
 Reply to this topicStart new topic
> Cursor-for-loop Out Of Bounds Error, error in PLSQL code
Vyoma
post Sep 26 2006, 01:45 PM
Post #1


Cosmic Overlord
Group Icon

Group: Members
Posts: 549
Joined: 26-November 05
From: Chennai, India
Member No.: 9,811
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?
Go to the top of the page
 
+Quote Post
bakr_2k5
post Sep 26 2006, 02:02 PM
Post #2


Member - Active Contributor
Group Icon

Group: Members
Posts: 83
Joined: 25-September 06
From: The Netherlands
Member No.: 16,153
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 huh.gif

bakr_2k5
Go to the top of the page
 
+Quote Post
Vyoma
post Sep 26 2006, 02:19 PM
Post #3


Cosmic Overlord
Group Icon

Group: Members
Posts: 549
Joined: 26-November 05
From: Chennai, India
Member No.: 9,811
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.
Go to the top of the page
 
+Quote Post
bakr_2k5
post Sep 26 2006, 02:34 PM
Post #4


Member - Active Contributor
Group Icon

Group: Members
Posts: 83
Joined: 25-September 06
From: The Netherlands
Member No.: 16,153
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
Go to the top of the page
 
+Quote Post
Vyoma
post Sep 26 2006, 03:19 PM
Post #5


Cosmic Overlord
Group Icon

Group: Members
Posts: 549
Joined: 26-November 05
From: Chennai, India
Member No.: 9,811
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

-- 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:

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.
Go to the top of the page
 
+Quote Post
bakr_2k5
post Sep 26 2006, 04:14 PM
Post #6


Member - Active Contributor
Group Icon

Group: Members
Posts: 83
Joined: 25-September 06
From: The Netherlands
Member No.: 16,153
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 wink.gif
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 wink.gif
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. biggrin.gif

bakr_2k5
Go to the top of the page
 
+Quote Post
Vyoma
post Sep 26 2006, 06:05 PM
Post #7


Cosmic Overlord
Group Icon

Group: Members
Posts: 549
Joined: 26-November 05
From: Chennai, India
Member No.: 9,811
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.
Go to the top of the page
 
+Quote Post
bakr_2k5
post Sep 26 2006, 06:43 PM
Post #8


Member - Active Contributor
Group Icon

Group: Members
Posts: 83
Joined: 25-September 06
From: The Netherlands
Member No.: 16,153
Well I'm back and came up with something again laugh.gif
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 smile.gif

I HOPE you can use this rolleyes.gif

bakr_2k5
Go to the top of the page
 
+Quote Post
iGuest
post Jan 23 2008, 04:56 AM
Post #9


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
« Next Oldest · SQL (Structured Query Language) · Next Newest »
 

Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. Error 406 - Problem In My Phpbb Forum(8)
  2. Do You Program/code Your Own Games(11)
  3. VB.NET: Howto Add And Delete Files(8)
  4. Does Anyone Code Using Turing(2)
  5. Myspace Code Changing(10)
  6. Free Shoutbox? HTML, Flash or PHP Code(24)
  7. Uploading Image File Through JSP Code To Server(9)
  8. Dynamically Change The Background Image On Mouse Effects!(3)
  9. How To Add Adsense Code In SMF(4)
  10. Windows XP Error: Explorer Crashes While Drag & Drop(14)
  11. Dreamweaver 8 Issue(9)
  12. Bsod Error & It's Causes(6)
  13. Disable Task Manager 1 Line Code![vb6](32)
  14. Tips For Modifying Wordpress Code(1)
  15. Decompile An Exe To Source Code(10)
  1. Whats The Ascii Code Of Your Name?(4)
  2. Java Applet Loading Error(5)
  3. Strange Error When Trying To Install Fedora Core 9(4)
  4. Dynamic Php Image And Better Php Code Question(10)
  5. Linux Basic Command - For Storing Compilation Error To File(1)
  6. Need Help With Code For Battle Calculator For An Mmorpg I Am Planning(0)
  7. Code To Text Ratio Tool(0)
  8. I Have An Error With My Mysql Connection(7)
  9. Mysql Storage Engine Error 28(5)
  10. Php Random Selector(2)
  11. Activation Code(7)
  12. Instant Replay Code?(0)
  13. Error Connecting To Domain(2)


 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

- Lo-Fi Version Time is now: 22nd August 2008 - 01:46 AM
Powered By IP.Board 2.2.1 © 2008  IPS, Inc.
Licensed to: Xisto Corporation