Home » SQL & PL/SQL » SQL & PL/SQL » ERROR Handling in Multi Table Inserts
icon2.gif  ERROR Handling in Multi Table Inserts [message #348917] Thu, 18 September 2008 05:20 Go to next message
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 #348918 is a reply to message #348917] Thu, 18 September 2008 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
IF you're on 10g or higher, look into the LOG ERRORS INTO claue for INSERTS and UPDATES
Re: ERROR Handling in Multi Table Inserts [message #348919 is a reply to message #348917] Thu, 18 September 2008 05:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
commit on every 100 records or
serch for bulk collection insert in forum topics
various solutions are offered

yours
dr.s.raghunathan
Re: ERROR Handling in Multi Table Inserts [message #348925 is a reply to message #348917] Thu, 18 September 2008 05:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: ERROR Handling in Multi Table Inserts [message #348929 is a reply to message #348917] Thu, 18 September 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: ERROR Handling in Multi Table Inserts [message #348932 is a reply to message #348917] Thu, 18 September 2008 05:54 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
thanks JRowbottom

It worked on 10g Smile

I am also considering any logical solution .. like having a check
with WHEN clause for duplicates.. or having a trigger (but that will again impact performance).

Re: ERROR Handling in Multi Table Inserts [message #348936 is a reply to message #348917] Thu, 18 September 2008 05:58 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
@JRowbottom

At this point can't give away ..INSERT ALL approach

As we do validation and insertion in a single insert sql
and not sure if possible to achieve similar functionality and performance with FORALL.
Re: ERROR Handling in Multi Table Inserts [message #348938 is a reply to message #348917] Thu, 18 September 2008 05:59 Go to previous message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
@Michel

Sorry I missed the sql formatter part of the instruction Razz

will keep it in mind next time ..
Previous Topic: which block will execute faster. please suggest
Next Topic: PL/SQL suggestion
Goto Forum:
  


Current Time: Thu Nov 14 04:30:56 CST 2024