Calling a function within a package which returns a cursor [message #38903] |
Fri, 24 May 2002 16:12 |
Amrendra
Messages: 2 Registered: April 2002
|
Junior Member |
|
|
Hi,
I need to write an execution script to see the return values of a function within a package.
The function accepts 6 parametes (say X1 to x6) and its return type is cursor.
The returning cursor (TempCur) within the program is defined as follows:
CURSOR TEMP IS
SELECT EFFECTEDTABLE_NAME,
EFFECTTYPE_TXT,
EFFECTDESC_TXT,
USER_ID_CD,
AUDIT_DT,
INDIVIDUAL_NBR,
ATTRIBUTE_NAME,
OLD_VALUE,
NEW_VALUE,
AUDIT_TRANSACTION_NBR
FROM TempTab_AuditReport;
type TempCur is ref cursor return TEMP%ROWTYPE;
Please let me know any solution to the above.
Rgds
Amrendra
|
|
|
Re: Calling a function within a package which returns a cursor [message #38904 is a reply to message #38903] |
Fri, 24 May 2002 17:32 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
I've always used ref cursors for that. you may need to swuitch to them. Although this example uses a Proc, a Function works pretty much the same.
CREATE TABLE ABC (A NUMBER);
insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);
CREATE OR REPLACE PACKAGE ref_cur_tst
IS
-- dummy cursor to get %rowtype below
CURSOR c1 IS SELECT a FROM abc;
TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
-- or just use abc%rowtype to match whole table structure.
-- TYPE t_cur IS REF CURSOR RETURN abc%ROWTYPE;
PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
PROCEDURE get_abc (cv_cur IN OUT t_cur)
IS
BEGIN
OPEN cv_cur FOR SELECT a FROM abc;
END get_abc;
END ref_cur_tst;
/
set serveroutput on;
DECLARE
a abc.a%TYPE;
cv_c1 ref_cur_tst.t_cur;
BEGIN
ref_cur_tst.get_abc (cv_c1);
LOOP
FETCH cv_c1 INTO a;
EXIT WHEN cv_c1%NOTFOUND;
DBMS_OUTPUT.put_line (a);
END LOOP;
CLOSE cv_c1;
END;
/
|
|
|