Re: Need help returning TABLE from Oracle function

From: Malcolm Dew-Jones <>
Date: 30 Mar 2009 13:30:25 -0700
Message-ID: <49d12be1$>

Bryan Ax ( wrote:
: I have the following Oracle function from a vendor:

: FUNCTION test_warn3


: ERRORS ab_curs.error_list;
: startdate DATE := to_date('01-JAN-2009','DD-MON-YYYY');
: enddate DATE := to_date('31-JAN-2009','DD-MON-YYYY');
: histdate DATE := TRUNC(SYSDATE);


: update_package1.update_query(errors,
: startdate,
: enddate,
: histdate);

: RETURN('Test Warn 3 Complete');

: END; -- END FUNCTION test_warn3
: /

: However, what I really want to do is return ERRORS, NOT just the
: message 'Test Warn 3 Complete', and then take the results of errors
: and do some logic in C#. However, I haven't been able to get this to
: work - I've tried having an OUT param that's a REF CURSOR and doing:

: OPEN errorCursor FOR SELECT * FROM ERRORS, but I get an error when I
: try to run that because the compiler doesn't recognize ERRORS as a
: table in the database - it isn't. It's just a TABLE return type.

: Any help appreciated,

Define an OUT parameter of type 'ab_curs.error_list'.

If you examine the DESCRIBE-tion of update_package1 you should find that update_query has an OUT parameter of that type.

Your higher level routine will define a local variable of that type (just like the example above) and it will call test_warn3 with that as a parameter (just like the example call to update_package1.update_query shown above). Received on Mon Mar 30 2009 - 15:30:25 CDT

Original text of this message