W2000 / NT.dll / Delphi 6

[Mark Gorst]

We're suffering a problem when opening a database using Delphi 6 on Win 2000 (Sp1)
where we don't have enough "open_cursors".

This is only manifesting itself on the clients machine (and there DB people keep reseting our resources, so we keep getting the problem).

Anyone else had this and got a better way of at least catching the problem. We don't get the normal Oracle error - we get an error in module NT.dll that we haven't be able to trap.

Anyone help me manage this situation more gracefully ?

Mark

 

[Eric^2]

Originally posted by Mark Gorst:
We're suffering a problem when opening a database using Delphi 6 on Win 2000 (Sp1)
where we don't have enough "open_cursors".

This is only manifesting itself on the clients machine (and there DB people keep reseting our resources, so we keep getting the problem).

Anyone else had this and got a better way of at least catching the problem. We don't get the normal Oracle error - we get an error in module NT.dll that we haven't be able to trap.

Anyone help me manage this situation more gracefully ?

Mark

I've got all my machines running on Win2K with D6E and DOA. I haven't once had an "out of cursors" error. I am on Service Pack 2 on my development laptop, and on Service Pack 1 on the rest of the machines. A few questions:
1) What is the number of cursors specified inside your init.ora file?
2) How many cursors are you knowingly trying to create?
3) How many data controls (design and runtime) are you using?

Eric

 

[Mark Gorst]

We can't go to SP2 yet, because our customer has specified that SP1 is their current environment in which everything is tested (all was fine until SP1 came along, but in doing so they also changed the Oracle installation - apart from reducing number of cursors we're not sure what their (outsourced) DBA has done - and currently have not been able to find out). This only occurs on the customers machine, not on ours - SP1 was our first thought, but it doesn't appear to be culprit.

1. We've raised that to 500 which is now more than adequate and has fixed the problem in our development environment which is a standalone machine.

2. Generally we open around 40 tables and around 3 queries at any given time. To the best of my knowledge that would mean that 50 cursors would be fine, but we got this problem with 100 cursors and so raised the "open_cursors" to 500.

3. It's a small number - I think it's 3 DBGrids.

Until we get the DBA to increase the number of cursors we're not sure if that's what's causing the problem. It may be completely unreleated
to DOA/Oracle...but the main difference we have is the Oracle configurations so that's where we are currently looking.

Thanks

Mark

 

[Marco Kalter]

I'm not sure how one can get an "open_cursors" error inside a Net8 DLL. Perhaps you can describe the sequence of events in some more detail?

To prevent this error (ORA-01000, right?), you should increase the OPEN_CURSORS initialization parameter, so that the database instance can service your application.

------------------
Marco Kalter
Allround Automations

 

[Mark Gorst]

Yep - we can fix the open cursors (when we get access to a DBA who can and will change the value back : thank you corporate culture).

I was wondering if anyone had experiences the NT.DLL problem. Sounds to me like we might be getting the problem from something else.

If "open_cursors" fixes it I'll let you know, if not then it might be something completely different...watch this space

Mark

 

[techstar]

Yes, we have run into this problem on numerous occasions using DOA and Java (SQLJ and JDBC). There are clearly some Oracle problems and memory leaks in the various verions. Plus, SQL*NET tries to keep cursors cached to improve performance. Here are some things you need to do.

1- Get the open_cursors parameter in the init file set as high as possible. Keep in mind, however, that this is not the total fix as some might lead you to believe.

2- In DOA, use .closeAll instead of close to close your data queries.

3- Right after establishing your Oracle session(s), issue the following query:
begin
dbms_session.set_close_cached_open_cursors( true );
end;

This is the pl/sql equivalent of: alter session set close_cached_open_cursors=true

4- Upon leaving any Oracle Package, Function or procedure issue the following pl/sql statement to overcome a memory leak in some versions Oracle & SQL*NET:

dbms_session.free_unused_user_memory;

Generally, I do this inside PL/SQL functions and procedures that I use. I also issue it when required from DOA.

[This message has been edited by techstar (edited 28 September 2001).]

 

[techstar]

Yes, we have run into this problem on numerous occasions. Here are some things you need to do.

1- Get the open_cursors parameter in the init file set as high as possible.

2- Use .closeAll instead of close to close your data queries.

3- Right after establishing your Oracle session(s), issue the following statement:

dbms_session.set_close_cached_open_cursors( true );

This is the pl/sql equivalent of: alter session set close_cached_open_cursors=true

4- Upon leaving any Oracle Package, Function or procedure issue the following pl/sql statement to overcome a memory leak in some versions Oracle & SQL*NET:

dbms_session.free_unused_user_memory;