Callouts

When to Callout

You should consider utilizing callouts in the following circumstances:

• When it would be impractical or impossible to code the algorithm you require in SQL.

• When the performance gains of a compiled language (e.g., C) outweigh the extproc callout overhead

• When you wish to leverage existing 3GL code

When to Callback

You should consider utilizing callbacks in the following circumstances:

• When you need data that was not passed as an argument to the call out.

• When it isn't practical to pass the data to the call out (e.g., the number and size of the parameters exceeds that which is allowed or performs well).

Consider making a single callout which does multiple callbacks rather than multiple callouts (e.g. instead of a factorial callout which takes a single number and computes a the factorial for it, consider making a callout which takes a VARRAY and repeatedly calls back to get next number to compute the factorial for. You always do performance testing to see at what at point the multi-call back approach out-performs the multi-callout approach

Callouts and LOB

• It may be to your advantage to code your callout so that it is independent of LOB type (BFILE/BLOB).

• The PL/SQL layer of your cartridge can "open" your BFILE so that no BFILE-specific logic is required in your callout (other than error recovery from OCILob calls that do not operate on BFILEs).

• With the advent of temporary LOBs in Orace8i release 8.1.5, you need to be aware of the deep copy that can occur when assignments and calls are done with temporary LOBs. Use "NOCOPY" (by REF) on BLOB parameters as appropriate.

Saving and Passing State

External procedures under Oracle 8.0 have a "state-less" model. All Statement handles opened during the invocation of an external procedure are closed implicitly at the end of the call.

In Oracle 8.1, we allow "state" (OCI Statement handles etc. and associated state in the DBMS) to be saved and used across invocations of external procedures in a session.B y default cartridges are still stateless, however, OCIMemory services and OCIContext services can be used with OCI_DURATION_SESSION or other appropriate duration to save state. Statement handles created in one external procedure invocation can get re-used in another. The Data Cartridge developer needs to explicitly free these handles. It is recommended that this is done as soon as the statement handle is no longer needed. All state maintained for the statement in the OCI handles and in the DBMS would get freed as a result. This should help in improving the scalability of the Data Cartridge.