Home » SQL & PL/SQL » SQL & PL/SQL » Calling a function within a package which returns a cursor
Calling a function within a package which returns a cursor [message #38903] Fri, 24 May 2002 16:12 Go to next message
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 Go to previous message
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;
/
Previous Topic: Executing an UNIX script Using the OAS servers
Next Topic: very urgent- calling SQLPLUS from PL/sql
Goto Forum:
  


Current Time: Tue Apr 23 18:44:45 CDT 2024