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,

Bryan

Bryan Ax (bax (AT) kleinbuendel (DOT) 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).

On Mar 30, 2:30*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
Unfortunately, the procedure I'm calling is obfuscated, so I can't see
the code. I do know that error_list is an in/out parameter. I'll try
setting up an OUT param to see if I can get that to work. Just because
I want to know how - how could I take ERRORS and return it in a
SYS_REFCURSOR? I'm very new to Oracle, and having trouble finding
advanced samples.