| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How do I get column name that causing ORA-01438
DWH1) oerr ora 12899
12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value // which is too wide for the width of the destination column. // The name of the column is given, along with the actual width // of the value, and the maximum allowed width of the column. // Note that widths are reported in characters if character length // semantics are in effect for the column, otherwise widths are // reported in bytes. // *Action: Examine the SQL statement for correctness. Check source // and destination column data types. // Either make the destination column wider, or use a subset // of the source column (i.e. use substring).You have mail in /usr/spool/mail/oracle
// *Cause: When inserting or updating records, a numeric value was entered // that exceeded the precision defined for the column. // *Action: Enter a value that complies with the numeric column's precision, // or use the MODIFY option with the ALTER TABLE command to expand // the precision.
they are different errors 1438 is for numeric columns and 12899 seems to be varchar2 columns
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.
> I have just done a simulation test on 10R2 with 10.2.0.3
> <http://10.2.0.3> patch, still I don't get the same explanation as you
> have shown.
>
> Can you elabrate more on this.
>
> Jaffar
>
> On 2/22/07, *Niall Litchfield* <niall.litchfield_at_gmail.com
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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 <mailto: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
> <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
>
>
>
>
> --
> 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."
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 22 2007 - 10:06:10 CST
 |
 |