ORACLE/D3 Maximum open cursors exceeded problem 
Author Message
 ORACLE/D3 Maximum open cursors exceeded problem

Hi,
     My delphi 3 app. is a direct translation of a PL/SQL package that
runs on ORACLE 7.3.3. It dynamically creates and frees several TQuery
objects at runtime then crashes with an ORACLE message : 'ORA-01000
Maximum open cursors exceeded'

     This is strange because the PL/SQL package - using CURSORs , too -
does not fail in this way.

     My TQueries are Free'd so I am not certain how to ensure that the
cursors are truly closed down beyond freeing the object itself.

     Can anyone advise what may be happening here and how to get around
it ??

Thanks in advance, Kev Grindley.

Sent via Deja.com http://www.*-*-*.com/
Share what you know. Learn what you don't.



Wed, 18 Jun 1902 08:00:00 GMT  
 ORACLE/D3 Maximum open cursors exceeded problem

Quote:

>Hi,
>     My Delphi 3 app. is a direct translation of a PL/SQL package that
>runs on ORACLE 7.3.3. It dynamically creates and frees several TQuery
>objects at runtime then crashes with an ORACLE message : 'ORA-01000
>Maximum open cursors exceeded'

>     This is strange because the PL/SQL package - using CURSORs , too -
>does not fail in this way.

>     My TQueries are Free'd so I am not certain how to ensure that the
>cursors are truly closed down beyond freeing the object itself.

>     Can anyone advise what may be happening here and how to get around
>it ??

>Thanks in advance, Kev Grindley.

This problem normally occurs if people call the same query many times
without closing it.  Each call creates a new cursor instance in
oracle.  If you then free the TQuery instance, only the last cursor
created by the query will be closed, leaving many open.  I know you
say you haven't done this, but you might find you have without
realising.  There is a view in Oracle - v$open_cursor - which will
show you open cursors.  Log in at SYS level, and look at the SQL text
for each of the open cursors. I suspect you will see the same query
text repeated many times, showing this to be the case.

Failing that, you can increase the maximum number of cursors allowed
to say 250 or 500. e.g:

open_cursors = 250

to init$ORACLE_SID.ora then restart the Oracle database.

I don't understand why you don't just call the PL/SQL using a
TStoredProc.  If it was written well in PL/SQL, it will run much
faster than any translation you could ever attempt in Delphi using
TQuerys.

Hope this helps.

Paul

^^ remove 'as' anti spam prefix to reply



Wed, 18 Jun 1902 08:00:00 GMT  
 ORACLE/D3 Maximum open cursors exceeded problem
Paul,
      Thanks for the response. I eventually solved this by getting rid
of some 'wasteful' code. No excuses really ! Basically I was creating
TQuery objects for the same query in a loop, Closing them then Freeing
them, assuming that they would immediately disappear from ORACLEs
memory space. So, thinking that the CURSORS_SET_FOR_TIME parameter was
forcing my Cursor queries to be cached, and because my previous
appraoch was sloppy anyway, I just reused the TQuery instances wherever
I could instead of creating a brand new one on the fly.

Unfortunately, in our installation I don't have the privileges required
to view the V$ objects (or much else really !) : I have to rely on our
overworked DBA !

By the way, my initial approach *was* to used a TStoredProc but this
failed because it returned apx 3000 bytes of data and Delphi 3 for some
reason can only cope with <=255 bytes when you try and access
TStoredProc paramaters 'AsString'. The best info we have up to now is
to move to Delphi 5 which solves this problem but if you (or anyone out
there) can shed any light on this as well I'd be obliged !! We're using
Delphi 3 running against ORacle 7.3.3.

Cheers,
Kev.



Quote:

> >Hi,
> >     My Delphi 3 app. is a direct translation of a PL/SQL package
that
> >runs on ORACLE 7.3.3. It dynamically creates and frees several TQuery
> >objects at runtime then crashes with an ORACLE message : 'ORA-01000
> >Maximum open cursors exceeded'

> >     This is strange because the PL/SQL package - using CURSORs ,
too -
> >does not fail in this way.

> >     My TQueries are Free'd so I am not certain how to ensure that
the
> >cursors are truly closed down beyond freeing the object itself.

> >     Can anyone advise what may be happening here and how to get
around
> >it ??

> >Thanks in advance, Kev Grindley.

