ERROR Handling in Multi Table Inserts [message #348917] |
Thu, 18 September 2008 05:20 |
idris.ali
Messages: 34 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Hi All,
I have a Multi Table Insert which typically inserts thousands
of rows in target tables.
Problem is that even if single record fails with some error
(Mostly Unique constraint violation error) all records are rollback
I want to get suggestions to implement it such that
I can still process the successful records.
here is the sqlplus dump....
SQL> CREATE TABLE source_t1 (id NUMBER , name VARCHAR2(100), deptname VARCHAR2(100) , salary NUMBER)
;
Table created.
SQL> CREATE TABLE dest_t1 (id NUMBER ,name varchar2(100),deptname VARCHAR2(100));
Table created.
SQL> CREATE TABLE dest_t2 (id NUMBER,name varchar2(100),salary NUMBER);
Table created.
SQL>
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO source_t1 VALUES (i,'NAME='||i,'DEPT NAME='||i,i*100);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> UPDATE source_t1 SET salary = salary-100 WHERE id = 999
2 /
1 row updated.
SQL> SELECT * FROM source_t1 WHERE salary = 99800
2 /
ID
----------
NAME
--------------------------------------------------------------------------------
DEPTNAME
--------------------------------------------------------------------------------
SALARY
----------
998
NAME=998
DEPT NAME=998
99800
ID
----------
NAME
--------------------------------------------------------------------------------
DEPTNAME
--------------------------------------------------------------------------------
SALARY
----------
999
NAME=999
DEPT NAME=999
99800
SQL> CREATE UNIQUE INDEX dest_t2_u1 ON dest_t2(salary);
Index created.
SQL> ed;
Wrote file afiedt.buf
1 INSERT ALL INTO dest_t1
2 (id,name,deptname)
3 VALUES
4 (s_id,s_name,s_deptname)
5 INTO dest_t2
6 (id,name,salary)
7 VALUES
8 (s_id,s_name,s_salary)
9 (
10 SELECT id s_id , name s_name , deptname s_deptname , salary s_salary
11 FROM source_t1
12* )
SQL> /
INSERT ALL INTO dest_t1
*
ERROR at line 1:
ORA-00001: unique constraint (APPS.DEST_T2_U1) violated
|
|
|
|
|
Re: ERROR Handling in Multi Table Inserts [message #348925 is a reply to message #348917] |
Thu, 18 September 2008 05:43 |
idris.ali
Messages: 34 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Thanks a lot for quick replies.
JRowbottom,
We are on 9i , Also I couldn't find a way to use
LOG ERRORS INTO with Multi table inserts
dr.s.raghunathan
We have noticed this in past that decreasing the commit size
have affected the performance.
Normally we go with a commit size of around 5000 to 10,000 records.
Also we wanted to pin point to the exact record which errored out
so that the debugging can be easier.
|
|
|
Re: ERROR Handling in Multi Table Inserts [message #348926 is a reply to message #348925] |
Thu, 18 September 2008 05:45 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you were on 10g, the syntax would be:INSERT ALL INTO dest_t1
(id,name,deptname)
VALUES
(s_id,s_name,s_deptname)
LOG ERRORS INTO <table> REJECT LIMIT <stuff>
INTO dest_t2
(id,name,salary)
VALUES
(s_id,s_name,s_salary)
LOG ERRORS INTO <table> REJECT LIMIT <stuff>
(SELECT id s_id , name s_name , deptname s_deptname , salary s_salary
FROM source_t1)
I don't know of a 9i solution to your problem other than looping through the records row by row and doing the insert all for each row.
|
|
|
Re: ERROR Handling in Multi Table Inserts [message #348928 is a reply to message #348926] |
Thu, 18 September 2008 05:50 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you're wiling to sacrifice the INSERT ALL approach, as @dr.s.raghunathan sugests, you can use a FORALL insert and usee the SQL%BULK_EXCEPTIONS logic to trap all the exceptions that happen in each set of inserts.
|
|
|
|
|
|
|