Home » SQL & PL/SQL » SQL & PL/SQL » Report error from an insert and rollback
Report error from an insert and rollback [message #37349] Fri, 01 February 2002 09:50 Go to next message
Colin Smith
Messages: 3
Registered: February 2002
Junior Member
I have a series of simple SQL insert statements run as a script.

e.g. insert into BLAHA
select BLAHB from BLAHC

If an insert fails (say due to a unique constraint), I want to log the error to a UNIX file, roll only that single statement back and exit the script.

Is this easily achieved?
Re: Report error from an insert and rollback [message #37350 is a reply to message #37349] Fri, 01 February 2002 10:44 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You may still want to follow your solution, but be aware that there are other ways of logging error records.
1.) when inserting records using sqlloader, you can specify and exceptions table.
2.) in pl/sql you can use autonomous transaction to log the details to a table and commit that and then decide whether to commit or rollback the rest of your transaction.
3.) for your solution, you could easily use UTL_FILE to write to a server based O/S file.

You can just commit after your exception is reported - you would commit all the successful stuff. Maybe just commit at the end of the program - exceptions remain just that - the statement didn't change any data.
Previous Topic: How do I get a list of databases that have a DB_LINK to my source database
Next Topic: Duplicated object
Goto Forum:

Current Time: Mon Oct 02 02:29:01 CDT 2023