Re: ORA-01401: how to get more details ?

From: Yong Huang <yong321_at_yahoo.com>
Date: 31 Jul 2004 21:03:26 -0700
Message-ID: <b3cb12d6.0407312003.45ea13de@posting.google.com>

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<410a040f$1_at_olaf.komtel.net>...
> Hi,
>
> sometimes I get
>
> ORA-01401: inserted value too large for column
>
> and this typically happens with tables that have quite a number of columns
> (>=40). What I have to do is go through all the values that should have been
> inserted and compare the size of each value with the column definition to
> detect which one is the evil-doer.
>
> Now I wonder is there a way to obtain more information, namely the name
> (or at least number) of the column that the conflict occurred on ? It would
> really ease work a lot.

Hi, André,

Before 10g, I can think of only one way and it's simple. Just replace all commas in your insert statement with comma plus carriage returns, so each value is on its own line. SQL*Plus is capable of telling you the line that causes the parse error, like this:

SQL> insert into t values (

  2  'x',
  3  'xx',
  4  'xxx');

'xx',
*
ERROR at line 3:
ORA-01401: inserted value too large for column

My table t has three columns, all being varchar2(1). It looks like if more than one column would trigger ORA-1401, SQL*Plus points at the first column.

To add carriage returns after all commas, a vi command s/,/,^V^M/g should do it (If you use Windows vim, ^V may need to be replaced with something else depending on the setting in _vimrc).

Just for the record, in 10g, ORA-1401 is replaced with a more helpful error:

ORA-12899: value too large for column <schema>.<table>.<column> (actual: <actual number of chars>, maximum: <max allowed number>)

Yong Huang Received on Sat Jul 31 2004 - 23:03:26 CDT