CODEWITHSUNDEEP
sqloracle-databasestored-proceduressys-refcursor
Arips Jen
Arips Jen

Reputation: 1

Can one procedure have 2 refcursor?

Stored procedure for selecting multiple rows of data

create or replace PROCEDURE tranferdata
(REFCUR OUT SYS_REFCURSOR,REFCUR2 OUT SYS_REFCURSOR)
AS
BEGIN

OPEN REFCUR FOR SELECT  distinct  b.GUARDIAN_ID, c.name,d.PHONE_NUMBER
from
student a,student_guardian b,guardian c,GUARDIAN_CONTACT d
where b.STUDENT_ID=a.student_id
and b.GUARDIAN_ID =c.GUARDIAN_ID
and c.GUARDIAN_ID=d.GUARDIAN_ID;

OPEN REFCUR2 FOR SELECT b.GUARDIAN_ID, c.name,d.PHONE_NUMBER
from
student a,student_guardian b,guardian c,GUARDIAN_CONTACT d, testarif e
where b.STUDENT_ID=a.student_id
and b.GUARDIAN_ID =c.GUARDIAN_ID
and c.GUARDIAN_ID=d.GUARDIAN_ID
and b.GUARDIAN_ID !=e.vv_guardian_id;


END;

Anonymous block to call the procedure

SET SERVEROUTPUT ON;
DECLARE
P_CURSOR SYS_REFCURSOR;
P_CURSOR2 SYS_REFCURSOR;
P_id VARCHAR2(100);
P_name VARCHAR2(100);
P_phone_no VARCHAR2(100);

PP_id VARCHAR2(100);
PP_name VARCHAR2(100);
PP_phone_no VARCHAR2(100);


BEGIN

DBMS_OUTPUT.PUT_LINE ('ATAS');

tranferdata(REFCUR,REFCUR2 => P_CURSOR,P_CURSOR2);



DBMS_OUTPUT.PUT_LINE ('BAWAH');

LOOP

FETCH P_CURSOR INTO P_id,P_name,P_phone_no;
insert into testarif(vv_guardian_id,vv_name,vv_phone) values (P_id, P_name, P_phone_no);
EXIT WHEN P_CURSOR%NOTFOUND;


END LOOP;

CLOSE P_CURSOR;


LOOP

FETCH P_CURSOR2 INTO PP_id,PP_name,PP_phone_no;

insert into testarif2(vvv_guardian_id,vvv_name,vvv_phone) values (PP_id, PP_name, PP_phone_no);
EXIT WHEN P_CURSOR2%NOTFOUND;


END LOOP;

CLOSE P_CURSOR2;

END;
/

The error is

ORA-06550: line 17, column 13:
PLS-00201: identifier 'REFCUR' must be declared

ORA-06550: line 17, column 1:

I cannot find which one is the error...

Is the code above valid for using 2 refcursors in one procedure?

Upvotes: 0

Views: 122

Answers (1)

AlexSmet
AlexSmet

Reputation: 2161

You use incorrect syntax. Just change tranferdata(REFCUR,REFCUR2 => P_CURSOR,P_CURSOR2); to tranferdata(REFCUR => P_CURSOR, REFCUR2 => P_CURSOR2);

Upvotes: 1

Related Questions