ORA-1438 and ORA-6502

From: Rodrigo Martins Zed <zed_at_tecnisainfor.com.br>
Date: Sat, 21 Jul 2001 21:33:17 GMT
Message-ID: <4f52b3b.0107120526.1c9a3344@posting.google.com>

Hello all,

I'm facing a strange behavior with a Stored Procedure that raises ORA-6502 or ORA-1438. This is the scenario:

server
Conectiva Linux 5.0 (kernel 2.2.14)
Oracle 8.1.6 for Linux

client
Winsuck 2000 Professional
BDE 5.11
Delphi

I have a table (I'll call it TAB1) with some numeric fields

CREATE TABLE TAB1
(....
 FIELD1 NUMBER(15,5),
 FIELD2 NUMBER(15,5),
 ....);

And the procedure (of course, it is more complicated, this is a short version of it)

1 CREATE OR REPLACE PROCEDURE PROC1
2 AS

3     V_VARIABLE1 NUMBER;
4     V_VARIABLE2 NUMBER;
5  BEGIN
6     V_VARIABLE1 := (SOME CALCULATION);
7     V_VARIABLE2 := (SOME CALCULATION)/NVL(V_VARIABLE1,1);
8     UPDATE TAB1
9     SET FIELD1 = ROUND(NVL(V_VARIABLE1,1),5),
10        FIELD2 = ROUND(NVL(V_VARIABLE2,0),5);

11 END; When I execute it, depending of the values of the variables I get the error on the UPDATE statement (line 8):

ORA-01438 value larger than specified precision allows for this column

I tried to change the procedure to something like this:

1 CREATE OR REPLACE PROCEDURE PROC1
2 AS

3     V_VARIABLE1 NUMBER;
4     V_VARIABLE2 NUMBER;
5     V_DUMMY1 TAB1.FIELD1%TYPE;
6     V_DUMMY2 TAB1.FIELD2%TYPE;
7  BEGIN
8     V_VARIABLE1 := (SOME CALCULATION);
9     V_VARIABLE2 := (SOME CALCULATION)/NVL(V_VARIABLE1,1);

10 V_DUMMY1 := ROUND(NVL(V_VARIABLE1,1),5); 11 V_DUMMY2 := ROUND(NVL(V_VARIABLE2,0),5) 12 UPDATE TAB1
13 SET FIELD1 = V_DUMMY1,
14 FIELD2 = V_DUMMY2;
15 END; The error changed to 6502 on line 10.

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

I have already checked the documentation, but I didn't understand why this is happening.

Any ideas?

Thanks,

Zed. Received on Sat Jul 21 2001 - 16:33:17 CDT