Re: ref cursor question

Hi Ryan,

The problem is, that you can only fetch row from the database. In case of ref_cursor you have to fetch complete row, so you have to have variable where to put all columns.
Previously in such a cases, I've used method similar to what David has described. The biggest advantage of this approach is that you will have "strong" refcusor instead of weak refcursor: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1409, so Oracle can check if your query matches your specification during parsing.
If you don't want to use this approach, you can write small java stored procedure, that can fetch from ref cursor and use this procedure from pl/sql.
Luckily in 11g you can convert ref cursor into dbms_sql cursor id and write a procedure that can fetch rows from any cursor using dbms_sql functions.

For unit tests I've used java stored procedure approach, but I don't have procedure code at my hand right now to show you as example. If you still need the java procedure to fetch from ref cursor, I can send it to you this evening.

Just my 2 cents.

On Nov 28, 2007 1:02 AM, <ryan_gaffuri_at_comcast.net> wrote:
> I am returning REF Cursors to an external interface. I want code to test each of these procedures. The problem is that these queries often have 25+ columns in them from several tables.

-- 
Best regards,
 Edgar Chupit
 callto://edgar.chupit
--
http://www.freelists.org/webpage/oracle-l