Re: Need help returning TABLE from Oracle function
From: Bryan Ax <bax_at_kleinbuendel.com>
Date: Mon, 30 Mar 2009 13:47:44 -0700 (PDT)
Message-ID: <ce3033e6-febf-426a-bcd1-975b694f5f94_at_y13g2000yqn.googlegroups.com>
On Mar 30, 2:30 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Bryan Ax (b..._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).
Date: Mon, 30 Mar 2009 13:47:44 -0700 (PDT)
Message-ID: <ce3033e6-febf-426a-bcd1-975b694f5f94_at_y13g2000yqn.googlegroups.com>
On Mar 30, 2:30 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Bryan Ax (b..._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).
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. Received on Mon Mar 30 2009 - 15:47:44 CDT