Re: How do I get column name that causing ORA-01438

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 22 Feb 2007 23:18:01 +0800
Message-Id: <200702221518.l1MFIJnD019977@smtp41.singnet.com.sg>

Hmm...
Testing this on 10.2.0.1 on Windows I get :

SQL> create table hkc_t_1 (col1 varchar2(5), col2 varchar2(3), col3 number(2));

Table created.

SQL> insert into hkc_t_1 values ('abcdefg','abc',2); insert into hkc_t_1 values ('abcdefg','abc',2)

                             *

ERROR at line 1:
ORA-12899: value too large for column "HEMANT"."HKC_T_1"."COL1" (actual: 7, maximum: 5)

SQL> insert into hkc_t_1 values ('abc','def',3006); insert into hkc_t_1 values ('abc','def',3006)

                                         *

ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> So if it is a NUMBER column, we get ORA-1438. If it is a VARCHAR2, we get ORA-12899.

Hemant

At 09:28 PM Thursday, Syed Jaffar Hussain wrote:
>Niall,
>
>I am just surprised to see this result.
>
>We are on 10gR2, we don't get such information as you get when the
>error occurs. Interestingly, Oracle support doesn't mention that its
>available with 10gR2.

<snip>

>On 2/22/07, Niall Litchfield
><<mailto:niall.litchfield_at_gmail.com>niall.litchfield_at_gmail.com> wrote:
>They already granted your request.
>
>NIALL @ NL102 >INSERT INTO TEST VALUES ('long');
>INSERT INTO TEST VALUES ('long')
> *
>ERROR at line 1:
>ORA-12899: value too large for column "NIALL"."TEST"."C1" (actual:
>4, maximum: 1)
>
>Oracle 10.2 New Feature.
>
>
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 22 2007 - 09:18:01 CST