Home » SQL & PL/SQL » SQL & PL/SQL » Abort primary key violation (Oracle 10g)
Abort primary key violation [message #294388] Thu, 17 January 2008 07:36 Go to next message
sivakumar.oracle
Messages: 9
Registered: August 2007
Location: Bangalore
Junior Member
Hi,

I got stuck up at my job,as i am doing the data transfer from one table to other where the souce table dont have a primary key constraints and for my new table i have implimeted the primary keys.

Then i run a insert statemnt with select * of all the values from source.I am getting primary violation error.Ok i understood some duplicates are there!!!

what i want to do is my process should not stop,means even if any duplicate is found it should move to other table and main process will continue till end..

can any one help me on this.

regards,
siva
Re: Abort primary key violation [message #294393 is a reply to message #294388] Thu, 17 January 2008 07:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If at all you want the erred rows into another table, you can use a cursor and in the for cursor loop, you can have a exception handler and when dup_value_on_index, insert the row into the other table.

If you don't care about the erred rows, you can have a select such that it will pick only required rows.

By
Vamsi
Re: Abort primary key violation [message #294396 is a reply to message #294388] Thu, 17 January 2008 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways to achieve this.
For instance, you can:
- Do it in 2 passes, one to insert non duplicates, one to insert duplicates in other table using EXISTS/NOT EXISTS or IN/NOT IN
- Use LOG ERRORS clause and then insert faulty rows inside the other table using exception table data

Regards
Michel
Re: Abort primary key violation [message #294397 is a reply to message #294393] Thu, 17 January 2008 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@vamsi kasina

This is really a slow process to do it row by row.

Regards
Michel
Re: Abort primary key violation [message #294403 is a reply to message #294397] Thu, 17 January 2008 08:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Yes. The worst case solution Cool .

By
Vamsi
Re: Abort primary key violation [message #294545 is a reply to message #294393] Thu, 17 January 2008 23:51 Go to previous messageGo to next message
sivakumar.oracle
Messages: 9
Registered: August 2007
Location: Bangalore
Junior Member
Vamsi,

If i handle the situation using exception, my control will not get back for doing remaining inserts.So whole process will stops at that instant.

I want to have complete records get transfered to target table and simaltaneously the duplicates which violates the PK will get into the DUP_TAB.


Regards,
siva
Re: Abort primary key violation [message #294560 is a reply to message #294396] Fri, 18 January 2008 00:26 Go to previous messageGo to next message
sivakumar.oracle
Messages: 9
Registered: August 2007
Location: Bangalore
Junior Member
Very Good IDEA 'MICHEL' , but mine is 10.1 version

LOG ERRORS will work from 10.2 release.

If i want to approach by first method what u said,it eats my performance.
AS i am performing BULK inserts,because my source tables having millions of records.

regrds,
siva.
Re: Abort primary key violation [message #294561 is a reply to message #294545] Fri, 18 January 2008 00:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Siva,
I said
Quote:
in the for cursor loop, you can have a exception handler
When you handle in the cursor for loop it won't go out of the loop.
Anyway it is a row by row process and perform badly.

By
Vamsi
Re: Abort primary key violation [message #294563 is a reply to message #294560] Fri, 18 January 2008 00:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Regarding Michel's first solution, I didn't understand your problem . What is the problem with BULK Insert in that approach?

By
Vamsi
Re: Abort primary key violation [message #294570 is a reply to message #294563] Fri, 18 January 2008 00:56 Go to previous messageGo to next message
sivakumar.oracle
Messages: 9
Registered: August 2007
Location: Bangalore
Junior Member
OK vamsi, you are telling right! but i am thinking that using EXISTS/NOT EXISTS clause in select statement of insert this will slow down the process.


regrds,
siva
Re: Abort primary key violation [message #294572 is a reply to message #294560] Fri, 18 January 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but mine is 10.1 version

As I always say: "post your Oracle version (4 decimals)" and here's a reason.

But you can use "SAVE EXCETIONS" in 10.1 PL/SQL:
PL/SQL User's Guide and Reference
Chapter 11 Tuning PL/SQL Applications for Performance
Section Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)
Subsection Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute

Regards
Michel
Re: Abort primary key violation [message #295210 is a reply to message #294572] Mon, 21 January 2008 13:55 Go to previous messageGo to next message
alekh_orafaq
Messages: 1
Registered: January 2008
Junior Member
Hi,

If you workout something like creating a db_trigger before insert.
With help of autonomous transaction inside the trigger you can see the count and handle the dup_table insert in autonomous transaction logic that should help.

As per my assumption this is a one time activity, and you can disable the trigger once you are done.

All the best



Re: Abort primary key violation [message #295214 is a reply to message #295210] Mon, 21 January 2008 14:15 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not work and will not answer OP's question.
If you think it should work, post code.

Regards
Michel
Previous Topic: View Help
Next Topic: KILL command
Goto Forum:
  


Current Time: Sat Dec 10 08:55:21 CST 2016

Total time taken to generate the page: 0.11581 seconds