Returning Results from External Procedures by Reference

In the documentation for writing external C procedures (e.g. in the Database Development Guide, http://docs.oracle.com/database/121/ADFNS/adfns_externproc.htm#ADFNS726), it is stated that values must be returned from an external procedure by pointer, except for scalar types, such as int. That leaves it open which object to point to.

• If you use a local variable, it will be allocated on the stack, and be gone when the procedure returns.
• A static (or even global) variable is not good programming practice as it is not thread safe. That might mean that it could be overwritten by another session if you use a multi-threaded extproc agent.
• If you use malloc, you have no way of releasing the space.

The documentation for OCIExtProcAllocCallMemory states: “Any memory allocated by the function is freed automatically as soon as control returns to PL/SQL.” If this means that the memory is still allocated when the result is passed back, you should be OK. The only documentation I could find on this, is this old example from 9i http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg11rtn.htm#1003308 . This indeed suggests that this is the way to do it. I can find not sensible alternatives to this, and if you assume that logically there must be a way to do it (otherwise there is a design fault from Oracle’s side, and we know that that never happens 🙂 ), this must be it.