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

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Oracle SQL query with cast function failed with following error message.

SQL> select cast(deptno as numeric(1)) from dept;

select cast(deptno as numeric(1)) from dept

            *

ERROR at line 1:

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

Solution Description:

The problem here is the value which you are passing to the cast function is larger than the precision mentioned. Here in above example, the deptno column in the dept table is 2 digit value and I have mentioned only numeric(1) as the precision.

SQL> desc dept

 Name                                      Null?    Type

 ----------------------------------------- -------- -----------

 DEPTNO                                    NOT NULL NUMBER(2)

 DNAME                                              VARCHAR2(14)

 LOC                                                VARCHAR2(13)

Suppose one of you query with cast function is working since long time and one fine day is giving such error (ora-01438) then you have to verify your data. There must be a data mismatch with data error something like that.

One more example:

SQL> select cast (100 as number (3,1)) from dual;

select cast (100 as number (3,1)) from dual

             *

ERROR at line 1:

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

The precision should be 4 and out of 4 one will be used for decimal.

SQL>  select cast (100 as number (4,1)) from dual;

CAST(100ASNUMBER(4,1))

----------------------

                   100

_____________________________________________________________________________________________________________________

Website Stats