1 2 Previous Next 20 Replies Latest reply: Apr 10, 2013 3:12 AM by Mr Lonely RSS

    While inserting reject bad records

    Mr Lonely
      Hi ,

      I have one SQL query like
      insert into table1 select * from table2
      Now table two has few bad records. It;s a staging table and date field in it is in VARCHAR2 format. And for few records it not in proper format. So when inserting into table1 and doing to_date it's throwing error.

      Error number is : ORA-01878

      So now I want to reject those records and insert rest of the records. Please guide how can this be achieved.
      select * from v$version
      where banner like 'Oracle%';
      
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      Thanks and regards,
      Mr. Lonely

      Edited by: Mr Lonely on Apr 10, 2013 10:35 AM -- Added Version
        • 1. Re: While inserting reject bad records
          JustinCave
          Depending on the Oracle version (it's always helpful to specify)
          dbms_errlog.create_error_log( 'TABLE2', 'TABLE2_ERR' );
          
          INSERT INTO table2
            SELECT *
              FROM table1
            LOG ERRORS INTO table2_err
            REJECT LIMIT UNLIMITED;
          Justin
          • 2. Re: While inserting reject bad records
            Ramin Hashimzadeh
            CREATE OR REPLACE FUNCTION checkDate(d VARCHAR2) RETURN DATE DETERMINISTIC IS
            BEGIN
              RETURN to_date(d,'dd.mm.yyyy');
            EXCEPTION
              WHEN OTHERS THEN RETURN NULL;
            END;
            WITH t(d) AS 
            (
              SELECT '01.01.2013' FROM dual UNION ALL
              SELECT '04.04.2013' FROM dual UNION ALL
              SELECT '.2013' FROM dual UNION ALL
              SELECT '09.04.2013' FROM dual UNION ALL
              SELECT '01.asdasdasdsad' FROM dual 
            )
            SELECT checkDate(d)  FROM t
            WHERE checkDate(d) IS NOT NULL
            Edited by: Ramin Hashimzadeh on Apr 10, 2013 9:59 AM
            • 3. Re: While inserting reject bad records
              Mr Lonely
              Hi Justin,

              Sorry I missed the version. Added it.

              One question.
              We don't want to reject other records. For other bad record it should fail. Can we catch only that particular exception? We got permission to reject only those records. And if we reject other records then that might create a problem. The job should fail for other bad records. Also can it be done without the extra table?

              Edited by: Mr Lonely on Apr 10, 2013 10:41 AM
              • 4. Re: While inserting reject bad records
                Mr Lonely
                This will not reject the record right? This will insert the bad record with date as null? please correct me if I am wrong.
                • 5. Re: While inserting reject bad records
                  Ramin Hashimzadeh
                  no you are wrong, it will ignore bad records and insert only records which can convert to date in format 'dd.mm.yyyy'
                  • 6. Re: While inserting reject bad records
                    JustinCave
                    You can certainly query the error table to see what errors were found and throw an error if there were "unauthorized" errors. Presumably, the set of "authorized" errors is likely to change over time.

                    Justin
                    • 7. Re: While inserting reject bad records
                      jeneesh
                      Mr Lonely wrote:
                      One question.
                      We don't want to reject other records. For other bad record it should fail. Can we catch only that particular exception?
                      For this you need LOOP processing. Process record by record, check the required exceptions - if the exceptions are in your limits, INSERT else REJECT it..

                      The price you have to pay is the performance.
                      • 8. Re: While inserting reject bad records
                        Mr Lonely
                        sorry I missed the part
                        WHERE checkDate(d) IS NOT NULL
                        This can be done. But this is my last choice.
                        • 9. Re: While inserting reject bad records
                          APC
                          Mr Lonely wrote:
                          We don't want to reject other records. For other bad record it should fail. Can we catch only that particular exception? We got permission to reject only those records.
                          The database gives you permission to reject a record which fails any of the table's validation rules. In fact it insists on it. So you might as well trap all the errors.

                          Having said which I'm not sure why you've gone down this route. The whole point of having a staging table (as opposed to using SQL LOader or an external table) is that it provides an opportunity to cleanse the data before loading it into the target table. So why not do that? Find al the dates which aren't in the right format and fix them.

                          Cheers, APCl
                          • 10. Re: While inserting reject bad records
                            Mr Lonely
                            can't do that :(.

                            It will be hard to get sign off that way. What do you say about the solution provide by ramin? With that I guess it will be simple also. only thing I will change it in exception block I will handle the particular exception I mentioned.
                            • 11. Re: While inserting reject bad records
                              jeneesh
                              Mr Lonely wrote:
                              can't do that :(.

                              It will be hard to get sign off that way. What do you say about the solution provide by ramin? With that I guess it will be simple also. only thing I will change it in exception block I will handle the particular exception I mentioned.
                              I am not sure how will you use that function in INSERT .... SELECT ... ?

                              You mean, you will have a function for each column to validate?
                              • 12. Re: While inserting reject bad records
                                Mr Lonely
                                even then also it's same right?

                                Like now staging to main table
                                then ext to staging table.

                                But the scenario will remain same :). We still need to reject the records with invalid date format and with same rule.

                                Edited by: Mr Lonely on Apr 10, 2013 10:53 AM
                                • 13. Re: While inserting reject bad records
                                  Mr Lonely
                                  I have only one date column.

                                  SO I can add that function like
                                  insert into table1 ( column_id, coulmn_date)
                                  select column_id, to_date(coulmn_date,'YYYY-MM-DD') from table2
                                  where new_function(coulmn_date) is not null.
                                  will it be problem or too much overhead?

                                  Edited by: Mr Lonely on Apr 10, 2013 10:58 AM -- Fixed typing mistakes
                                  • 14. Re: While inserting reject bad records
                                    APC
                                    Mr Lonely wrote:
                                    even then also it's same right?

                                    Like now staging to main table
                                    then ext to staging table.
                                    No. With an external table you would go straight from EXT to MAIN.
                                    But the scenario will remain same :). We still need to reject the records with invalid date format and with same rule.
                                    Well yes, if the data is unreliable and you can't clean it in pure SQL then you still need to reject it. That's why Nature gave us staging tables: so we load shonky data and clean it programmatically before loading into the main table. To have a staging table and not use it to clean the data is just pointless.

                                    Cheers, APC
                                    1 2 Previous Next