Are PL/SQL stored procedures transactions?

Question

Are PL/SQL stored procedures transactions? When a trigger, procedure or function runs, is it a transaction? I usually end my procedures with COMMIT and when an error occurs I use ROLLBACK! Is it wrong?

I'am not asking about START TRANSACTION into procedures but I want to know if they are transactions.

Thank you.

Answer 1

Are PL/SQL stored procedures transactions? When a trigger, procedure or function runs, is it a transaction?

No. A transaction starts with the first DML encountered and ends when it encounters a COMMIT or ROLLBACK. A transaction can comprise many function and procedure calls and DML statements and trigger code. On the other hand, you can create a procedure that comprises of many transactions by issuing lots of COMMITs.

I usually end my procedures with COMMIT and when an error occurs I use ROLLBACK! Is it wrong?

Wrong is a strong word. Let's just say it's not a good practice. Making (packaged) functions and procedures is all about modularization: making reusable pieces of code. When a function/procedure contains ROLLBACK or COMMIT statements, it stops being reusable as it messes up the transaction of the caller. So it's better not to use ROLLBACK or COMMIT in your procedures and leave it to the topmost caller.

You could use SAVEPOINTS throughout your code which makes sure a single function or procedure doesn't leave open parts of a transaction. But for esthetical reasons I prefer to not use SAVEPOINTS. For me, it's just five lines of unnecessary code, because I know my caller function will handle the transaction just nicely.

Exception is when you create an autonomous procedure, which is by definition a single transaction and thus needs to end with a COMMIT.

UPDATE

Note that a RAISE_APPLICATION_ERROR or a RAISE [exception name] statement will also automatically rollback your PL/SQL block as a single atomic unit. Which is of course a desirable effect as it doesn't leave you with uncommitted changes.

SQL> create table mytable (id int)
  2  /

Table created.

SQL> create procedure p
  2  as
  3  begin
  4    insert into mytable values (2);
  5    raise_application_error(-20000,'My exception');
  6  end;
  7  /

Procedure created.

SQL> select *
  2    from mytable
  3  /

no rows selected

SQL> insert into mytable values (1)
  2  /

1 row created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-20000: My exception
ORA-06512: in "X.P", regel 5
ORA-06512: in regel 1


SQL> select *
  2    from mytable
  3  /

        ID
----------
         1

1 row selected.