Serius Problem with Doa and Oracle 10

[Franco]

Hello,
I'm a user of Doa and PlSqlDeveloper since year 2000.

I recently faced with a problem that it seems to come only if we run the program on an Oracle DB server 10, but it does not happends with Oracle9.1 or older version.
The program is only an infinite loop that perform a couple of query every x seconds ( where x is a parameter we can configure). After some time it crash giving an Access violation at address 0x00000000 by reading address 0x00000.
After several tries we probably localize the crash point in the Oracle.pas

TOracleQuery.InternalExecute;
it seems when calling InitPieces routine or some lines after when calling the following lines:

OCICall(OCIStmtExecute(Session.svchp, stmthp, errhp, ExecIters, 0, nil, nil, ExecMode));

It is strange because the query are not rewritten ( in the sense that are static TOracleDataSet component on the main form of the program) so nothing changes between the first and ( after hours) the last call to close& open query.
and after the access violation the query seems still ok.

It seem it depends on the number of time we ran the query, because as we run them faster we get the error sooner ( but is seems not be a strictly linear correlation)

for what we know the error is not present in old Oracle version ( one of our customer have this program since 2005 and each session run for several days without disconnecting and without any problem)

Can you help me?

n.b.
At present we are using Doa version 4.0.4 ( doa34d6.bpl)

 

[Marco Kalter]

This is a known issue on Oracle10g. You can update to the 4.0.7 version to fix it.

 

[Franco]

Wow! that's a great news!
we download it and we try again.
Thank you

Franco

 

[Franco]

Hi Marco,
I downloaded the version 4.0.7 but I discovered we have had already that one: my collegue have donloaded it some time ago fogetting to change the {$DESCRIPTION} meta variable in Doaxxx.dpk pkg-> I was thinking we are using Doa 4.0.4 but we have version 4.07 and I'm sorry, but we still have the problem.

Could you please give me any other suggestion?
Franco

 

[Marco Kalter]

With version 4.0.7, can you set the global variable NoQueryOptimize to True at runtime before making a connection and try again? The variable is located in the Oracle unit.

 

[Franco]

Thanky you Marco,
we are now doing a try by disconnecting end reconnecting each 100 we open the same query... I don't know if this is a solution, but...it's a try :-/

Unfortunately the crash randomly appears, sometime after minutes, sometimes after several hours...So we have to wait a lot before knowing if it works or not.
Anyway we start on another machine another version with you suggestion.
I will let you know what's happ...
question: this is realted to query optimization; in the sense that it ask to Oracle to not optimize the query?
Franco

 

[Franco]

Hi MArco,
Diconnecting and reconnecting seems to get worsrt the problem.
We are now try to use Oracle query instead of OracleDataSet.

Our problem is that we have two program, in one I can do nything I want because it is a very small application, but the other application is really BIG and I must be careful of any change i do.

for example If I disable optimizazion of all query, I could get serious trouble anywhere else.

what is the sense of the variable NoQueryOptimize ?
what influence it has on default Oracle's "Choose" optimization?
Franco

 

[Marco Kalter]

we are now doing a try by disconnecting end reconnecting each 100 we open the same query... I don't know if this is a solution, but...it's a try :-/

Note that this is not what I asked you to do. You just need to set Oracle.NoQueryOptimize to True in during the initialization part of your application. That's all.

question: this is realted to query optimization; in the sense that it ask to Oracle to not optimize the query?

No, it is related to reusing parsed cursors on the client. It does not affect query optimization on the Server.

 

[Franco]

Hi Marco,
After a week end of test we are sure we have a solution.
Belive it or not... either setting Oracle.NoQueryOptimize to true ( easiest solution) or using Toracle Query in place of TOracleDataSet works fine.

thank you very much for your help.
Franco