CODEWITHSUNDEEP
sqloracle-databaseerror-handlingsql-update
Ana
Ana

Reputation: 325

How to capture the failed records while updating and continue with the rest in Oracle?

I have a table with 50k records. How to capture the failed records(primary key) while updating and continue with the rest? 

Create table temp(
  id       NUMBER,
  word     VARCHAR2(1000),
  Sentence VARCHAR2(2000),
  valid    NUMBER
);

insert into temp
SELECT 1,'automation testing', 'automtestingation testing is popular kind of testing',0 FROM DUAL UNION ALL
SELECT 2,'testing','manual testing',0 FROM DUAL UNION ALL
SELECT 3,'manual testing','this is an old method of testing',0 FROM DUAL UNION ALL
SELECT 4,'punctuation','automation testing,manual testing,punctuation,automanual testing-testing',0 FROM DUAL UNION ALL
SELECT 5,'B-number analysis','B-number analysis table' FROM DUAL UNION ALL
SELECT 6,'B-number analysis table','testing B-number analysis',0 FROM DUAL;

update temp set valid = 1;

I am expecting to capture all the failed records, that is records which failed while updating to valid =1. I want to capture the primary key of all the records that failed to update and the update should continue.

Upvotes: 0

Views: 339

Answers (1)

APC
APC

Reputation: 146209

I want to capture the primary key of all the records that failed to update and the update should continue.

UPDATE is a single statement. Single statements either succeed or fail in their entirety. Consequently for the given example all records are successful or they are all failed.

However, for Bulk Operations such as ETL Oracle does provide a DML Logging capability. It is covered in the PL/SQL Reference and the Admistrator's Guide (because it requires the creation of tables to capture the errors). Although to be honest Tim Hall's article on his Oracle Base site is probably more helpful as a place to start.

Upvotes: 3

Related Questions