PLS-00225: subprogram or cursor 'TEST2' reference is out of scope

[kamdarmihir06]

Hi,

I am new to PL-SQL. I am getting the PLS-00225 error while executing the folowing code. Please help on where I am getting wrong...

DECLARE
--create or replace procedure MULTIPLE_RATE_TYPES is
package_id number(5) := 0 ;
package_PlanName varchar2(255) ;
package_RateTypeName varchar2(255) ;
package_TimeZone varchar2(255) ;
package_Rate number(16,6) := 0.0 ;
package_ChargePerEvent number(16,6) := 0.0 ;
timezone_ratetypes varchar2(100) ;

cursor test1 is
select id, plan_name, rate_type_name, time_zone, rate, charge_per_event from umniah_tmp_rate_plan;

cursor test2 is
select distinct RATE_TYPE into timezone_ratetypes from time_zone_rate_types where start_time >= (select start_time from billing_time_interval where name = package_RateTypeName) and end_time <= (select end_time from billing_time_interval where name = package_RateTypeName) ;

BEGIN
--delete from rate_plans ;

open test1 ;
loop
fetch test1 into package_id, package_PlanName, package_RateTypeName, package_TimeZone, package_Rate, package_ChargePerEvent ;
open test2 ;
loop
--fetch test2 into timezone_ratetypes ;

insert into rate_plans(ID,RATE_PLAN, ROAMER_FLAG, RATE_PLAN_ID, ORG_NUMBER_TYPE, DEST_NUMBER_TYPE, ZONE_CODE,
RATE_TYPE, CALL_TYPE, SERVICE_BAND, MIN_CHARGEABLE_UNIT, MIN_CHARGE, PULSE, RATE,
PSTN_MIN_CHARGEABLE_UNIT, PSTN_MIN_CHARGE, PSTN_PULSE, PSTN_RATE, FF_MIN_CHARGEABLE_UNIT,
FF_MIN_CHARGE, FF_PULSE, FF_RATE, FF_PSTN_MIN_CHARGEABLE_UNIT, FF_PSTN_MIN_CHARGE,
FF_PSTN_PULSE, FF_PSTN_RATE, EXTRA_CHARGE, CALL_CATEGORY, PMN )
select rate_plans_seq.nextval, PLAN_NAME, 'N', ID, 'GS','GSM', TIME_ZONE,
test2.timezone_ratetypes, 'V', 'FO', 0.0, 0.0, 1, RATE,
0, 0, 0, 0, 0,
0, 0, 0, 0, 0,
0, 0, 0.0, 2, 'HPMN' from umniah_tmp_rate_plan ;

end loop ;
close test2 ;

end loop ;
close test1 ;
DBMS_OUTPUT.PUT_LINE ('No Exception Caught !') ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No Data Found Exception Caught !') ;
--WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE ('Exception Caught !') ;
END ;
--END MULTIPLE_RATE_TYPES;
/

[PAVB]

at first glance you have commented out your "fetch test2" statement, then when you try to use some test2 cursor data nothing is there.