ORA-01438:大于指定精度的值允许该列-如何获得它所指向的列? [英] ORA-01438: value larger than specified precision allows for this column - How do i get which column it is referring to?

问题描述

我们是否有任何标准的oracle错误表或任何可识别我输入的值大于指定精度的值的表?

Do we have any standard oracle error tables or anything to identify which column in a table i have entered a value larger than specified precision.

ORA-01438: value larger than specified precision allows for this column-如何获得它所指的是哪一列?

ORA-01438: value larger than specified precision allows for this column - How do i get which column it is referring to?

推荐答案

我认为您必须编写一个过程，然后尝试逐一更新该列.

I think you have to write a procedure and try to update the column one by one.

假设您有这样的更新:

UPDATE TABLE_1 a SET (COL_1, COL_2, COL_3) = 
   (SELECT COL_1, COL_2, COL_3 FROM TABLE_2 b WHERE a.COL_ID = b.COL_ID);

然后您可以通过以下方法遍历所有列:

Then you can loop over all columns by this:

DECLARE
    CURSOR TabColumns IS 
    SELECT column_id, column_name, data_precision, data_scale
    FROM USER_TAB_COLUMNS 
    WHERE table_name = 'TABLE_2'
       AND column_name <> 'COL_ID'
    ORDER BY 1;

BEGIN
    FOR aCol IN TabColumns LOOP
    BEGIN 
        sqlstr := 
            'UPDATE TABLE_1 a SET '||aCol.column_name ||' = '
                ||' (SELECT '||aCol.column_name ||
                ||' FROM TABLE_2 b '
                ||' WHERE  a.COL_ID = b.COL_ID)'

            EXECUTE IMMEDIATE sqlstr USING CalcDate, CalcDate;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE ( 'Error at column '|| aCol.column_id||CHR(9)|| aCol.column_name||CHR(9)||SQLERRM);
            DBMS_OUTPUT.PUT_LINE ( sqlstr );
    END;
    END LOOP;

END;    

效率不高，您应该会看到错误.

Not very efficient you should see the error.

这篇关于ORA-01438:大于指定精度的值允许该列-如何获得它所指向的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！