PL/SQL numeric or value error: number precision too large

65,805

Try changing shipping number(2,2) to shipping number(4,2)

(2,2) is basically saying that you want 2 digits and 2 of them are after the decimal point. So your range of values is 0 through 0.99. What you really want is "4 digits, 2 of which are after the decimal" which ranges from 0 through 99.99.

Share:
65,805
atrueresistance
Author by

atrueresistance

Updated on July 09, 2022

Comments

  • atrueresistance
    atrueresistance almost 2 years

    I've got a oracle 10g PL/SQL program that I'm trying to get to run,

    Program

    set serveroutput on size 10000;
    DECLARE
         membership varchar2(1) :='Y';
         shipping number(2,2);
         quantity number(3) :=0;
    BEGIN
       if membership = 'Y' then 
          if quantity  <= 3 then
            shipping := 3.00;
          elsif quantity > 3 and quantity <= 6 then
            shipping := 5.00;
          elsif quantity > 6 and quantity <= 10 then
            shipping := 7.00;
          elsif quantity > 10 then
            shipping := 9.00;
       end if;  
       elsif membership = 'N' then
          if quantity  <= 3 then
            shipping := 5.00;
          elsif quantity > 3 and quantity <= 6 then
            shipping := 7.50;
          elsif quantity > 6 and quantity <= 10 then
            shipping := 10.00;
          elsif quantity > 10 then
            shipping := 12.00;
       end if;  
    end if;
    DBMS_OUTPUT.PUT_LINE(shipping);
    END;
    

    The error I keep getting. At first I thought it was just because I was assigning quantity to a number(3), so then I would compare with 003 but that didn't work either.

     Error report:
     ORA-06502: PL/SQL: numeric or value error: number precision too large
     ORA-06512: at line 8
     06502. 00000 -  "PL/SQL: numeric or value error%s"
     *Cause:    
     *Action: