Re: Need help returning TABLE from Oracle function
Date: 30 Mar 2009 13:30:25 -0700
Message-ID: <49d12be1$1_at_news.victoria.tc.ca>
Bryan Ax (bax_at_kleinbuendel.com) wrote:
: I have the following Oracle function from a vendor:
: CREATE OR REPLACE
: FUNCTION test_warn3
: RETURN VARCHAR2 IS
: 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);
: BEGIN
: 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