PLS-00225: subprogram or cursor 'TEST2' reference is out of scope
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Jan 2008
    Posts
    1

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

    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;
    /

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    at first glance you have commented out your "fetch test2" statement, then when you try to use some test2 cursor data nothing is there.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width