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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

On 14 Sep 2005 13:31:31 -0700, goyald@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

Thanks for the reply. I should have explained my question further.

Error message is
*More text here * exception occurred in
ORA9IDynamicSql:penCursor(StmtExecute)
ORA-01438: value larger than specified precision allows for this
column

As you will notice, this is a dynamic SQL that gets built somewhere in
program, (from input XML sent by external system), program than tries
to insert/update database. It does not happen often but only sometimes
- when sender system sends data with more precision to our system.

If I can get capture offending SQL than I can try comparing data
fields. I am looking for some suggestions to find this column from
database logs/trace files etc.
thanks

Thanks for the reply. I should have explained my question further.

Error message is
*More text here * exception occurred in
ORA9IDynamicSql:penCursor(StmtExecute)
ORA-01438: value larger than specified precision allows for this
column

As you will notice, this is a dynamic SQL that gets built somewhere in
program, (from input XML sent by external system), program than tries
to insert/update database. It does not happen often but only sometimes
- when sender system sends data with more precision to our system.

If I can get capture offending SQL than I can try comparing data
fields. I am looking for some suggestions to find this column from
database logs/trace files etc.
thanks