6 Replies Latest reply on Feb 8, 2012 12:35 AM by 832302

    Exception Handling for inserts

    832302
      Hi,

      My requirement is to populate a table with values got by selecting columns from various table on join conditions. It works fine if all the rows inserted are unique. However if the row to be inserted is duplicate, it violates the uniqueness constraint.

      I want an exception wherein if select query returns 100 rows, of which 80 are already there in the table to be populated, it should just insert the 20 records.

      Below is the SP i wrote for the same. However, as soon as it meets exception condition, it just prints the condition and exits, without processing the rest of the records. Please look at the SP below and suggest a solution.
      ------------------------------------------------------------------------------------------------------------------------------------------------------------
      create or replace
      PROCEDURE PP_CMC_TEST AS 
      cursor c1 is 
      (select cdu.subscription_id,max(cdu.account_id),max(s.subscription_versn_start_date),
      max(s.service_plan_id),max(sbp.billing_period_id),sysdate-1
      ,cdu.device_name, cdu.resource_id,sum(cdu.usage),max(cdu.unit_of_measurement)
      from
      subscriptions s, subscription_billing_period sbp, consolidated_daily_usage cdu
      where s.version_end_date is null and
      sysdate-1 between sbp.start_date and sbp.end_date and
      cdu.usage_date between sbp.start_date and sbp.end_date
      and s.subscription_id = cdu.subscription_id
      and sbp.subscription_id = cdu.subscription_id 
      and sbp.subscription_id = s.subscription_id 
      and s.subscription_versn_start_date=sbp.subscription_versn_start_date
      group by cdu.subscription_id,cdu.device_name, cdu.resource_id);
      a number;
      b number;
      c date;
      d number;
      e number;
      f date;
      g varchar2 (255);
      h number;
      i number;
      j varchar2(60);
      BEGIN
      
        OPEN c1;
          LOOP
              FETCH c1 INTO a,b,c,d,e,f,g,h,i,j;
               EXIT WHEN c1%NOTFOUND;
      insert into cmc_test
      (subscription_id,account_id,subscription_versn_start_date,service_plan_id,billing_period_id,curr_date,
      device_name,resource_id,usage,unit_of_measurement) values
      (a,b,c,d,e,f,g,h,i,j);
          END LOOP;  
                                   EXCEPTION
          WHEN DUP_VAL_ON_INDEX
            THEN 
            DBMS_OUTPUT.PUT_LINE('DUPLICATE RECORD FOUND');
            
            commit;
          CLOSE c1;  
      
      END PP_CMC_TEST;
      Edited by: BluShadow on 07-Feb-2012 09:03
      added {noformat}
      {noformat} tags (for what it was worth).  Please read {message:id=9360002} and learn to do this yourself in future.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: Exception Handling for inserts
          sb92075
          829299 wrote:
          Hi,

          My requirement is to populate a table with values got by selecting columns from various table on join conditions. It works fine if all the rows inserted are unique. However if the row to be inserted is duplicate, it violates the uniqueness constraint.

          I want an exception wherein if select query returns 100 rows, of which 80 are already there in the table to be populated, it should just insert the 20 records.
          so change the SELECT so that only the 20 new rows are returned.

          SELECT ... -returns 100 rows
          minus
          SELECT ... returns 80 previously existing rows.
          • 2. Re: Exception Handling for inserts
            HuaMin Chen
            You can have the check right before the insert
            select count(*)
            into cnt
            from ..
            where ..

            if cnt=0
            insert into ..
            end if;

            to assure that the same key does not exist

            Edited by: Hua Min on Feb 7, 2012 11:22 AM
            • 3. Re: Exception Handling for inserts
              Using SQL you would create an error table and modify the INSERT to log the errors. See the 'Inserting Into a Table with Error Logging' section of http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm. Note this approach will not work if you are using direct path loads since the log table won't be used.

              If you are going to use PL/SQL for this then what you want is to use BULK COLLECT and then a FORALL with a SAVE EXCEPTIONS clause.

              >
              A. BULK COLLECT INTO plsqlTable LIMIT 5000 - These are your new records you want to INSERT

              B. FORALL ... SAVE EXCEPTIONS - This is the INSERT query to insert the new records

              C. Use a bulk exception handler. Any record in the FORALL that causes an exception will have it's index put into the bulk exception array. In the bulk exception handler you can loop through the array and access the records that caused the error using the index into the plsqlTable and do any error logging you need to do.
              >
              The bulk exception array will have the plsql table index of the row that caused the error. You can index into the plsql table (see Step A above) to access the record and then log it, INSERT it into a duplicates table or whatever you want.

              The important thing is that the records that do not have errors will still get processed. Similar result to what will happen if you use SQL and an error table.
              • 4. Re: Exception Handling for inserts
                523861
                you can also investigate the MERGE statement and/or the LOG ERRORS INTO clause of the insert statement.
                • 5. Re: Exception Handling for inserts
                  Billy~Verreynne
                  Hua Min wrote:
                  You can have the check right before the insert
                  select count(*)
                  into cnt
                  from ..
                  where ..

                  if cnt=0
                  insert into ..
                  end if;

                  to assure that the same key does not exist
                  Code like this is not "+thread safe+" - it wrongly assumes that the database is a single-user and process system and the data static.

                  That is not the case.

                  Between that select count statement and IF condition that follows, any number of transactions may have been committed with the table now containing a very different row count. So when the IF condition is executed, the table has changed and the condition evaluated is no longer relevant and applicable to the rows in that table.

                  Also - an INSERT raises exceptions (like PK violations) anyway... so why spend very expensive I/O to code such checks manually in your PL/SQL code and then do the INSERT, when the INSERT will be doing those checks irrespective?
                  • 6. Re: Exception Handling for inserts
                    832302
                    Resolved the issue by using minus operator to insert only new records.
                    Thanks all for your response.