Ora 01438 ошибка - Не ошибается лишь тот, кто ничего не делает!

We get sometimes the following error from our partner’s database:

<i>ORA-01438: value larger than specified precision allows for this column</i>

The full response looks like the following:

<?xml version="1.0" encoding="windows-1251"?>
<response>
  <status_code></status_code>
  <error_text>ORA-01438: value larger than specified precision allows for this column ORA-06512: at &quot;UMAIN.PAY_NET_V1_PKG&quot;, line 176 ORA-06512: at line 1</error_text>
  <pay_id>5592988</pay_id>
  <time_stamp></time_stamp>
</response>

What can be the cause for this error?

Kiquenet

14.4k35 gold badges147 silver badges242 bronze badges

asked Oct 8, 2008 at 4:30

The number you are trying to store is too big for the field. Look at the SCALE and PRECISION. The difference between the two is the number of digits ahead of the decimal place that you can store.

select cast (10 as number(1,2)) from dual
             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

select cast (15.33 as number(3,2)) from dual
             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Anything at the lower end gets truncated (silently)

select cast (5.33333333 as number(3,2)) from dual;
CAST(5.33333333ASNUMBER(3,2))
-----------------------------
                         5.33

answered Apr 29, 2010 at 23:44

Gary MyersGary Myers

34.9k3 gold badges49 silver badges74 bronze badges

The error seems not to be one of a character field, but more of a numeric one. (If it were a string problem like WW mentioned, you’d get a ‘value too big’ or something similar.) Probably you are using more digits than are allowed, e.g. 1,000000001 in a column defined as number (10,2).

Look at the source code as WW mentioned to figure out what column may be causing the problem. Then check the data if possible that is being used there.

answered Oct 8, 2008 at 4:56

ThorstenThorsten

12.8k17 gold badges60 silver badges79 bronze badges

Further to previous answers, you should note that a column defined as VARCHARS(10) will store 10 bytes, not 10 characters unless you define it as VARCHAR2(10 CHAR)

[The OP’s question seems to be number related… this is just in case anyone else has a similar issue]

answered Oct 8, 2008 at 6:49

cagcowboycagcowboy

29.8k11 gold badges69 silver badges93 bronze badges

This indicates you are trying to put something too big into a column. For example, you have a VARCHAR2(10) column and you are putting in 11 characters. Same thing with number.

This is happening at line 176 of package UMAIN. You would need to go and have a look at that to see what it is up to. Hopefully you can look it up in your source control (or from user_source). Later versions of Oracle report this error better, telling you which column and what value.

Dave Jarvis

30.3k40 gold badges178 silver badges313 bronze badges

answered Oct 8, 2008 at 4:35

WW.WW.

23.7k13 gold badges94 silver badges121 bronze badges

FYI:
Numeric field size violations will give
ORA-01438: value larger than specified precision allowed for this column

VARCHAR2 field length violations will give
ORA-12899: value too large for column…

Oracle makes a distinction between the data types of the column based on the error code and message.

answered Jun 27, 2016 at 15:14

One issue I’ve had, and it was horribly tricky, was that the OCI call to describe a column attributes behaves diffrently depending on Oracle versions. Describing a simple NUMBER column created without any prec or scale returns differenlty on 9i, 1Og and 11g

answered Oct 8, 2008 at 5:12

Robert GouldRobert Gould

68.5k61 gold badges186 silver badges271 bronze badges

From http://ora-01438.ora-code.com/ (the definitive resource outside of Oracle Support):

ORA-01438: value larger than specified precision allowed for this column
Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
Action: Enter a value that complies with the numeric column’s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

http://ora-06512.ora-code.com/:

ORA-06512: at stringline string
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.

answered Oct 8, 2008 at 14:00

warrenwarren

32.3k21 gold badges85 silver badges122 bronze badges

It might be a good practice to define variables like below:

v_departmentid departments.department_id%TYPE;

NOT like below:

v_departmentid NUMBER(4)

answered Apr 30, 2010 at 2:00

gokhantgokhant

961 silver badge3 bronze badges

It is also possible to get this error code, if you are using PHP and bound integer variables (oci_bind_by_name with SQLT_INT).
If you try to insert NULL via the bound variable, then you get this error or sometimes the value 2 is inserted (which is even more worse).

To solve this issue, you must bind the variable as string (SQLT_CHR) with fixed length instead. Before inserting NULL must be converted into an empty string (equals to NULL in Oracle) and all other integer values must be converted into its string representation.

answered Jan 13, 2022 at 11:13

Источник

Following is my code, I dont understand what I’m doing wrong. Any help will be greatly appreciated

CREATE OR REPLACE
PROCEDURE COMP_LATE_FEE(LATE_APT_FINE IN NUMBER, LATE_GRG_FINE IN NUMBER)
AS
DIFF NUMBER;
TYPE MBCUR IS REF CURSOR RETURN MONTHLY_BILL%ROWTYPE;
MONBILL MBCUR;
MBREC MONTHLY_BILL%ROWTYPE;
BEGIN
--DIFF := FLOOR(SYSDATE - (TRUNC(SYSDATE,'MM')));
--DBMS_OUTPUT.PUT_LINE(DIFF);

OPEN MONBILL FOR
    -- checking the status of all last month's bills
    SELECT * FROM MONTHLY_BILL
    WHERE STATUS = 'PENDING' AND SYSDATE > ED_DT;
