"Ignoring" Exceptions in Oracle

admin

Administrator
Staff member
Hi
I have a JDBC based Call-Logs-Server that has to store about 75 to 100 records (per second) in our Oracle 8i Database,

In order to keep up under the pressure I write to the DB in blocks of say 100 INSERTs per statement in order to maintain some aspect of performence.

I do something like this :
BEGIN
INSERT INTO CALL_LOGS(...)...
INSERT INTO CALL_LOGS(...)...
INSERT INTO CALL_LOGS(...)...
COMMIT;
END;

My problem starts when somtimes some of the records that are inserted already exist in the table - this causes an error (ORA:00001 -constraint violated).

What happens is that all the statments that comes after the errored-statement will not be executed, because oracle breaks a block after it detects an error.

It looks like that
BEGIN
INSERT INTO CALL_LOGS(.)... Well Inserted
INSERT INTO CALL_LOGS(.)... Well Inserted
INSERT INTO CALL_LOGS(.)... Errored ORA:00001
INSERT INTO CALL_LOGS(.)... Wiil not b stored
INSERT INTO CALL_LOGS(.)... Wiil not b stored
COMMIT;
END;

My Question is how can I like "Ignore" the error ones and continue from there to insert.

My idea is to execute each Insert in its own seperate block and one each block catch an exception without handling it
My idea looks like this :

declare
a number;
begin
Begin
INSERT INTO CALL_LOGS (.)
Exception
When OTHERS THEN
a:=0; -- we have to do something
END;

Begin
INSERT INTO CALL_LOGS (.)
Exception
When OTHERS THEN
a:=0; -- we have to do something
END;
.
.
.
.
Commit;
END;

My only doubts about this technique is in the performance issue (which is the issue !)

is this technique costs performance ?
is it good ?
does somebody else has a better idea ?

Thanx in advance.

Peter.
 
Back
Top