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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

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.

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

Unknown

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.

523861

you can also investigate the MERGE statement and/or the LOG ERRORS INTO clause of the insert statement.

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?

832302

Resolved the issue by using minus operator to insert only new records.
Thanks all for your response.