error when calling reference cursor

Question

FN_UPD is calling CURSOR_SELECT,but i keep getting error.

"Error(11,12): PLS-00201: identifier 'V_RC' must be declared"
when i try to add the DECLARE , it will throw other error.

create or replace procedure FN_UPD
( COL_A in varchar2 ,V_INPUT_TYP in varchar2)

IS
v_rc sys_refcursor;
begin
v_rc  := CURSOR_SELECT(COL_A, V_INPUT_TYP);


for f in v_rc 
loop
if (f.rownum = 1) then 
        update TB_TARGET SUMM set ENEX_ID = F.SNS_KEY, LAST__DT = F.VEH_ENT_EXT_ON
    where V_NUM = COL_A and INPUT_TYP = V_INPUT_TYP;
else
    update TB_TARGET SUMM set ENEX2_ID = f.sns_key, LAST2__DT = f.veh_ent_ext_on
     WHERE V_NUM = COL_A and INPUT_TYP = V_INPUT_TYP;
end if;
end loop;
commit;
--close C1;

EXCEPTION
WHEN OTHERS THEN

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END; /

Encounter other error

"Error(10,12): PLS-00221: 'V_RC' is not a procedure or is undefined"

if i update to the following

create or replace procedure FN_UPD
 ( COL_A in varchar2 ,V_INPUT_TYP in varchar2) is
v_rc sys_refcursor;
begin
v_rc := CURSOR_SELECT(COL_A, V_INPUT_TYP);

Answer 1

You don't need loop as you don't change the where clause:

Try with this:

CREATE OR REPLACE
PROCEDURE fn_upd(
          col_a       IN VARCHAR2 ,
          v_input_typ IN VARCHAR2)
IS
     v_rc sys_refcursor;
     l_id NUMBER;
     l_ent_on DATE;
BEGIN
     v_rc := cursor_select(col_a, v_input_typ);
     dbms_output.put_line ('Records to be processed: '||col_a);
     fetch v_rc INTO l_id, l_ent_on;
          UPDATE tb_target summ
          SET  enex_id   = l_id,
               last__dt  = l_ent_on
          WHERE v_num    = col_a
           AND input_typ = v_input_typ;

COMMIT;
exception WHEN others THEN
     raise_application_error(-20001,'An error was encountered - '||sqlcode||' -ERROR- '||sqlerrm);
END;

Answer 2

You need to open the cursor for a specific query.

What records should your v_rc contain? more specifically, on what values are you trying to run the for loop?

use

create or replace procedure FN_UPD
 ( COL_A in varchar2 ,V_INPUT_TYP in varchar2) is
v_rc sys_refcursor;
begin
OPEN V_RC FOR SELECT X,Y,Z FROM YOUR_TABLE_FOR_LOOP;

for f in v_rc
.
.
.
.
.
CLOSE V_RC