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

    Precision Error

    Mini

      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.

       

      Thanks

       

      Brgds,

      Mini

        • 1. Re: Precision Error
          Hoek

          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
              Mini

              Hi Mark & Hoek,

               

              Thanks for the reply, my db version is 11.2.0.3.0. 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.

               

              Thanks

               

              Brgds,

              Mini

              • 4. Re: Precision Error
                Solomon Yakobson

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

                 

                SY.

                • 5. Re: Precision Error
                  Hoek

                  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
                    Mini

                    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

                     

                    Brgds,

                    Mini

                    • 7. Re: Precision Error
                      Mini

                      THIS IS MY INSERT QUERY

                      • 8. Re: Precision Error
                        Hoek

                        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
                          rp0428

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

                           

                          This Oracle-base article has an example

                          http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

                           

                          See the SQL Language doc

                          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#BCEGDJDJ

                          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 --