Rollback @ Stored Procedure on Oracle

Question

I don't know if thats right but for some reason my stored procedure is not rolling back after an exception occurs. So my insert statement is commited even when i get an exception

Did i forgot something?

PROCEDURE SP_USUARIO_INSERT
        (
          pUSU_IDUSUARIO          IN OUT ENG.USU_USUARIO.USU_IDUSUARIO%TYPE,
          pUSU_CDUSUARIO          IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
          pPES_IDPESSOA           IN ENG.USU_USUARIO.PES_IDPESSOA%TYPE,
          pUSU_DLSENHA            IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
          pUSU_DLOBSERVACAO       IN ENG.USU_USUARIO.USU_DLOBSERVACAO%TYPE,
          pUSU_NUIP               IN ENG.USU_USUARIO.USU_NUIP%TYPE,
          pUSU_DTCADASTRO         IN ENG.USU_USUARIO.USU_DTCADASTRO%TYPE,
          pUSU_DTDESATIVACAO      IN ENG.USU_USUARIO.USU_DTDESATIVACAO%TYPE,
          pUSU_DTULTIMOACESSO     IN ENG.USU_USUARIO.USU_DTULTIMOACESSO%TYPE,
          pUSU_DLMAQUINA          IN ENG.USU_USUARIO.USU_DLMAQUINA%TYPE,
          pUSU_STNOVO             IN ENG.USU_USUARIO.USU_STNOVO%TYPE,
          pUSU_STATIVO            IN ENG.USU_USUARIO.USU_STATIVO%TYPE
        )
IS
sCreateUser Varchar(200);
bUsuarioExiste Number;
eUsuarioExiste Exception;
BEGIN
       SELECT 
               COUNT(usu_cdusuario) 
               INTO bUsuarioExiste 
        FROM ENG.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 ENG.USU_USUARIO
             (
                USU_IDUSUARIO, 
                USU_CDUSUARIO, 
                PES_IDPESSOA, 
                USU_DLOBSERVACAO, 
                USU_NUIP, 
                USU_DTCADASTRO, 
                USU_DTDESATIVACAO, 
                USU_DTULTIMOACESSO, 
                USU_DLMAQUINA, 
                USU_STNOVO, 
                USU_STATIVO
             )
        VALUES
             (
                pUSU_IDUSUARIO, 
                pUSU_CDUSUARIO, 
                pPES_IDPESSOA, 
                pUSU_DLOBSERVACAO, 
                pUSU_NUIP, 
                sysdate, 
                pUSU_DTDESATIVACAO, 
                pUSU_DTULTIMOACESSO, 
                pUSU_DLMAQUINA, 
                pUSU_STNOVO, 
                pUSU_STATIVO 
             ) ;
        sCreateUser := 'CREATE USER ' || pUSU_CDUSUARIO || ' IDENTIFIED BY ' || pUSU_DLSENHA;
        EXECUTE IMMEDIATE sCreateUser;
        EXECUTE IMMEDIATE 'GRANT ENG_GERAL TO ' || pUSU_CDUSUARIO;
        COMMIT;
EXCEPTION
       WHEN eUsuarioExiste THEN
             RAISE_APPLICATION_ERROR (-20001, 'Usuário já existe ou possui nome inválido.');
             ROLLBACK;
       WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20001, SQLCODE || ': ' || SQLERRM);
             ROLLBACK;
END SP_USUARIO_INSERT;

Answer 1

I suspect you are 'handling' (or more accurately, ignoring) your exception.

create table temp (id number);
DECLARE
  v_str VARCHAR2(2);
BEGIN
  INSERT INTO temp VALUES (1);
  v_str := '123';
EXCEPTION
  WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Whoops');
END;
/
select * from temp;

Will show the row because, as far as the SQL layer is concerned, the procedure completed successfully (as the exception was caught and ignored).

There can be other reasons such as

• The insert happens before the procedure execution and so isn't rolled back when the statement fails (and you don't explicitly rollback the transaction)
• The insert is committed by a explicit commit before the exception is raised.

Answer 2

The "EXECUTE IMMEDIATE sCreateUser;" is implicitly committing your insert.

Answer 3

I think if you restructure your code, you can get the behavior you want.

