Problems: TOraclePackage and implicit cursors

[Martin]

I am running into troubles when using TOraclePackage with Oracle 8.1.6.

My application runs permanetly and uses TOraclePackage to call several procedures from an Oracle package.
These procedures perform different update statements on the data base (one statement per procedure). For update statements within procedures Oracle allocates an implicit cursor which does not seem to be ever deallocated when used with TOraclePackage.
In fact it is even worse. The cursor keeps growing.

As a result more and more shared pool is allocated for these cursors. The procedures are called about a 1500 times a day. sys.dbms_shared_pool.sizes shows me that the memory allocated by the associated cursors goes up to 1 Megabyte within about 3 weeks.

All the UPDATE statements in the procedures are followed by a commit. The are performed as expected. I tried with the Optimize property of the TOraclePackage set to true and false - no difference.

Running queries manually with TOracleQuery or calling package procedures on a regular basis using Oracle jobs does not show these problems.

Is there anything known about this problem?
What can I do in order to stop theses cursors from growing?

Thank you for any hint,

Martin

 

[Marco Kalter]

It sounds like a bug, though I can't reproduce it from your description. Can you send me something to reproduce the problem?

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

 

[Martin]

Originally posted by mkalter:
It sounds like a bug, though I can't reproduce it from your description. Can you send me something to reproduce the problem?

The problem seems to be more complicated as I expected. I just wrote a test application that is calling a package procedure with an update statement several 1000 times. I do not see an increase in the shared pool allocation.
In our production system we are talking about four applications running on two different machines. They access the same package. I will do further testing with several applications and machines.

Thank you so far,

Martin

 

[techstar]

Martin-

I have experienced the same problem.

I found that this only occurs when using static TOracleDataSet and TOraclePackage components. That is, when using packages "physically" placed on a data module or form.

To avoid this problem, I dynamically create and then free TOraclePackages.

Perry

 

[Martin]

Originally posted by techstar:
Martin-

I have experienced the same problem.

I found that this only occurs when using static TOracleDataSet and TOraclePackage components. That is, when using packages "physically" placed on a data module or form.

To avoid this problem, I dynamically create and then free TOraclePackages.

Perry

That sounds interessting. I will give it a try.

Thank you so far.

Martin