CODEWITHSUNDEEP
sqloracle-databaseoracle10gsqlplus
Eron Paul Diaz
Eron Paul Diaz

Reputation: 33

oracle - ORA-06502: PL/SQL: numeric or value error: number precision too large

I'm trying to run this in SQL Command Line This is my Program:

set verify off;
set serveroutput on;

prompt
prompt
prompt    ========================================
prompt          E O N  MULTIPLANETARY SYSTEM 
prompt    ========================================
prompt

accept inputstarname prompt "Enter the name of the star: "
accept inputdistance prompt "Enter the light year distance: "
accept inputspectral prompt "Enter the spectral type: "
accept inputmass prompt "Enter the mass: "
accept inputtemp prompt "Enter the temperature(kelvin): "
accept inputage prompt "Enter the age (Giga Year): "
accept inputconplanets prompt "Enter the confirmed planets: "
accept inputunconplanets prompt "Enter the unconfirmed planets: "
accept inputconstellation prompt "Enter the name of the constellation: "

DECLARE
    starname varchar2(20);
    distance number(10,2);
    spectral varchar2(10);
    mass number(2,4);
    temp int;
    age number(3,5);
    conplanets int;
    unconplanets int;
    constellation varchar(25);
BEGIN
    starname:='&inputstarname';
    distance:='&inputdistance';
    spectral:='&inputspectral';
    mass:='&inputmass';
    temp:='&inputtemp';
    age:='&inputage';
    conplanets:='&inputconplanets';
    unconplanets:='&inputunconplanets';
    constellation:='&inputconstellation';
    INSERT INTO eonmultiplanetarysystem (ID, STAR_NAME, DISTANCE_LY, SPECTRAL_TYPE, MASS, TEMPERATURE_K, AGE, CONFIRMED_PLANETS, UNCONFIRMED_PLANETS, CONSTELLATION) VALUES (eonmultiplanetarysystem_seq.nextval, starname, distance, spectral, mass, temp, age, conplanets, unconplanets, constellation);
    commit;
    dbms_output.put_line(chr(20)||'Successfully Added!');
END;
/
prompt
prompt
@c:/CS325/index

My Problem is this even I change my input I get that error:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 15

So this is I'm inputting And this, I was trying to input, I thought the problem is the distance so I decided to change '1' to '1.6. Can you please help me?

Enter the name of the star: Sun
Enter the light year distance: 1.6
Enter the spectral type: G2V
Enter the mass: 1
Enter the temperature(kelvin): 5778
Enter the age (Giga Year): 4.572
Enter the confirmed planets: 8
Enter the unconfirmed planets: 1
Enter the name of the constellation: None

Upvotes: 2

Views: 7901

Answers (2)

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

The Issue is with the NUMBER Datatype with decimal precision.

Inside the NUMBER datatype, the first Number indicates the total number of digits in both sides of decimal point and the second indicates the number of digits after decimal point.

e.g : To hold a value of 34.34434 the datatype should be NUMBER(7,5)

Thanks :)

Upvotes: 1

Valli
Valli

Reputation: 1450

age number(3,5) is throwing the error.

This cannot hold 4.572 To hold 4.572, you have to change the declaration to number(5,3). This means the number will have 2 digits before the period and 3 digit after the period.

Upvotes: 2

Related Questions