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

From: Anand Rao <panandrao_at_gmail.com>
Date: Thu, 22 Feb 2007 12:06:33 +0530
Message-ID: <d70710370702212236s39c5a52fne135a7764d38bc92@mail.gmail.com>

hmmm ..its rather difficult even with the event setting. the trace file doesn't explicitly show me the column name.

SQL> create table emp (col1 number(3));

Table created.

SQL> ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';

Session altered.

SQL> insert into emp values (1111) ;
insert into emp values (1111)

                        *

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

SQL> ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK OFF'; Session altered.

of course, for varchar2 columns, the error reported isn't ORA-1438 but ORA-01401. now, if you look at the trace file, you really need to search for the keyword "COL1" (the column name i used) to be actually able to find something. there is no clear message that shows that this column violated the rule.

<snip>

ksedmp: internal or fatal error
ORA-01438: value larger than specified precision allows for this column Current SQL statement for this session:
insert into emp values (1111)
----- Call Stack Trace -----

<snip>

In case an insert or update has more than 5,6 columns, it is a cumbersome task to search for each column_name i suppose.

guess the easiest is SQLPlus, where the ' * ' character points to the column/value which violates the rule.

i know this event is the probably as far as we can go...or is there something else?

anand

On 22/02/07, Mladen Gogala < mgogala_at_vmsinfo.com> wrote:
>
> Syed Jaffar Hussain wrote:
> > Mladen,
> >
> > Yes, I do set the event to trace the culprit. The problem is that when
> > I enable this event, Oracle is taking around 6 second to return the
> > error msg. on the sql prompt. And ours is a very high OLTP application
> > where around 500 tps take places.
> > We have request Oracle for an enhancement. Because, when constraints
> > violates, Oracle do gives the constrain name and details, likewise, I
> > would like to have so and so column in the particular table is the
> > culprit.
> >
>
> Syed, it's you who should discover the problem and fix the SQL. It's
> done once, in a sqlplus session
> and then turned off. It's not intended for all users.
>
> --
> Mladen Gogala
> Sr. Oracle DBA
> Video Monitoring Systems
> 1500 Broadway
> New York City, NY 10036
> Phone: (212) 329-5201
> Email: mgogala_at_vmsinfo.com
>
>
>
>

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

Received on Thu Feb 22 2007 - 00:36:33 CST