OraFAQ Forum: SQL & PL/SQL » numeric value error

Home » SQL & PL/SQL » SQL & PL/SQL » numeric value error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

numeric value error [message #633035]

Tue, 10 February 2015 06:26

orcle_us
Messages: 21
Registered: February 2015

Junior Member

I am novice to oracle and learning. Please dont mind my silly question. Thanks for your time.

I am trying to display the class average from a st_marks table and i am getting numeric error though I have increased the number data type.

here is my code

SQL> declare
2 cursor c is select aveg from st_marks;
3 r c%rowtype;
4 v_class_avg number(4,3);
5 v_count number(4,2) := 0;
6 v_total_avg number(5,5) := 0;
7 --v_avg number(6,2);
8 begin
9 for r in c
10 loop
11 v_total_avg :=(v_total_avg)+(r.aveg);
12 v_count:=v_count+1;
13 end loop;
14 v_class_avg:=(v_total_avg)/(v_count);
15 dbms_output.put_line('the class average is '||v_class_avg);
16 end;
17 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 11

SQL>

Report message to a moderator

Re: numeric value error [message #633036 is a reply to message #633035]

Tue, 10 February 2015 06:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

What have you learnt from your previous question?

Report message to a moderator

Re: numeric value error [message #633037 is a reply to message #633035]

Tue, 10 February 2015 06:28

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

You are no longer sufficiently "novice" to ignore the forum guidelines. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Report message to a moderator

Re: numeric value error [message #633038 is a reply to message #633035]

Tue, 10 February 2015 06:31

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I am novice to oracle and learning.

No problem but you should FIRST learn how to post and correctly behave in a forum.

Quote:

Lalit : Added code tags, however the formatting is still missing. Please do so yourself in future, read How to use [code] tags

Littlefoot wrote on Fri, 06 February 2015 14:27

...
By the way, as it seems that you didn't read it yet, spend 10 seconds of your time and read how to use [code] tags which will improve readability of your future messages.

BlackSwan wrote on Fri, 06 February 2015 21:10

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

ora-06502: pl/SQL: numeric OR valueERROR [message #633042 is a reply to message #633038]

Tue, 10 February 2015 06:44

orcle_us
Messages: 21
Registered: February 2015

Junior Member

Sorry, when I format the text in sqlformatter, I got the code like below

SQL>DECLARE 2
CURSOR c ISSELECT aveg
FROM st_marks;3 r c%rowtype;4 v_class_avg number(4,3);5 v_count number(4,2) := 0;6 v_total_avg number(5,5) := 0;7 --v_avg number(6,2);
8
BEGIN
9 FOR r IN c 10
loop 11 v_total_avg :=(v_total_avg)+(r.aveg);12V_COUNT:=v_count+1;13
END
loop;14V_CLASS_AVG:=(v_total_avg)/(v_count);15 dbms_output.put_line('the class average is '||v_class_avg);16
END;17 /DECLARE * error at line 1: ora-06502: pl/SQL: numeric
OR
valueERROR: number PRECISION too large ora-06512: at line 11 sql>

thats the reason I posted the copy of code from my SQLPLus.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633043 is a reply to message #633042]

Tue, 10 February 2015 06:45

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

You haven't bothered to read the links, have you?
Goodbye.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633045 is a reply to message #633043]

Tue, 10 February 2015 07:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Quote:

Sorry, when I format the text in sqlformatter, I got the code like below

I can't believe you used SQL Formatter to format your SQL*Plus output. First format your code, and then paste in SQL*Plus interactive window. Then copy your session and paste it here. And the most important thing, use CODE tags to enclose it, VERIFY your post using PREVIEW MESSAGE before submitting.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633046 is a reply to message #633043]

Tue, 10 February 2015 07:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

When people post links you're supposed to click on them and read the contents of the page that comes up. One of the links posted was this one:
How to use [code] tags and make your code easier to read?
So click on it, read what it says and follow the instructions it contains in all future posts.

Lalit has already answered your question - you've made exactly the same mistake as last time.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633050 is a reply to message #633046]

Tue, 10 February 2015 07:53

orcle_us
Messages: 21
Registered: February 2015

Junior Member

Sorry, will rectify it when I post next time. This is the first forum I have registered to, so unaware of the guidelines.
Thank you

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633051 is a reply to message #633050]

Tue, 10 February 2015 08:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

So did you get your answer to your original question? The link I posted would point you to your previous question where you already asked similar issue. Blackswan showed you why the error is encountered.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633054 is a reply to message #633051]

Tue, 10 February 2015 08:59

orcle_us
Messages: 21
Registered: February 2015

Junior Member

No I did not still get the answer, I tried to increase the data type value of v_total_avg and v_count. Still getting the same error.

I dont understand why, because my table consists of only 5 rows and the class average also does not exceed 3 digits. I have given number(5,2) in the declaration section.
I think I am missing something.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633055 is a reply to message #633054]

Tue, 10 February 2015 09:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do NOT tell us!
use COPY & PASTE to SHOW us what exactly you have & how Oracle responded!

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633064 is a reply to message #633055]

Tue, 10 February 2015 09:34

orcle_us
Messages: 21
Registered: February 2015

Junior Member

SQL> select * from st_marks;

SN ST_ID NAME
---------- ---------------------------------------- --------------------
ENGLISH MATHS AVEG
---------- ---------- ----------
1 yr2_15 Luke
78 82 80

2 yr2_18 Mike
67 72 70

3 yr2_19 Daniel
75 80 78

SN ST_ID NAME
---------- ---------------------------------------- --------------------
ENGLISH MATHS AVEG
---------- ---------- ----------
4 yr2_20 sam
92 95 94

5 yr2_21 julie
56 76 66

SQL> DECLARE
2 CURSOR c IS
3 SELECT aveg
4 FROM st_marks;
5 r c%ROWTYPE;
6 v_class_avg NUMBER(4, 3);
7 v_count NUMBER(4, 2) := 0;
8 v_total_avg NUMBER(5, 5) := 0;
9 v_avg NUMBER(6, 2);
10 BEGIN
11 FOR r IN c LOOP
12 v_total_avg := ( v_total_avg ) + ( r.aveg );
13
14 v_count := v_count + 1;
15 END LOOP;
16
17 v_class_avg := ( v_total_avg ) / ( v_count );
18
19 dbms_output.Put_line('the class average is '
20 ||v_class_avg);
21 END;
22 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 12

SQL> desc st_marks;
Name Null? Type
----------------------------------------- -------- ----------------------------

SN NUMBER(3)
ST_ID CHAR(40)
NAME VARCHAR2(20)
ENGLISH NUMBER(3)
MATHS NUMBER(3)
AVEG NUMBER(4)

SQL>

Lalit, I have used the formatting as you described but still getting the same format style as before

[Updated on: Tue, 10 February 2015 09:35]

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633066 is a reply to message #633064]

Tue, 10 February 2015 09:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>v_total_avg NUMBER(5, 5) := 0;

what is the largest value that can be contained in V_TOTAL_AVG variable?

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633068 is a reply to message #633046]

Tue, 10 February 2015 09:41

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

cookiemonster wrote on Tue, 10 February 2015 13:29

When people post links you're supposed to click on them and read the contents of the page that comes up. One of the links posted was this one:
How to use [code] tags and make your code easier to read?
So click on it, read what it says and follow the instructions it contains in all future posts.

Did your eyes magically glaze over this post, or do you think the rules shouldn't apply to you?

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633070 is a reply to message #633068]

Tue, 10 February 2015 09:44

orcle_us
Messages: 21
Registered: February 2015

Junior Member

sorry for troubling you guys

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633076 is a reply to message #633070]

Tue, 10 February 2015 10:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  declare
  2  v_total_avg NUMBER(5, 5) := 0;
  3  begin
  4  v_total_avg  := .9;
  5  v_total_avg  := 1;
  6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 5

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633078 is a reply to message #633070]

Tue, 10 February 2015 10:35

orcle_us
Messages: 21
Registered: February 2015

Junior Member

I figured out where I was wrong. I thought that Oracle NUMBER(p,s) --> p precision and s --> scale
If I declare a variable with type n_value NUMBER(5,3) --> What I understood until now is n_value can have a precision of 5 digits (Before the decimal we can have 5 digits) and 3 --> means after the decimal we can have 3 digits.

That is n_value can have a maximum 99999.999

But now I understood that,
This leave TWO digits to the left of the decimal point; hence 99 limit for NUMBER(5,3)

Many thanks Lalit and blackswan

[Updated on: Tue, 10 February 2015 10:36]

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633079 is a reply to message #633078]

Tue, 10 February 2015 10:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

As I showed it is TRIVIAL to simply & actually test what works & what throws error.
You should always do the same for yourself in the future.

Report message to a moderator

Re: ora-06502: pl/SQL: numeric OR valueERROR [message #633080 is a reply to message #633079]

Tue, 10 February 2015 10:58

orcle_us
Messages: 21
Registered: February 2015

Junior Member

yeah Blackswan.
Thank you

Report message to a moderator

Previous Topic:	How to Create Database Link In Oracle 8.1.7
Next Topic:	Oracles recursive Connect By

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 21:10:27 CDT 2024