TOraclePackage and parses

[ltd]

Hello,
i made some tests with calling stored procs using TOracleQuery or TOraclePackage. In the TOracleQuery i build one Query per Procedure in the package that leads to only one parse run on the server and after that only executes. So no extra load on the oracle side.
When i use the TOraclePackage i made two different aproaches. First i build only one Instance of TOraclePackage and called the different functions from the package. That leads to the fact that every call has one parse and one execute on the server. When i build a TOraclePackage Instance for every used function of the package i get the behaviour as in TOracleQuery. Now my two questions. First: Is there a possibility that an OraclePackage can hold the Cursors of more then one function and avoid the parses on the server.
Second: When there is no possibility to avoid the parses the question is, what is better
(used resources, speed (both on client and on server)) using an OracleQuery or using an OraclePackege for each function.

Regards,
Lars

 

[Marco Kalter]

The TOraclePackage will only use one cursor for subsequent calls, and therefore calls to different program units will lead to extra parse operations.

A parse operation will lead to some overhead, which you have to compare to the actual work that is done inside the program unit. You can use tkprof to find out if this overhead is significant. If it is, you can use multiple TOraclePackage or TOracleQuery instances to prevent the extra parses.

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