Describing and Fetching/Copying/Manipulating any REF CURSOR

Ever needed to describe, fetch, copy or manipulate a REF CURSOR in Oracle, where you don't know the structure of the REF CURSOR in advance? My friend, Oracle test coach and PL/SQL expert, Finn Ellebaek Nielsen has spent a whole lot of time developing utility code implemented in a combination of PL/SQL, Java and C, that you can use as is in order to:

• Describe any REF CURSOR: Get an XML document containing a description of the cursor's columns, including name, datatype, precision, scale and declaration code for a variable usable with this column.

• Fetch any REF CURSOR to memory: Fetch the rows of the cursor into an ANYDATA instance. Optionally manipulate data for each row after it has been fetched through custom PL/SQL code.

• Copy any REF CURSOR: Fetch the rows of the cursor and copy these to either memory or a table and get a new REF CURSOR opened for this copy. Optionally manipulate data for each row after it has been fetched through custom PL/SQL code. Optionally call custom PL/SQL code either before the new REF CURSOR is opened or after.

The utility code works with any weak and strong REF CURSOR and also any CURSOR expression (fetch/copy/manipulate doesn't support LONG and LONG RAW columns). It's supported with any edition of Oracle Database 10g Release 1 and newer and Finn also describes how you can make it work for Oracle Database 9i Release 2. Lastly, Finn discovered that contrary to Oracle’s documentation it’s possible to pass a REF CURSOR directly to a C external procedure.

Read all about it on Finn's blog here:

http://ellebaek.wordpress.com/2011/03/11/describing-a-ref-cursor-in-oracle-10g-using-plsql-java-and-c/

http://ellebaek.wordpress.com/2011/03/29/copying-transforming-a-ref-cursor-in-oracle-10g/

Cheers, Steven