FETCH MONBILL INTO MBREC;
    -- adding the late fee amount for any bills that are past the due date
    -- due date = last day of the month
    DIFF := FLOOR(ABS(MBREC.ED_DT - (TRUNC(SYSDATE,'MM'))));
    UPDATE MONTHLY_BILL
    SET LATE_FEE = DIFF * LATE_APT_FINE
    WHERE BILL_NUM = MBREC.BILL_NUM;
    -- if a garage is rented by the resident then the respective additional fee is included
    IF (MBREC.GARAGE_RENT != 0) THEN
        UPDATE MONTHLY_BILL
        SET LATE_FEE = LATE_FEE + DIFF * LATE_GRG_FINE
        WHERE BILL_NUM = MBREC.BILL_NUM;
    END IF;

    COMMIT;
CLOSE MONBILL;
 END;
/

The procedure compiled without any err. But I get the following err when i call the proc

BEGIN
COMP_LATE_FEE(70,20);
END;
/

Error report:

ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "LALLURI.COMP_LATE_FEE", line 19
ORA-06512: at line 2
01438. 00000 -  "value larger than specified precision allowed for this column"
*Cause:    When inserting or updating records, a numeric value was entered
           that exceeded the precision defined for the column.
*Action:   Enter a value that complies with the numeric column's precision,
           or use the MODIFY option with the ALTER TABLE command to expand
           the precision.

Источник

ORA-01438

ORA-01438: значение больше указанной точности для этой колонки

Причина:

Когда вы вставляли или изменяли записи, была введена величина превышающая ширину колонки.

Действие:

Введите меньшую величину, или используйте опцию MODIFY для увеличения ширины колонки.

Источник

September 17, 2020

Hi,

I got ” ORA-01438: value larger than specified precision allowed for this column” error in Oracle database.

ORA-01438: value larger than specified precision allowed for this column

Details of error are as follows.

SQL> create table test_table (id number(3,3));

Table created.

SQL> insert into test_table values(3.333);
insert into test_table values(3.333)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>

The ORA-01438 error is related with the value larger than column’s width is inserted in the column.

Number datatype columns are used in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(4,2)”, you need to use 4 maximum width of the data and 2 for decimal part like (123.45)

ORA-01438

To solve The ORA-01438 error, use the correct value as follows.

SQL> insert into test_table values(0.3);

1 row created.


SQL> insert into test_table values(0.2);

1 row created.

SQL> insert into test_table values(0.1);

1 row created.

SQL>

Second example is as follows.

SQL> CREATE TABLE test
2 ( id number(6) not null,
3 name varchar2(40) not null
4 );

Table created.

SQL> insert into test values(1453634,'Mehmet Salih');
insert into test values(1453634,'Mehmet Salih')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

To solve this error use only 6 digits for the first column as follows.

SQL> insert into test values(145363,'Mehmet Salih');

1 row created.

SQL>

Or another example is as follows.

SQL> create table test (id number(6,3));

Table created.

SQL> insert into test values(12.3456);

1 row created.

SQL> insert into test values(123.4567);

1 row created.

SQL> insert into test values(1234.567);
insert into test values(1234.567)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(123.45678);

1 row created.

SQL> insert into test values(123.456789);

1 row created.

SQL> insert into test values(123.4567890);

1 row created.

SQL> insert into test values(123.4567890123);

1 row created.

SQL>

Do you want to learn Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

14,474 views last month, 3 views today

Источник

You may also want to
see this article about the ORA-12899 which is returned if a value larger than
column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what
is defined for the NUMBER datatype column. Number datatype columns are defined
in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”,
it would mean that maximum width of the data could be 5 digits, out of which 2
will be decimal part (for example 123.45). Following are some examples that
explain this concept further.

— In the following example,
the inserted value should only have 2 digits, and both digits should be in
the decimal part of the number

SQL>
create table test (sal number(2,2));

Table
created.

SQL>
insert into test values(2.3333);

insert
into test values(2.3333)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2.3);

insert
into test values(2.3)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2);

insert
into test values(2)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(.2);

1
row created.

SQL>

— In the following example,
the inserted value should only have 2 digits for precision part only as no
decimal could be inserted in this column, although INSERT statement would
succeed if decimal value is mentioned.

SQL>
create table test (sal number(2));

Table
created.

SQL>
insert into test values(2.1);

1
row created.

SQL>
insert into test values(2.11);

1
row created.

SQL>
insert into test values(2.111);

1
row created.

SQL>
insert into test values(2.1110000);

1
row created.

SQL>
insert into test values(22.1110000);

1
row created.

SQL>
insert into test values(223.1110000);

insert
into test values(223.1110000)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
select * from test;

SAL

———-

— In the following example,
maximum 6 digits could be inserted in the column out of which 4 have to be
decimal part. As soon as we try to insert 3 digits in precision part,
ORA-01438 would be returned because 4 places have to be reserved for the decimal
part.

SQL>
create table test (sal number(6,4));

Table
created.

SQL>
insert into test values(25.65743);

1
row created.

SQL>
insert into test values(2534333.65743);

insert
into test values(2534333.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(253433.65743);

insert
into test values(253433.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2534.65743);

insert
into test values(2534.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2.65743);

1
row created.

SQL>
insert into test values(22.65743);

1
row created.

SQL>
insert into test values(223.65743);

insert
into test values(223.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(243.5);

insert
into test values(243.5)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
select * from test;

SAL

———-

25.6574

2.6574

22.6574

Источник