Using Cursor Expressions. When the query is attempted in the editor, I receive the following:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
When I copy and paste the query into SqlPlus, the query executes without errors.
Reproduce using statement like:
SELECT
D.*, CURSOR(SELECT * FROM Emp WHERE DeptNo = D.DeptNo) Emps
FROM
Dept D
If the query does run, scrolling through the cursor values in the grid could lead to this error. Trying to scoll back to a previously viewed value doesn't work (scrolls back and shows a blank field) and closing the viewer window and reopening it doesn't help (doesn't bring up any data).
RESOLUTION 1:
If having problems getting any data to be returned in the grid, try tetting the OCI_ARRAY_BUFFER_SIZE to a low number like 25 would at least delay the error until the user scrolls down far enough. Remember to restart Toad after changing that option.
RESOLUTION 2:
If data is returned but the error is encountered during the review of specific data, this is a limited by how cursors can be handled in general, and the query will need to be re-run and review will need to be picked up where it was before erroring out (i.e. cursor limit is 500, and record 500 is hit and cursor limit is reached, note the record that was being reviewed, re-run the query, and start reviewing data again at that record).
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center