Styjun

Can I tell a prospective employee that everyone in the team is leaving?

Python program to take in two strings and print the larger string

Have 1.5% of all nuclear reactors ever built melted down?

What is the object moving across the ceiling in this stock footage?

Pirate democracy at its finest

My employer faked my resume to acquire projects

Externally monitoring CPU/SSD activity without software access

Is it possible to remotely hack the GPS system and disable GPS service worldwide?

How should I introduce map drawing to my players?

Using credit/debit card details vs swiping a card in a payment (credit card) terminal

How long until a random word with letters "A", "B", "C" ends in the pattern "ABC"?

Why aren't space telescopes put in GEO?

Did the UK Government ask for the Irish backstop?

What are the real benefits of using Salesforce DX?

Compaq Portable vs IBM 5155 Portable PC

Count Even Digits In Number

Python program to find the most frequent letter in a text

Why does this if-statement combining assignment and an equality check return true?

Is it possible to play as a necromancer skeleton?

How to deal with a colleague who is being aggressive?

What is Theresa May waiting for?

Why didn't Thanos use the Time Stone to stop the Avengers' plan?

What to do when you've set the wrong ISO for your film?

A Riley Respite

How to handle row error in direct path insert

How do I limit the number of rows returned by an Oracle query after ordering?How can I get useful error messages in PHP?How to 'insert if not exists' in MySQL?Resolving foreign key before inserting new rowsDirect-Path INSERT OracleHow to duplicate 1000 rows of data in Oracle Database table and change just the value of 1 field?Is it wrong to use direct path load hint in array binding?DML ERROR LOG restrictionIs there a way to insert data to Oracle using Direct Path loading for in-memory records?How to avoid duplicate primary key values with INSERT INTO SELECT and NOT EXISTS

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

1

I have table A that I need to insert its data into table B.

The way I'm doing it is with direct path insert for performance issue:

INSERT /*+ append */ INTO
usr.B (c,d,e)
select c,d,e from usr.A;

Now I want to add an errors handling on row-level.

I mean that if there is an exception with some rows (because of constraints), the next rows will continue to insert
into A, and the specific problematic rows will be saved in log table.

So I checked the option of DML error logging for that goal, but I found that it can't work with unique duplicates constraints in direct path statements, which is a very basic and important constraints in my tables.

I saw that solution that suggest to add fictive failure if there is duplicates, but it can't be good for me because of some reasons:

1. I have a lot of tables (1000) which need to go over that process, so add that column for each table is impposible.




2. The solution will help if there are duplicates in the source table already, but in my case can be one row in each table, so the duplicate check of the source data is not good enough.

Is there any idea how to do it right?

I know there is an option of SAVE EXCEPTIONS with FORALL but I realy prefer to use INSERT SELECT for all the rows with no loop.

Thanks.

oracle performance error-handling

share|improve this question

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is anybody have an idea?
  
  – user2671057
  Mar 22 at 14:22
  

  
  
  
  

add a comment | 

1

1

I have table A that I need to insert its data into table B.

The way I'm doing it is with direct path insert for performance issue:

INSERT /*+ append */ INTO
usr.B (c,d,e)
select c,d,e from usr.A;

Now I want to add an errors handling on row-level.

I mean that if there is an exception with some rows (because of constraints), the next rows will continue to insert
into A, and the specific problematic rows will be saved in log table.

So I checked the option of DML error logging for that goal, but I found that it can't work with unique duplicates constraints in direct path statements, which is a very basic and important constraints in my tables.

I saw that solution that suggest to add fictive failure if there is duplicates, but it can't be good for me because of some reasons:

1. I have a lot of tables (1000) which need to go over that process, so add that column for each table is impposible.




2. The solution will help if there are duplicates in the source table already, but in my case can be one row in each table, so the duplicate check of the source data is not good enough.

Is there any idea how to do it right?

I know there is an option of SAVE EXCEPTIONS with FORALL but I realy prefer to use INSERT SELECT for all the rows with no loop.

Thanks.

oracle performance error-handling

share|improve this question

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is anybody have an idea?
  
  – user2671057
  Mar 22 at 14:22
  

  
  
  
  

add a comment | 

I have table A that I need to insert its data into table B.

The way I'm doing it is with direct path insert for performance issue:

INSERT /*+ append */ INTO
usr.B (c,d,e)
select c,d,e from usr.A;

Now I want to add an errors handling on row-level.

I mean that if there is an exception with some rows (because of constraints), the next rows will continue to insert
into A, and the specific problematic rows will be saved in log table.

So I checked the option of DML error logging for that goal, but I found that it can't work with unique duplicates constraints in direct path statements, which is a very basic and important constraints in my tables.

I saw that solution that suggest to add fictive failure if there is duplicates, but it can't be good for me because of some reasons:

1. I have a lot of tables (1000) which need to go over that process, so add that column for each table is impposible.




2. The solution will help if there are duplicates in the source table already, but in my case can be one row in each table, so the duplicate check of the source data is not good enough.

Is there any idea how to do it right?

I know there is an option of SAVE EXCEPTIONS with FORALL but I realy prefer to use INSERT SELECT for all the rows with no loop.

Thanks.

oracle performance error-handling

share|improve this question

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

INSERT /*+ append */ INTO
usr.B (c,d,e)
select c,d,e from usr.A;

share|improve this question

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

share|improve this question

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

edited Mar 24 at 4:09

Jon Heller

25.2k34994

edited Mar 24 at 4:09

Jon Heller

25.2k34994

asked Mar 19 at 14:40

user2671057user2671057

596518

asked Mar 19 at 14:40

user2671057user2671057

596518