10 Replies Latest reply: Dec 19, 2013 9:27 AM by Mark D Powell RSS

    Precision Error


      Hi friends,


      I have a table with more than 50 columns, when im trying to insert a records of nearly 50,000 through insert stmt, im getting an precision error like value specified is more than the column. Since in the table of 50 columns im not sure of to find in which columns does this precision problem occurring. Kindly help me that how i can find the corresponding column in the table as im executing my code in toad.






        • 1. Re: Precision Error

          What is the result of: select * from v$version; ? This is important to know, you should always post your database version along with your question.

          • 2. Re: Precision Error
            Mark D Powell

            Mini, besides posting the data Hoek suggested I believe you need to post the full Oracle error stack returned to you.  If you were using sqlldr to perform the inserts then there should be a .bad file and a .log file for you to review.


            HTH -- Mark D Powell --


            Message was edited by: MarkDPowell  add dot to bad, log

            • 3. Re: Precision Error

              Hi Mark & Hoek,


              Thanks for the reply, my db version is The below is the exact error returned from toad

              ORA-01438: value larger than specified precision allowed for this column


              Im not using Sqldr to perform insert since i have only toad. Is there anyway to find the troubled column in the toad.






              • 4. Re: Precision Error
                Solomon Yakobson

                Is it single INSERT.. SELECT or 50,000 single row inserts?



                • 5. Re: Precision Error

                  Can you provide an example of the inserts?

                  Is it a single SQL statement, or a PL/SQL block?

                  If it is a PL/SQL block, what exception handling (if any) do you use?

                  • 6. Re: Precision Error

                    Hi guys,


                    It is an single insert stmt, im afraid that i couldn't paste my query as it looks more untidy if pasted in the post




                    • 7. Re: Precision Error

                      THIS IS MY INSERT QUERY

                      • 8. Re: Precision Error

                        I'm afraid this is yet another case of relying on implicit datatype conversions.

                        Based on your inputs, there's no shortcut, you'll have to debug.

                        • 9. Re: Precision Error

                          Use a DML Error logging table to capture the rows causing the error.


                          This Oracle-base article has an example



                          See the SQL Language doc


                          Inserting Into a Table with Error Logging: Example The following statements
                          create a raises table in the sample schema hr, create an error logging table
                          using the DBMS_ERRLOG package, and populate the raises table with data from
                          the employees table. One of the inserts violates the check constraint on
                          raises, and that row can be seen in errlog. If more than ten errors had
                          occurred, then the statement would have aborted, rolling back any insertions made:

                          CREATE TABLE raises (emp_id NUMBER, sal NUMBER
                             CONSTRAINT check_sal CHECK(sal > 8000));

                          EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');
                          INSERT INTO raises
                             SELECT employee_id, salary*1.1 FROM employees
                             WHERE commission_pct > .2
                             LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;

                          SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;

                          ORA_ERR_MESG$               ORA_ERR_TAG$         EMP_ID SAL
                          --------------------------- -------------------- ------ -------
                          ORA-02290: check constraint my_bad               161    7700
                          (HR.SYS_C004266) violated

                          • 10. Re: Precision Error
                            Mark D Powell

                            Why do your substr statements use zero instead of 1 where columns length start with Oracle substr(expression, starting_position, for_length) ?

                            - -

                            HTH -- Mark D Powell --