Re: Error ORA-01000 when using CURSOR in select statement

"Anette Engel" <anette.engel_at_visitscotland.com> wrote in message news:<0tjZ7.11583$4n1.1293217_at_news1.cableinet.net>...
> I quite like to use CURSOR in a select statement, since it would simply a
> few bits and pieces in my java code.
>
> select ...
> cursor ( select ...
> from ...
> where ...
> ) as cursor_name
> from ...
> where ...
>
> I tested the select statement first using Oracle SQL Plus.
> Depending on the size of my result set this works fine or I get the
> following error message:
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01000: maximum open cursors exceeded
>
> Unfortunately I get very quickly the above error. Does anyone know would I
> could do to avoid above error.
> I am wondering if it is at all feasible to use a cursor in a select
> statement as above.
>
> Regards,
>
> Anette

I have encountered this problem when I open a cursor/resultset in a loop (i.e. multiple times) without closing the resultset each time (within the loop).

Perhaps this is the case here too (though I don't know how you would 'close' it here, since you are just using a select statment).

Hope this helps,

Regards,

Rajesh Jayaprakash Received on Mon Jan 07 2002 - 10:46:40 CET