Handle ORA-00001: unique constraint violations

[LarrySteele]

I'm pulling external data into a local reporting database. One of the tables I'm importing has data errors. For a number of employees in this table, they have multiple top of stack records - the current record is defined as the ~one~ record with an end date of 12/31/9999. For these few employees, they have multiple records with said end date. This is a data entry error, and I'm having the records corrected for these entries.

In the meantime...

I have a query that pulls the needed records and attempts to put them in my local table, which has a unique constraint on the employee id field. Since some employees appear multiple times in the source table, attempted inserts cause a unique constraint violation and the insert is canceled.

Here's the script that I'm running:

delete from ee_misc;
   
insert into ee_misc 
select pa1.pernr, 
       pa9.locat_dept, 
       pa9.py_entity, 
       pa9.sales_terri, 
       (select case pa0.massn when '04' then 'V' when '05' then 'I' else '' end as field1 from pa0000 pa0 where pa0.pernr=pa1.pernr and pa0.endda='99991231') 
          field1,  
       case pa3.funkt when '01' then 'Y' when '02' then 'N' when '03' then 'F' when '04' then 'S' when '05' then 'H' else '' end as 
          field2,
       (select pa0.massn from pa0000 pa0 where pa0.pernr=pa1.pernr and pa0.endda='99991231') 
          vol_invol, 
       pa3.funkt,
       pa1.plans
from pa0001 pa1,
     pa9001 pa9,
     (select distinct pa0.pernr, pa0.funkt from pa0034 pa0 where pa0.endda='99991231') pa3
where pa9.pernr=pa1.pernr
  and pa3.pernr=pa1.pernr
  and pa1.endda='99991231'
  and pa9.endda='99991231';

alter index ee_misc_pernr rebuild;

commit;

quit;

I'm running this from a batch file using the following syntax:

sqlplus -s xxxxx/yyyyy@zzzzz @aaaaa.sql

What I'd like to do is something like this pseudo-code:

delete from ee_misc;
   
insert into ...;

[b]exception when others then rollback;[/b]

alter index ee_misc_pernr rebuild;

commit;

quit;

I've used the exception statement nested in a begin...end block in a proc. However, this isn't valid in a sql script.

With that long intro, I finally get to my question. Is there a way to trap for errors and force a rollback in a sql script without using a stored proc?

What I'm trying to accomplish is this - if the script encounters errors inserting records, then rollback the delete and keep yesterday's records. It's more acceptable for some fields to be one or two days old rather then empty. We log the script and would be able to identify ORA errors and/or rollbacks for after-event troubleshooting.

Thanks in advance,
Larry

 

[Thargy]

Larry,

an exception is only raised by PL/SQL code, not sql.

If bad data is the problem, and you want to sort it out, then may I suggest the use of DBMS_ERRLOG.

You should look the package up in the online doco before use, but in a nut shell, it will automatically create an error table, into which Oracle will automatically insert all records which fail any kind of check on the main table.

You check the error table for errors (by just selecting from it) and send them to the data cleansing team.

Rinse and repeat until no errors are found. Basically Larry, let Oracle sweat, and not you.

Since I know you're experienced, I'll say no more, right now, but if you would like further assistance, I'm usually lurking hereabouts.

If this isn't what you want, let us know.

Regards

T

 

[LarrySteele]

T,

Thanks for confirming. I'll take a look at the DBMS_ERRLOG and see if that's the way to go.

Larry