How to mount a Transaction with Commit and Rollback in Oracle?

I'm assuming you're connected to Oracle directly and are not doing this through a programming language like Java or .NET. That said, we followed.

You do not have to be stuck with procedures to declare blocks of code with commits or rollbacks. You can use it directly in your preferred SQL IDE. See this example stolen 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 should be very careful when you are making queries that read inserts that you just made but have not yet done commit of those inserts. Your queries will not be able to read these because Oracle does not support dirty reads , as described below:

  

Dirty read: The meaning of this term is as bad as it sounds. You're   allowed 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 .

Assuming you encounter an exception during processing, you could rollback this 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;