TOracleQuery Bind Variables

[MarkR]

Marco,

When using bind variables with TOracleQuery, how does DOA make the OCI call in reference to the max size of the bind variable?

I have noticed that many of our queries used with TOracleQuery have multiple versions in the SGA SQL Area. According to Oracle the issue is that many third party tools use the max size of the data being passed instead of the max size of the column in the table. Therefore, '1A' and '1AA' using the same bind variable in the same query run at separate times would create 2 versions of that query in the SQL Area.

Is DOA using the max size of the data or of the column? If it is using max size of the data, can something be done to use the max size of the columns to cut down on the number of versions?

Thanks

 

[Marco Kalter]

The max size is used, not the data size. There must be another reason for this issue. Are there any LONG, LONG RAW or other 'special' variable types in this SQL statement? Are you using substitution variables? Are you closing the query in your application?

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

 

[MarkR]

The query is being closed. It is interesting to note that one of the queries in question seems to max out at 60 versions even though the query is closed each time (this query could be run by many different users hundreds of times a day).

The app was designed where there would be one TOracleQuery component in a data module that would be used for many queries. Therefore, all the queries have to be closed after executing.