CODEWITHSUNDEEP
sqloracle-database
user1761160
user1761160

Reputation: 281

Oracle err handling

I am writing a proceudre and trying to do some logging of sql insert query success status. I have no clue how to write the if ERR insert to a error log table.

insert into TableA (select * from TableB);
commit;

**if (show ERR <> NULL)
 insert into TableErr**

can anyone guy me how shall I do it? Try search but don't know what key to look for

Upvotes: 0

Views: 58

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

As well as the classic approach to handling errors in PL/SQL, if you're loading bulk data using SQL then consider using an error handling table.

From the docs: http://docs.oracle.com/cd/E11882_01/server.112/e17120/tables004.htm#InsertDMLErrorLogging

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

Upvotes: 1

DCookie
DCookie

Reputation: 43523

It's called "Exception Handling". Have a look at this tutorial.

Upvotes: 2

Related Questions