+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 29

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

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

    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


  2. Re: 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

  3. Re: 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

  4. Re: 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

  5. Re: 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

  6. Re: 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

  7. Re: 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

  8. Re: 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

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

    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


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

    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


+ Reply to Thread
Page 1 of 3 1 2 3 LastLast