How to Mount a Transaction with Commit and Rollback on Oracle?

Question

I’m trying to mount a transaction on Oracle, but I don’t know how to perform a Rollback in case there’s an error in one of the querys. I searched some websites, but I couldn’t find anything clear and simple to explain.

I started doing so:

DELETE FROM 
  TB_CONFIGURACAO_EXERCICIO_CRON 
WHERE 
  COD_CONFIGURACAO_MODULO IN 
  (SELECT COD_CONFIGURACAO_MODULO FROM TB_CONFIGURACAO_MODULO WHERE COD_CONFIGURACAO = 502);

DELETE FROM
  TB_CONFIGURACAO_MODULO WHERE COD_CONFIGURACAO = 502;

DELETE FROM
  TB_CONFIGURACAO_EXERCICIO WHERE COD_CONFIGURACAO = 502;

COMMIT;

Answer 1

I’m guessing you’re connected to Oracle directly and you’re not doing it through a programming language like Java or .NET. That said, we follow it.

You don’t need to get stuck with procedures to declare blocks of code with commits or rollbacks. You can use directly in your preferred SQL IDE. See this example stolen from stackoverflow in English:

begin

  statement_zero;

  savepoint my_savepoint;

  begin
    -- if either of these fail, then exception section will be executed
    statement_one;
    statement_two;
  exception
     when others then
       rollback to my_savepoint;
  end;

  statement_three;

  commit;

end;

But you must be very attentive when you’re making queries that read inserts that you just made but you haven’t done yet commit of these inserts. Their darlings cannot make these readings because the Oracle does not support Dirty reads, as described below:

Dirty read: The meaning of this term is as bad as it Sounds. You’re permitted to read Uncommitted, or Dirty , data. You can Achieve this Effect by just Opening an OS file that someone Else is writing and Reading whatever data Happens to be there. Data Integrity is compromised, Foreign Keys are violated, and Unique constraints are Ignored.

Source: Ask Tom.

Answer 2

Assuming you find an exception during your processing, you could do a rollback on that line:

PROCEDURE SP_FAZ_ALGO
        (
          pUSU_STATIVO IN OUT USU_USUARIO.USU_STATIVO %TYPE,
          pUSU_CDUSUARIOIN OUT USU_USUARIO.USU_CDUSUARIO%TYPE,
        )
IS
sCreateUser Varchar(200);
bUsuarioExiste Number;
eUsuarioExiste Exception;
BEGIN
       SELECT 
               COUNT(usu_cdusuario) 
               INTO bUsuarioExiste 
        FROM USU_USUARIO 
        WHERE USU_CDUSUARIO = pUSU_CDUSUARIO;

        IF(bUsuarioExiste > 0) THEN
              RAISE eUsuarioExiste;
        END IF;

        SELECT usu_seq.nextval INTO pUSU_IDUSUARIO FROM DUAL;

        INSERT INTO USU_USUARIO
             (
                USU_STATIVO
             )
        VALUES
             (
                pUSU_STATIVO 
             ) ;

        COMMIT;
EXCEPTION
       WHEN eUsuarioExiste THEN
             RAISE_APPLICATION_ERROR (-20001, 'MINHA EXCEPTION LANÇOU.');
             ROLLBACK;
       WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20001, SQLCODE || ': ' || SQLERRM);
             ROLLBACK;
END SP_FAZ_ALGO;