Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: number precision too large (10g)
ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307264] Tue, 18 March 2008 05:07 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi there.

I am getting a very strange error message when I try to call a stored procedure.

I am calling the following stored procedure:

PROCEDURE MyProc(my_field_name IN fields.field_name%TYPE)


and getting the following message:

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


The fields.field_name type is of the type NUMBER (looks like no decimal places are used).

I am only passing in whole numbers (i.e. 1) but I can't figure out what's wrong. All the searching I have done on the net seems to suggest it's an issue with the precision however when the table was created it was created without a set of (p,s) values.

Does Oracle add in a default precision I need to change?
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307269 is a reply to message #307264] Tue, 18 March 2008 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't figure out what's wrong

And so we also don't unless you post the code, table description and the call.

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307281 is a reply to message #307264] Tue, 18 March 2008 05:44 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Sorry about that.

The table is as follows

CREATE TABLE fields
(
field_name     NUMBER NOT NULL,
log_dtm        DATE,
field_name_2   VARCHAR2(100)
);


From what I can tell the procedure never actually runs but here is the code for the stored procedure.

PROCEDURE MyProc(my_field_name IN fields.field_name%TYPE) IS
BEGIN 
		
INSERT INTO fields
(
field_name,
log_dtm,
field_name_2
)
VALUES
(
my_field_name,
SYSDATE,
'TEST'
);
		
COMMIT;
		
END MyProc;


The code I call to use the procedure is

My_Package.MyProc(1);


If I just run the insert statement directly it runs fine.

Is this information any more useful?

[Updated on: Tue, 18 March 2008 05:50]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307284 is a reply to message #307264] Tue, 18 March 2008 05:52 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Embarassed Have to read better Embarassed

EDIT: Or the OP has edited himself something Smile

[Updated on: Tue, 18 March 2008 05:54]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307289 is a reply to message #307284] Tue, 18 March 2008 06:03 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
MarcS wrote on Tue, 18 March 2008 05:52
Embarassed Have to read better Embarassed

EDIT: Or the OP has edited himself something Smile


My fault again. I posted it and noticed that I had made a few mistakes when changing the names of my variables, columns and tables.

But that's what the code looks like and it compiles fine and only throws an error when the procedure is called.

I was testing and added an insert into a temporary log after every line of code and noticed it calls the stored procedure but does not even get to the first line inside the begin.
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307298 is a reply to message #307264] Tue, 18 March 2008 06:21 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
The code you provided works out (did you test that?) , so the problem lies with stuff that you have left out to simplify the case.
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307343 is a reply to message #307298] Tue, 18 March 2008 09:17 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
That is my code.

The thing I have noticed is that it sometimes works but more often then not falls over.

If I comment out the procedure call the code works all the time.

The only difference between the code I posted is the names of the tables and columns are different.
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307344 is a reply to message #307343] Tue, 18 March 2008 09:22 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
amardilo wrote on Tue, 18 March 2008 15:17
That is my code.

The thing I have noticed is that it sometimes works but more often then not falls over.

If I comment out the procedure call the code works all the time.

The only difference between the code I posted is the names of the tables and columns are different.


Well, what about giving us a case where it doesn't work?

I'm not that good in guessing Smile
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307347 is a reply to message #307343] Tue, 18 March 2008 09:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why don't you make the procedure call inside a begin exception block and use the infamous when others and output the parameter you are passing in to the procedure. Hopefully you should be able to identify the problematic number.

Regards

Raj

Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307636 is a reply to message #307347] Wed, 19 March 2008 05:50 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Thanks. I tried that and it falls over randomly.

The numbers are between 1 - 10 (currently the only values possible to pass into the procedure). I tried hard coding all 10 numbers to see if any of them are the cause but the issue is still very random (i.e. sometimes they go through but 8/10 times they fall over).

If I comment out the procedure call the code runs fine every time.

The only problem is that the procedure needs to be called by several other procedures so I don't want to replicate the code to run the same insert statement again and again.

[Updated on: Wed, 19 March 2008 05:51]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307645 is a reply to message #307636] Wed, 19 March 2008 06:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Thanks. I tried that and it falls over randomly.

Why don't you post the actual code and the error ?

Regards

Raj
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307695 is a reply to message #307645] Wed, 19 March 2008 09:04 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
S.Rajaram wrote on Wed, 19 March 2008 06:07
Quote:
Thanks. I tried that and it falls over randomly.

Why don't you post the actual code and the error ?

Regards

Raj


I can't post the actual code, but the only difference between the code posted and the actual code is the name of the fields and columns.

But I tried just running the following code (run inside TOAD 7.5 SQL Window):

DECLARE

BEGIN

My_Package.MyProc(1);

END;


I ran it the first time and got the following message:

Quote:

ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "MY_DB.My_Package", line 5
ORA-06512: at line 5


Line 5 seems to be:

My_Package.MyProc(1);


I then tried running it again and it worked fine. I then ran it another 6 times and it was OK.
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #307701 is a reply to message #307695] Wed, 19 March 2008 10:18 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

I can't post the actual code, but the only difference between the code posted and the actual code is the name of the fields and columns.


Post like what I did
create or replace package test_pkg
is
procedure test_proc1(a number);
end;
/

 create or replace package body test_pkg
    is
    procedure test_proc1(a number)
    is
    b varchar2(1);
    begin
       b := 'junk';
       dbms_output.put_line(a);
    end;
 end;

SQL> begin
  2  test_pkg.test_proc1(1234);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "RSUBRAMANIAN.TEST_PKG", line 7
ORA-06512: at line 2

SQL> col text format A80

SQL> select text, type from user_source where name = 'TEST_PKG' and line = 7 and type = 'PACKAGE BODY';

TEXT
--------------------------------------------------------------------------------
TYPE
------------
       b := 'junk';
PACKAGE BODY


I am not asking you to post the entire script. I am asking you to post the problematic line (in this case line 5) only where it is failing and more information about the variables used in that line.
Regards

Raj
Previous Topic: SQL HELP
Next Topic: Update date
Goto Forum:
  


Current Time: Sun Dec 04 14:29:58 CST 2016

Total time taken to generate the page: 0.12165 seconds