Re: How do I get column name that causing ORA-01438
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- To: mgogala@xxxxxxxxxxx
- Date: Thu, 22 Feb 2007 12:06:33 +0530
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@xxxxxxxxxxx> 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@xxxxxxxxxxx
Other related posts:
