CODEWITHSUNDEEP
sqloracle-databaseloopsplsql
AJR
AJR

Reputation: 589

How to loop through a cursor that has a ref cursor

I have the following PL/SQl procedure (code below), and this procedure calls a a PL/SQL Function from a Cursor. However the return type of this function is SYS_REFCURSOR

My goal is loop through the cursor and insert the records into a table. Simple.

First here is the FUNCTION:

FUNCTION DOB_DIFF (JOB1 NUMBER, JOB2 NUMBER, CASE_NUMBER NUMBER DEFAULT 0)
RETURN SYS_REFCURSOR IS

XDO_CURSOR SYS_REFCURSOR;

BEGIN
  OPEN XDO_CURSOR FOR
    'SELECT A.X,
          A.Y,
          ''BNFCRY'' AS TYPE,
          B.Z AS DOB
  FROM  TABLE1 B, TABLE A
  WHERE B.X = A.X
  AND   B.Y = A.Y';

 RETURN XDO_CURSOR;

END DOB_DIFF;

Here is the procedure & What I have so far:

create or replace PROCEDURE CALL_FUNC1 (USER_ID IN VARCHAR2, JOB_NR1 IN NUMBER, JOB_NR2 IN NUMBER, CASE_NUM NUMBER DEFAULT 0) AS

CURSOR DOB_DIFF IS
SELECT PKG_PACKAGE1.DOB_DIFF (JOB_NR1, JOB_NR2, CASE_NUM) FROM DUAL;

BEGIN

FOR I IN DOB_DIFF LOOP
    INSERT INTO IPVOWN.PRT_BEN_DOB_DIFF (X, Y, TYPE, DOB) 
    VALUES (I.X, I.Y, I.TYPE, I.DOB);
END LOOP;
COMMIT;

END CALL_FUNC1;

I am getting the following Error, What am i doing wrong ?? It seems like I can't loop through the cursor because the return value of the function is SYS_REFCURSOR which is a list. if that is the case, then how can I loop through a list in a cursor ??

Error(61,2): PLS-00989: Cursor Variable in record, object, or collection is not supported by this release

Thanks in advance.

Upvotes: 0

Views: 3185

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132670

Your code needs to be more like:

create or replace procedure call_func1 
    ( user_id in varchar2
    , job_nr1 in number
    , job_nr2 in number
    , case_num number default 0
    )
 as
    dob_diff sys_refcursor;
    dob_diff_rec ipvown.prt_ben_dob_diff%rowtype;
begin
    dob_diff := pkg_package1.dob_diff (job_nr1, job_nr2, case_num);
    loop
        fetch dob_diff 
            into dob_diff_rec.x
               , dob_diff_rec.y
               , dob_diff_rec.type
               , dob_diff_rec.dob;

        exit when dob_diff%notfound;

        insert into ipvown.prt_ben_dob_diff (x, y, type, dob) 
          values ( dob_diff_rec.x
                 , dob_diff_rec.y
                 , dob_diff_rec.type
                 , dob_diff_rec.dob
                 );
    end loop;

    close dob_diff;

end call_func1;

You can write it all in uppercase if you really prefer :-)

Upvotes: 1

Related Questions