-
Title
Getting ORA-01438 when trying to optimize a SQL Statement -
Description
When I try to optimize a SQL statement in SQL Optimizer I get the errors below. This does not happen with every SQL statement, only some.
ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allowed for this column
Invalid SQL statement -
Cause
Data being entered into one of the explain plan table's column is too large for that column's specified precision.
-
Resolution
Select a different explain plan to use in SQL Optimizer. Use one that has the column precisions that will handle the values being inserted from the optimization of your SQL statement. You can change the explain plan being used in SQL Optimizer by selecting Options | General, clicking the “Use the following plan table” option, and entering the schema and table name for the plan table you want to use.
-
Additional Information
Another place you can get a different plan table from is in Toad for Oracle. If you can run an explain plan for the "problem" SQL statement in Toad, then use the plan table from Toad. You can get this information by selecting View | Toad Options | Oracle – General in Toad and looking at the Explain Plan value.