CODEWITHSUNDEEP
sqloracle-databaseplsql
Nitin Tripathi
Nitin Tripathi

Reputation: 1254

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

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 - How do i get which column it is referring to?

Upvotes: 5

Views: 5933

Answers (2)

Peter Nosko
Peter Nosko

Reputation: 1144

If you're doing an INSERT, create a view based on the query or values (SELECT FROM DUAL) you're inserting. Then DIFF the view with the table you're inserting into. You ought to be able to find where the data types do not match in size/precision/scale.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

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

Let's say you have an update like this:

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.

Upvotes: 1

Related Questions