create or replace procedure INSERT_SNAPSHOTS
(
p_error_code OUT NUMBER,
p_error_text OUT VARCHAR2
)
IS

begin
begin
insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

COMMIT;

p_error_code := 0;
p_error_text := '';

exception
when DUP_VAL_ON_INDEX then

delete from SNAPSHOT1 where C_ID in (select C_ID from SNAPSHOT_TEMP);
delete from STER_SNAPSHOT where C_ID in (select C_ID from STER_SNAPSHOT_TEMP);

insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

COMMIT;

p_error_code := 0;
p_error_text := '';
end;

exception
when OTHERS then
p_error_code := 1;
p_error_text := 'Error in inserting into snapshot tables ' || SQLERRM;
ROLLBACK;
RETURN;

end INSERT_SNAPSHOTS;
/
show errors