Continue after error in script 1

[WP]

hi,

I am migrating data and have the problem that the host has no ref integrity. Resulting in my script failing with
ORA-00001: unique constraint (OMCOD.PK_OSMORUL) violated. Does anyone know how I can ignore the failed inserts and continue?

declare
cursor ORRCD IS
SELECT ORORDREF, ORRULCD, ORSTAT, ORTEXT FROM OSMXDE061.OSMORUL@OSMOSYS.DB2
WHERE ORRULCD IN ((select rlcode from OSMXDE061.OSMRUL@OSMOSYS.DB2))
order by orordref, orrulcd ;

tORORDREF number(10);
tORRULCD number(10);
tORSTAT varCHAR2(15);
tORTEXT varCHAR2(50);
tCountOSMORUL Integer;

BEGIN

OPEN ORRCD;

LOOP

FETCH ORRCD INTO tORORDREF , tORRULCD, tORSTAT, tORTEXT;

INSERT INTO OSMORUL (ORORDREF, ORRULCD, ORSTAT, ORTEXT) VALUES (tORORDREF , tORRULCD, tORSTAT, tORTEXT);

EXIT WHEN ORRCD%NOTFOUND;

End loop read_OrRCD;

end;
/

WP

http://www.neptune400.com

 

[WP]

SORTED! simple check for the row first ... then insert. I'd still like to know if there is an easier way.

declare
cursor ORRCD IS
SELECT ORORDREF, ORRULCD, ORSTAT, ORTEXT FROM OSMXDE061.OSMORUL@OSMOSYS.DB2
WHERE ORRULCD IN ((select rlcode from OSMXDE061.OSMRUL@OSMOSYS.DB2))
order by orordref, orrulcd ;

tORORDREF number(10);
tORRULCD number(10);
tORSTAT varCHAR2(15);
tORTEXT varCHAR2(50);
tCountOSMORUL Integer;

BEGIN

OPEN ORRCD;

LOOP

FETCH ORRCD INTO tORORDREF , tORRULCD, tORSTAT, tORTEXT;

select count(*) into tCountOSMORUL from osmorul where ORORDREF=tORORDREF and ORRULCD=tORRULCD;

if tCountOSMORUL=0 then

INSERT INTO OSMORUL (ORORDREF, ORRULCD, ORSTAT, ORTEXT) VALUES (tORORDREF , tORRULCD, tORSTAT, tORTEXT);
end if;


EXIT WHEN ORRCD%NOTFOUND;

End loop read_OrRCD;

end;
/

WP

http://www.neptune400.com

 

[BJCooperIT]

LOOP 
   FETCH ORRCD INTO tORORDREF , tORRULCD, tORSTAT, tORTEXT;
     begin    
       INSERT INTO OSMORUL 
              (ORORDREF, ORRULCD, ORSTAT, ORTEXT) 
       VALUES (tORORDREF , tORRULCD, tORSTAT, tORTEXT);
       exception when others then null;
     end;
   EXIT WHEN ORRCD%NOTFOUND;
End loop read_OrRCD;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[WP]

Oh I like it ... thanks

WP

http://www.neptune400.com