How to progromatically know to rollback a transaction (can do it in SQLServer, but need oracle solution)

Question

This seems like it should be something I already know. We need to run a bunch of sql updates in a transaction, and rollback if one of them fails. We also want to print a status message since we'll be running a large number of these. This would be simple if I were doing it in a general purpose programming language. But I am trying to find a solution a team member can use that is just SQL. She has done this in MS SQL Server in the past with the pattern below. Is there an equivalently simple pattern for Oracle?

DECLARE @ErrorVar INT;
BEGIN TRANSACTION;
UPDATE MyTable1 SET MyColumn1 = 'JSMITH' where MyColumn1 = 'JOHN';
SET @ErrorVar = @@ERROR;
UPDATE MyTable2 SET MyColumn2 = 'JSMITH' where MyColumn2 = 'JOHN';
SET @ErrorVar = @ErrorVar + @@ERROR;
UPDATE MyTable SET LoginID = 'JSMITH' where LoginID = 'JOHN';
SET @ErrorVar = @ErrorVar + @@ERROR;

IF @ErrorVar <> 0
   BEGIN
   ROLLBACK TRANSACTION;
   PRINT 'We had a problem with JSMITH and rolled back *****';
   END;
ELSE
   BEGIN
   COMMIT TRANSACTION;
   PRINT 'JSMITH Updated ok';
   END;
GO

Answer 1

you can add an EXCEPTION block at the end of your procedure:

EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;  --// Oracle will do this for you, but it doesnt hurt to be clear
  DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-     '||SQLERRM);
END;

edit: i added a call to output a backtrace to provide linenumbers in the error reporting. (this is for 10g or better)

Answer 2

If a stored procedure is your design choice (and I am not sure it is) then you might want to do something like the follwoing:

EXCEPTION
    WHEN OTHERS
    THEN
      out_status := 1;
      raise_application_error(-20001,SQLERRM);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;

Return an out status from your stored proc will then allow you to respond accordingly, if all you are looking for is a pass/fail boolean.

If straight SQL is your choice then in ORACLE if you get an exception during a sql statement update it will throw the exception and not commit any changes (I'm not sure a rollback is necessary when the update is not successful due to an exception, I believe Oracle handles this).

Answer 3

In SQLPlus, use the WHENEVER command to control behavior when an error occurs.

WHENEVER SQLERROR EXIT FAILURE ROLLBACK is fairly standard in our scripts.

Type HELP WHENEVER at the SQLPlus prompt for more info.

Answer 4

PL/SQL has rollback and rollback to savepoint commands.

You can find documentation about it here.

Answer 5

A simple SQL Script might look this way:

set serveroutput on
DECLARE
BEGIN
  dbms_output.enable(100000);  -- overrides default 10000 byte limit
  UPDATE MyTable1 SET MyColumn1 = 'JSMITH' where MyColumn1 = 'JOHN';
  UPDATE MyTable2 SET MyColumn2 = 'JSMITH' where MyColumn2 = 'JOHN';
  UPDATE MyTable SET LoginID = 'JSMITH' where LoginID = 'JOHN';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
     ROLLBACK;
     dbms_output.put_line('Error processing JSMITH');
END;
/

This is called an anonymous procedure in Oracle. You can feed it to Oracle via sqlplus easily:

sqlplus user/pw@db @yourscript

You can get fancier with the script, of course (e.g., surround each UPDATE with BEGIN-EXCEPTION-END to identify which update had the problem) but this should get you started.

Answer 6

Because my answer to akf's comment is too long for a comment, I post it as a separate answer.

Here is why it doesn't achieve anything extra/useful.

Suppose we have the three tables mentioned by Peter:

SQL> create table mytable1 (mycolumn1) as select cast('JOHN' as varchar2(6)) from dual
  2  /

Tabel is aangemaakt.

SQL> create table mytable2 (mycolumn2) as select cast('JOHN' as varchar2(6)) from dual
  2  /

Tabel is aangemaakt.

SQL> create table mytable (loginid) as select cast('JOHN' as varchar2(6)) from dual
  2  /

Tabel is aangemaakt.

And add a check constraint to make the third update statement fail:

SQL> alter table mytable add constraint no_jsmith_ck1 check (loginid <> 'JSMITH')
  2  /

Tabel is gewijzigd.

The PL/SQL block can be as simple as this, and it fails:

SQL> begin
  2    update mytable1
  3       set mycolumn1 = 'JSMITH'
  4     where mycolumn1 = 'JOHN'
  5    ;
  6    update mytable2
  7       set mycolumn2 = 'JSMITH'
  8     where mycolumn2 = 'JOHN'
  9    ;
 10    update mytable
 11       set loginid = 'JSMITH'
 12     where loginid = 'JOHN'
 13    ;
 14    commit
 15    ;
 16  end;
 17  /
begin
*
FOUT in regel 1:
.ORA-02290: check constraint (RWK.NO_JSMITH_CK1) violated
ORA-06512: at line 10

And to show that everything was rollbacked, without issuing a rollback:

SQL> select * from mytable1
  2  /

MYCOLU
------
JOHN

1 rij is geselecteerd.

SQL> select * from mytable2
  2  /

MYCOLU
------
JOHN

1 rij is geselecteerd.

SQL> select * from mytable
  2  /

LOGINI
------
JOHN

1 rij is geselecteerd.

So no exception handler is necessary here. Your proposed exception handler does this:

SQL> begin
  2    update mytable1
  3       set mycolumn1 = 'JSMITH'
  4     where mycolumn1 = 'JOHN'
  5    ;
  6    update mytable2
  7       set mycolumn2 = 'JSMITH'
  8     where mycolumn2 = 'JOHN'
  9    ;
 10    update mytable
 11       set loginid = 'JSMITH'
 12     where loginid = 'JOHN'
 13    ;
 14    commit
 15    ;
 16  EXCEPTION
 17  WHEN OTHERS THEN
 18    ROLLBACK;  --// Oracle will do this for you, but it doesnt hurt to be clear
 19    DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
 20    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-     '||SQLERRM);
 21  END;
 22  /
ORA-06512: at line 10

begin
*
FOUT in regel 1:
.ORA-20001: An error was encountered - -2290 -ERROR-     ORA-02290: check constraint (RWK.NO_JSMITH_CK1) violated
ORA-06512: at line 20

Although it's not wrong, I see nothing of added value here.

SQL> select * from mytable1
  2  /

MYCOLU
------
JOHN

1 rij is geselecteerd.

SQL> select * from mytable2
  2  /

MYCOLU
------
JOHN

1 rij is geselecteerd.

SQL> select * from mytable
  2  /

LOGINI
------
JOHN

1 rij is geselecteerd.

And why advice to add code that does nothing?

Regards, Rob.