> This problem normally occurs if people call the same query many times
> without closing it.  Each call creates a new cursor instance in
> oracle.  If you then free the TQuery instance, only the last cursor
> created by the query will be closed, leaving many open.  I know you
> say you haven't done this, but you might find you have without
> realising.  There is a view in Oracle - v$open_cursor - which will
> show you open cursors.  Log in at SYS level, and look at the SQL text
> for each of the open cursors. I suspect you will see the same query
> text repeated many times, showing this to be the case.

> Failing that, you can increase the maximum number of cursors allowed
> to say 250 or 500. e.g:

> open_cursors = 250

> to init$ORACLE_SID.ora then restart the Oracle database.

> I don't understand why you don't just call the PL/SQL using a
> TStoredProc.  If it was written well in PL/SQL, it will run much
> faster than any translation you could ever attempt in Delphi using
> TQuerys.

> Hope this helps.

> Paul

> ^^ remove 'as' anti spam prefix to reply

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.


Wed, 18 Jun 1902 08:00:00 GMT  
 ORACLE/D3 Maximum open cursors exceeded problem

Quote:

>Paul,
>      Thanks for the response. I eventually solved this by getting rid
>of some 'wasteful' code. No excuses really ! Basically I was creating
>TQuery objects for the same query in a loop, Closing them then Freeing
>them, assuming that they would immediately disappear from ORACLEs
>memory space. So, thinking that the CURSORS_SET_FOR_TIME parameter was
>forcing my Cursor queries to be cached, and because my previous
>appraoch was sloppy anyway, I just reused the TQuery instances wherever
>I could instead of creating a brand new one on the fly.

>Unfortunately, in our installation I don't have the privileges required
>to view the V$ objects (or much else really !) : I have to rely on our
>overworked DBA !

>By the way, my initial approach *was* to used a TStoredProc but this
>failed because it returned apx 3000 bytes of data and Delphi 3 for some
>reason can only cope with <=255 bytes when you try and access
>TStoredProc paramaters 'AsString'. The best info we have up to now is
>to move to Delphi 5 which solves this problem but if you (or anyone out
>there) can shed any light on this as well I'd be obliged !! We're using
>Delphi 3 running against ORacle 7.3.3.

>Cheers,
>Kev.

Have you tried a newer version of the BDE or compiling your code
allowing huge strings?

Paul

^^ remove 'as' anti spam prefix to reply

- Show quoted text -

Quote:




>> >Hi,
>> >     My Delphi 3 app. is a direct translation of a PL/SQL package
>that
>> >runs on ORACLE 7.3.3. It dynamically creates and frees several TQuery
>> >objects at runtime then crashes with an ORACLE message : 'ORA-01000
>> >Maximum open cursors exceeded'

>> >     This is strange because the PL/SQL package - using CURSORs ,
>too -
>> >does not fail in this way.

>> >     My TQueries are Free'd so I am not certain how to ensure that
>the
>> >cursors are truly closed down beyond freeing the object itself.

>> >     Can anyone advise what may be happening here and how to get
>around
>> >it ??

>> >Thanks in advance, Kev Grindley.

>> This problem normally occurs if people call the same query many times
>> without closing it.  Each call creates a new cursor instance in
>> oracle.  If you then free the TQuery instance, only the last cursor
>> created by the query will be closed, leaving many open.  I know you
>> say you haven't done this, but you might find you have without
>> realising.  There is a view in Oracle - v$open_cursor - which will
>> show you open cursors.  Log in at SYS level, and look at the SQL text
>> for each of the open cursors. I suspect you will see the same query
>> text repeated many times, showing this to be the case.

>> Failing that, you can increase the maximum number of cursors allowed
>> to say 250 or 500. e.g:

>> open_cursors = 250

>> to init$ORACLE_SID.ora then restart the Oracle database.

>> I don't understand why you don't just call the PL/SQL using a
>> TStoredProc.  If it was written well in PL/SQL, it will run much
>> faster than any translation you could ever attempt in Delphi using
>> TQuerys.

>> Hope this helps.

>> Paul

>> ^^ remove 'as' anti spam prefix to reply

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. ORA-01000 maximum open cursors exceeded - Delphi - Oracle

2. Maximum Open Cursors Exceeded

3. Maximum open cursors exceeded

4. problems with packages access in Oracle 7.3 and D3

5. Maximum record size with ORACLE and 32 bit BDE

6. 'Text exceeds memo capacity' problem

7. D3.Pdx D2-->D3 partial migration problem

8. D3 C/S + MSSQL6.5 error creating cursor handle

9. D3 & BDE: Too many files open erro

10. DB App - Too many open cursors

11. Opening Access 95 Table Without Login Prompt (D3)

12. Too Many Open Cursors

 

 
Powered by phpBB® Forum Software