Re: ORA-1438 and ORA-6502

Hi Stephen,

At the specific time the exception is raised, I checked the value of the variables. V_VARIABLE2 (in second stored Procedure - line 7) has a value between 0.1 and 0.2 with several decimal digits...

Maybe I'm wrong, I'll double check it, but I think my calculations NEVER get large numbers. This high precision is just to prevent errors like these...

Thanks,

Zed.

Stephen Bell <stephen.bell_at_cgi.ca> wrote in message news:<3B4DB1FD.C96A870E_at_cgi.ca>...
> Hi Rodrigo,
>
> At the risk of stating the obvious, what are some of the numbers your
> calculations are producing? The precision of your table is 15, scale is
> 5..so, 10 digits to the left of the decimal point right?
>
> My suggestion is to double check some sample values in your calculations..
>
> do you have denominators less than 1 etc....
>
> Hope this is helpful,
>
> Steve
>
>
> Rodrigo Martins Zed wrote:
>
> > 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:34:33 CDT