Hello
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?


Regards

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

goyald@gmail.com schrieb:
> 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
>

You may be interesting in tracing the session with event 10046 at level
4 or 12. In that case you will find in trace file the error 01438 as
well as SQL and inserted values. You can enable tracing for certain user
via logon trigger ( http://www.evdbt.com/trclvl12.ddl ).

Best regards

Maxim

Thanks