Just because you are using cursors, doesn't mean you have to use
single record cursor loops. Cursors can be bulk collected into a
collection that is then processed using "forall". I may disagree with
Sybrand concerning the "efficency" of opening and fetching a cursor
multiple times over a collection memory reference, but it is easier.
Collection passing between PL/SQL routines only works if the "other
transaction" is executed in the same call or (when properly coded) by
the same session in a sesequent SQL call. The package cursor method
works with connection pooling apps where the "other transaction" is
coming in a later SQL call and probably from a different session.
If you're not familiar with collection methods, check out the
"Application Developer's Guide - Object-Relational Features" manual.
Steven Feuerstein's "Oracle PL/SQL for DBAs" has the best write up on
collections with code examples.
Received on Tue Feb 13 2007 - 11:28:33 CST
