Freeing Cursors using TOraclePackage

[techstar]

I am using several TOraclePackage.CallIntegerFunction. I have also set a very large number for OPEN_CURSORS. However, after my application runs for a long time, I get message to the effect that the Package is out of Cursors. I have set the OPEN_CURSORS quite high in Oracle. However, this seems to be related to DOA not freeing up the cursor after the execution of the package, such as forced by using closeAll on a TOracleDataset.

Any suggestions?

[This message has been edited by techstar (edited 11 February 2000).]

 

[Marco Kalter]

If a session runs out of cursors, this is usually caused by one of the following 2 problems:

• You are dynamically creating TOracleQuery, TOracleDataSet and TOraclePackage components, but do not always free them. This will lead to memory and cursor leaks.
  
• You are using an early version of Net8 and are using cursor variables. Net8 has a bug that causes cursor variables to remain open on the server. You can set TOracleSession.Preferences.UseOCI7 to fix it (if you are not using any Net8 specific features like LOB's and Objects).

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

 

[techstar]

Actually, this occurs when using static TOracleDataSet and TOraclePackage components. That is using TOraclePackages "physically" placed on a data module or form.

To avoid this problem, I have to dynamically create and then free TOraclePackages, just as stated in your reply.

In other words, using the examples in the documentation results in the creation but not the freeing of cursors. This eventually causes an oracle error.

Am I missing something? Is this how the component was intended to work or is this a problem?

[This message has been edited by techstar (edited 12 February 2000).]

 

[Marco Kalter]

What about the second possibility, are you using any cursor variables?

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

 

[DaveH]

But what if you ARE using LOBs? IS there any other way to free open cursors other than going yo OCI7. (Wish I'd NEVER HEARD of Oracle 8, personally).

TIA
Dave

 

[techstar]

Marco-

I am not really sure if I understand your question regarding cursor variables. I am just firing package procedures and functions as in the following example:

oraclePackage.callProcedure(oracleFunction, ['TABLE_NAME',upperCase(TableName)]);

 

[Marco Kalter]

This call can hardly cause cursor leaks, unless you dynamically create the package without freeing it. The call itself will simply execute a PL/SQL Block.

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