Re: Implicit Cursor Not Capturing all Records in the Query

Please disregard this post. I've found the problem/solution and it was not actually related to the Implicit Cursor.

Additional testing using different methods turned up the following error:
"ora-20000, oru-10027 buffer overflow, limit of 2000 bytes"

As it turns out the "Put_Line" statement I had in the loop was actually causeing the problem but for some reason the error was not getting return.

Anyway, once I removed the "Put_Line" the loop processed all returned records.

Thank You.

Mr. Duhl wrote:
> I have a procedure (a couple actually) that use an implicit cursor to
> "Loop" through a set of data and then perform additional processing
> based on the references within the cursor.
>
> The problem seems to be that the "Implicit Cursor" is not getting all
> of the records in the associated select query that populates it.
>
> Here is a sample of my code:
> FOR My_Cursor IN (SELECT COLA, COLB, COLC, COLD FROM TABLE1 WHERE...
> UNION
> SELECT COLA, COLB, COLC, COLD FROM TABLE2 WHERE...
> UNION
> SELECT COLA, COLB, COLC, COLD FROM TABLE3 WHERE...)
> LOOP
> USP_CREATENOTIFICATION(My_Cursor.ColA, My_Cursor.ColB,
> My_Cursor.ColD);
> USP_CLEANUPHISTORY(My_Cursor.ColA, My_Cursor.ColB, My_Cursor.ColD);
> END LOOP;
>
> If I pull out my select and run it in a query window it may bring
back
> 1500 records, however my loop only processes 125 or so times.
>
> No error occurs in the processing, the "Implicit Cursor" seems to
only
> contain 125 elements from the select query.
>
> If anyone can tell me whats causing this and how to fix it it would
be
> greatly appreciated.
>
> We are running Oracle 10g Rac in a Windows 2K3 server environment.
>
> Disclaimer: The code above is just a sample loop to give you an idea
> what my problem is. The info refereced is not real nor is it in a
> production environment so please do not ridicule me over "bad code".
> Thank You.
Received on Thu Feb 03 2005 - 09:20:50 CST