Re: How to find Column name - ORA-01438: value larger than specified precision allows

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 14 Sep 2005 22:14:12 +0100
Message-ID: <mo3hi19gue3ch824892d8qooe8d9trou6d@4ax.com>

On 14 Sep 2005 13:31:31 -0700, goyald_at_gmail.com wrote:

>I have a very large table with many columns that can cause this. Input
>is coming through external application integration and we do not have
>control on code that inserts data in our table. We can however change
>precision in the table.
>
>Problem is that I can not find a way to get name of offending column.
>Are their any suggestions?

 What's the data loaded with? There's a way to highlight the column, at least within the SQL statement; here's an example via SQL*Plus.

SQL> create table t (c1 number(4), c2 number(4));

Table created.

SQL> insert into t values (9999, 99999); insert into t values (9999, 99999)

                            *

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

SQL> insert into t values (99999, 9999); insert into t values (99999, 9999)

                      *

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

 Note that there is a "*" on the value that broke the datatype constraint.

 At the OCI level this comes from the OCI_ATTR_PARSE_ERROR_OFFSET statement handle attribute, so it's available to any OCI program. Perl's DBD::Oracle will pick this up, too.

 If it's acceptable to have to run it past a human to find the problem then this could work. Can't think of a more direct method that doesn't involve you basically re-inventing the validation rules in code somewhere.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Wed Sep 14 2005 - 16:14:12 CDT