Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Unanswered: ORA01403 and ORA06512 errors in PL/SQL script

    Hi!

    I need your advice.
    I've wrote the following PL/SQL script:


    DECLARE
    counter1 number(6) := 0;
    counter2 number(6) := 0;
    K_Index number(9) := 1;
    K_Ban number(9);
    K_Bill_Seq_No number(3);
    K_Product_Type char(2);
    K_Billing_No char(16);
    K_Feature_Code char(6);
    K_Service_Ftr_Seq_No number(9);
    K_Price_Plan_Code char(9);
    K_Price_Plan_Seq_No NUMBER (9);
    K_Chrg_Amt_Combd NUMBER (9,2):= 0;
    K_Step_tier_uom CHAR(2);
    K_Tier_Level_Combd NUMBER (4);
    K_Rate_Qty_Combd NUMBER (9,2):= 0;
    K_Rate NUMBER(11,4):= 0;
    K_Calculated_Charge NUMBER (9,2):= 0;
    K_Service_Charge NUMBER (9,2):= 0;
    K_Rating_Method CHAR(2);

    cursor REQUEST is
    select BAN, BILL_SEQ_NO, PRODUCT_TYPE, BILLING_NO,
    FEATURE_CODE, SERVICE_FTR_SEQ_NO, PRICE_PLAN_CODE,
    PRICE_PLAN_SEQ_NO, CHRG_AMT_COMBD, STEP_TIER_UOM,
    STEP_TIER_NO_COMBD, RATE_QTY_COMBD, 0,
    nvl(SERVICE_CHRG_AMT,0),RATING_METHOD
    from AU0101;

    BEGIN

    open REQUEST;

    fetch REQUEST into K_Ban,
    K_Bill_Seq_No,
    K_Product_Type,
    K_Billing_No,
    K_Feature_Code,
    K_Service_Ftr_Seq_No,
    K_Price_Plan_Code,
    K_Price_Plan_Seq_No,
    K_Chrg_Amt_Combd,
    K_Step_tier_uom,
    K_Tier_Level_Combd,
    K_Rate_Qty_Combd,
    K_Calculated_Charge,
    K_Service_Charge,
    K_Rating_Method;

    WHILE REQUEST%FOUND LOOP


    IF ( K_Rating_Method = 'C' or K_Rating_Method = 'T' )
    THEN

    counter1 := counter1 + 1;

    select STEP_TIER_NO_COMBD
    into K_Tier_Level_Combd
    from AU0101
    where BAN = K_Ban
    and BILL_SEQ_NO = K_Bill_Seq_No
    and PRODUCT_TYPE = K_Product_Type
    and BILLING_NO = K_Billing_No
    and FEATURE_CODE = K_Feature_Code
    and SERVICE_FTR_SEQ_NO = K_Service_Ftr_Seq_No
    and PRICE_PLAN_CODE = K_Price_Plan_Code
    and PRICE_PLAN_SEQ_NO = K_Price_Plan_Seq_No
    and (PRICE_PLAN_LEVEL_CD ='P'
    or (PRICE_PLAN_LEVEL_CD ='B' and billing_no!='0000000000')
    or (PRICE_PLAN_LEVEL_CD='C' and billing_no!='0000000000'));

    IF (K_Tier_Level_Combd != 0)
    THEN
    select RATE
    into K_Rate
    from RTF_RATE
    where PRICE_PLAN = K_Price_Plan_Code
    and FEATURE_CODE = K_Feature_Code
    and TIER_LEVEL_CODE = K_Tier_Level_Combd
    and expiration_date is NULL;

    END IF;


    IF ((ABS( K_Rate_Qty_Combd * K_Rate + K_Service_Charge ) -
    K_Chrg_Amt_Combd ) > 0.02 )
    THEN

    counter2 := counter2 + 1 ;
    insert
    into WRONG_RERATE_AU_RECS
    ( WR_BAN,
    WR_BILL_SEQ_NO,
    WR_PRODUCT_TYPE,
    WR_BILLING_NO,
    WR_FEATURE_CODE,
    WR_SERVICE_FTR_SEQ_NO,
    WR_PRICE_PLAN_CODE,
    WR_PRICE_PLAN_SEQ_NO,
    WR_CHRG_AMT_COMBD,
    WR_STEP_TIER_UOM,
    WR_STEP_TIER_NO_COMBD,
    WR_RATE_QTY_COMBD,
    WR_CALCULATED_CHARGE,
    WR_SRV_CHRG_AMOUNT,
    WR_RATING_METHOD)
    values ( K_Ban,
    K_Bill_Seq_No,
    K_Product_Type,
    K_Billing_No,
    K_Feature_Code,
    K_Service_Ftr_Seq_No,
    K_Price_Plan_Code,
    K_Price_Plan_Seq_No,
    K_Chrg_Amt_Combd,
    K_step_tier_uom,
    K_Tier_Level_Combd,
    K_Rate_Qty_Combd,
    K_Rate_Qty_Combd * K_Rate,
    K_Service_Charge,
    K_Rating_Method );
    END IF;

    END IF;

    fetch REQUEST into K_Ban,
    K_Bill_Seq_No,
    K_Product_Type,
    K_Billing_No,
    K_Feature_Code,
    K_Service_Ftr_Seq_No,
    K_Price_Plan_Code,
    K_Price_Plan_Seq_No,
    K_Chrg_Amt_Combd,
    K_step_tier_uom,
    K_Tier_Level_Combd,
    K_Rate_Qty_Combd,
    K_Calculated_Charge,
    K_Service_Charge,
    K_Rating_Method;

    END LOOP;

    close REQUEST;
    END;
    /




    When I run it the following message appears:

    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 56


    SQL>


    Tell me please, what's the problem and how to fix it?
    Is this problem on the DBA level?

    Many Thanks in advance,
    John.
    Last edited by _John Smith; 02-19-03 at 11:16.
    John Smith

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA01403 and ORA06512 errors in PL/SQL script

    One of your SELECT...INTO statements is returning no rows - probably this one:

    select STEP_TIER_NO_COMBD
    into K_Tier_Level_Combd
    from AU0101
    where BAN = K_Ban
    and BILL_SEQ_NO = K_Bill_Seq_No
    and PRODUCT_TYPE = K_Product_Type
    and BILLING_NO = K_Billing_No
    and FEATURE_CODE = K_Feature_Code
    and SERVICE_FTR_SEQ_NO = K_Service_Ftr_Seq_No
    and PRICE_PLAN_CODE = K_Price_Plan_Code
    and PRICE_PLAN_SEQ_NO = K_Price_Plan_Seq_No
    and (PRICE_PLAN_LEVEL_CD ='P'
    or (PRICE_PLAN_LEVEL_CD ='B' and billing_no!='0000000000')
    or (PRICE_PLAN_LEVEL_CD='C' and billing_no!='0000000000'));

    You can determine precisely which one like this:

    SQL> L 51 60

    That will print lines 51 to 60

    If it is valid for no rows to be returned, and you want to continue (perhaps assuming that K_Tier_Level_Combd = 0) then you can catch and ignore the NO_DATA_FOUND exception like this:

    Code:
    ...
    BEGIN
      SELECT ... INTO...;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        K_Tier_Level_Combd := 0;
    END;
    ...

  3. #3
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    Re: ORA01403 and ORA06512 errors in PL/SQL script

    Originally posted by andrewst
    One of your SELECT...INTO statements is returning no rows - probably this one:

    select STEP_TIER_NO_COMBD
    into K_Tier_Level_Combd
    from AU0101
    where BAN = K_Ban
    and BILL_SEQ_NO = K_Bill_Seq_No
    and PRODUCT_TYPE = K_Product_Type
    and BILLING_NO = K_Billing_No
    and FEATURE_CODE = K_Feature_Code
    and SERVICE_FTR_SEQ_NO = K_Service_Ftr_Seq_No
    and PRICE_PLAN_CODE = K_Price_Plan_Code
    and PRICE_PLAN_SEQ_NO = K_Price_Plan_Seq_No
    and (PRICE_PLAN_LEVEL_CD ='P'
    or (PRICE_PLAN_LEVEL_CD ='B' and billing_no!='0000000000')
    or (PRICE_PLAN_LEVEL_CD='C' and billing_no!='0000000000'));

    You can determine precisely which one like this:

    SQL> L 51 60

    That will print lines 51 to 60

    If it is valid for no rows to be returned, and you want to continue (perhaps assuming that K_Tier_Level_Combd = 0) then you can catch and ignore the NO_DATA_FOUND exception like this:

    Code:
    ...
    BEGIN
      SELECT ... INTO...;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        K_Tier_Level_Combd := 0;
    END;
    ...
    Hi Andrew

    Didn't know list command accepts parameters. (keep learning every day...

    remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  4. #4
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38

    Thumbs up

    Thank you. I didn't post in this problem, but it helped me greatly.
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

  5. #5
    Join Date
    Aug 2007
    Posts
    4
    Good one. However I have included a detail demonstration in the post Oracle in World: Troubleshoot ORA-06512: at line
    You might feel interest to check it.

Posting Permissions

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