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

that's a relief :)

thanks Niall.

cheers
anand

On 22/02/07, Niall Litchfield <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.
>
>
>
>
>
>
> On 2/22/07, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com> wrote:
> >
> > Anand,
> >
> > We have request to Oracle for an enhancement.
> >
> > The trace file although doesn't show the column name, but, you have
> > enough information to find out. No matter, this is little bit time
> > consuming.
> >
> > Jaffar
> >
> > On 2/22/07, Anand Rao <panandrao_at_gmail.com > wrote:
> > >
> > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
> > --
> > Best Regards,
> > Syed Jaffar Hussain
> > Oracle ACE
> > 8i,9i & 10g OCP DBA
> >
> > http://jaffardba.blogspot.com/
> > http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
> > ----------------------------------------------------------------------------------
> >
> > "Winners don't do different things. They do things differently."
> >
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info

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