PROCEDURE SP_USUARIO_INSERT      
        (      
          pUSU_IDUSUARIO          IN OUT ENG.USU_USUARIO.USU_IDUSUARIO%TYPE,      
          pUSU_CDUSUARIO          IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,      
          pPES_IDPESSOA           IN ENG.USU_USUARIO.PES_IDPESSOA%TYPE,      
          pUSU_DLSENHA            IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,      
          pUSU_DLOBSERVACAO       IN ENG.USU_USUARIO.USU_DLOBSERVACAO%TYPE,      
          pUSU_NUIP               IN ENG.USU_USUARIO.USU_NUIP%TYPE,      
          pUSU_DTCADASTRO         IN ENG.USU_USUARIO.USU_DTCADASTRO%TYPE,      
          pUSU_DTDESATIVACAO      IN ENG.USU_USUARIO.USU_DTDESATIVACAO%TYPE,      
          pUSU_DTULTIMOACESSO     IN ENG.USU_USUARIO.USU_DTULTIMOACESSO%TYPE,      
          pUSU_DLMAQUINA          IN ENG.USU_USUARIO.USU_DLMAQUINA%TYPE,      
          pUSU_STNOVO             IN ENG.USU_USUARIO.USU_STNOVO%TYPE,      
          pUSU_STATIVO            IN ENG.USU_USUARIO.USU_STATIVO%TYPE      
        )      
IS      
sCreateUser Varchar(200);      
bUsuarioExiste Number;      
eUsuarioExiste Exception;      
l_sqlcode      number;
BEGIN      
       SELECT       
               COUNT(usu_cdusuario)       
               INTO bUsuarioExiste       
        FROM ENG.USU_USUARIO       
        WHERE USU_CDUSUARIO = pUSU_CDUSUARIO;      

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

        sCreateUser := 'CREATE USER ' || pUSU_CDUSUARIO || ' IDENTIFIED BY ' ||     
                       pUSU_DLSENHA;      
        EXECUTE IMMEDIATE sCreateUser;      

        begin
              EXECUTE IMMEDIATE 'GRANT ENG_GERAL TO ' || pUSU_CDUSUARIO;      

              -- moved this into the insert          
              --SELECT usu_seq.nextval INTO pUSU_IDUSUARIO FROM DUAL;      

              INSERT INTO ENG.USU_USUARIO      
              (      
                   USU_IDUSUARIO,       
                   USU_CDUSUARIO,       
                   PES_IDPESSOA,       
                   USU_DLOBSERVACAO,       
                   USU_NUIP,       
                   USU_DTCADASTRO,       
                   USU_DTDESATIVACAO,       
                   USU_DTULTIMOACESSO,       
                   USU_DLMAQUINA,       
                   USU_STNOVO,       
                   USU_STATIVO      
              )      
              VALUES      
              (      
                   usu_seq.nextval,  --pUSU_IDUSUARIO,       
                   pUSU_CDUSUARIO,       
                   pPES_IDPESSOA,       
                   pUSU_DLOBSERVACAO,       
                   pUSU_NUIP,       
                   sysdate,       
                   pUSU_DTDESATIVACAO,       
                   pUSU_DTULTIMOACESSO,       
                   pUSU_DLMAQUINA,       
                   pUSU_STNOVO,       
                   pUSU_STATIVO       
              ) 
              returning ;      
        exception
          when others then 
             -- save off the sqlcode, you will need it to reraise
             l_sqlcode := SQLCODE;
             -- rollback any insert that may have run, 
             -- depending on where the exception was raised
             rollback;
             -- drop the created user
             execute immediate 'drop user ' || pUSU_CDUSUARIO ;
             -- reraise the error
             RAISE_APPLICATION_ERROR (-20001, l_sqlcode || ': ' || 
                                      SQLERRM(-l_sqlcode));                   
        end;

        COMMIT;      
EXCEPTION      
       WHEN eUsuarioExiste THEN      
             RAISE_APPLICATION_ERROR (-20001, 
                         'Usuário já existe ou possui nome inválido.');      
             ROLLBACK;      
       WHEN OTHERS THEN      
             RAISE_APPLICATION_ERROR (-20001, 
                 SQLCODE || ': ' || SQLERRM);      
             ROLLBACK;      
END SP_USUARIO_INSERT;   

Answer 4

Doesn't the RAISE_APPLICATION_ERROR mean that the ROLLBACK is unreachable? Unless the caller is also issuing a ROLLBACK, I think those need to be the other way around. You may be getting in implicit commit from DDL as other have said, depending on what's actually erroring, so it might be irrelevant, but it doesn't look quite right.