PL/SQL Rollback Transaction with multiple package calls

Question

I am new to Oracle PL/SQL and am trying to adjust from SQL Server with C# to a Oracle custom web application using PL/SQL for multiple layers of the application.

I currectly am having issues with rolling back a transaction. Since I am using to writing reusable code, I have written my PL/SQL code using packages and methods within those classes. My update procedure first completes validation and if validated successfully, calls a succession of various package methods to complete the save. Unfortunately, my rollback in the EXCEPTION portion of the update procedure does not rollback everything when the Rollback function is called. I'm at a loss as to why it is doing this. My basic code (although not exact due to legal issues) is as follows:

PROCEDURE SaveApplicationData(
variableName                  IN VARCHAR2 DEFAULT NULL,
--...
seq_id OUT INT )
AS
BEGIN
SET TRANSACTION NAME 'Transaction Name';

  --Save initial program record
  SaveNewRecord(variableName, seq_id);
  IF (seq_id != 0) THEN
    --If saved successfully, seq_id represents record ID
    package_class.secondarySaveMethod(variableName, seq_id);
    second_package_class.anotherSaveMethod(variableName, seq_id);  

END IF;

  COMMIT;
  htp.p('Sequence ID: ' || seq_id);
  htp.p('Saved the record"' || programName || '" successfully!');
EXCEPTION
WHEN OTHERS THEN
 utilityPackage.rollbacktransaction;
END SaveApplicationData;

The utilityPackage.rollbacktransaction includes a ROLLBACK as well as the custom error exception handling package used by our organization.

Essentially, it will roll back the section that causes the error, but as soon as it rolls the section back, it continues with the rest of the transaction (and does not roll back previously executed blocks of code).

Please let me know if this does not make sense - and thank you in advance for your help!

This sounds more complicated than things need to be. Perhaps it is necessary to do things this way. Either way, without seeing all your code it is difficult for us to give any advice. — APC, Sep 27 '13 at 20:15
APC - in what way does it sound too complicated? As far as the code goes, it is identical structure to my code...it just has less variables and different names. The main question I have is when a rollback is executed in a package method - should it also roll back the code in the parent procedure that called the package method? — Laura Ritchey, Sep 27 '13 at 21:27
rollbacks affect the whole transaction. unless you have savepoints — APC, Sep 27 '13 at 21:33
Ok, that's what I thought it should do but wasn't sure since it wasn't acting as expected. I'll check about posting more exact code. Thanks for the quick answer. — Laura Ritchey, Sep 27 '13 at 21:41

bdeem · Accepted Answer · 2014-12-10T16:50:48.100

1

Coming from a SQL Server environment before Oracle I can understand the confusion. Oracle does not use a BEGIN TRANSACTION. Instead, a transaction is implicitly started for you.

Thus, I believe in your case SET TRANSACTION NAME is not what you are looking to do, see SET TRANSACTION.

I recommend removing the rollback code from your package and placing it in C#. It should be the responsibility of the caller to commit. Use a transaction in C# to guarantee the the transaction is committed upon successful execution of the package(s).

Ideally, your package structure should look more like this.

declare
  ex_custom EXCEPTION;
  PRAGMA EXCEPTION_INIT( ex_custom, -20001 );
begin
  --Save initial program record
  SaveNewRecord(variableName, seq_id);
  IF (seq_id != 0) THEN
    --If saved successfully, seq_id represents record ID
    package_class.secondarySaveMethod(variableName, seq_id);
    second_package_class.anotherSaveMethod(variableName, seq_id);  
  ELSE
     -- seq_id invalid throw an exception
     RAISE_APPLICATION_ERROR(-20001,'Custom error')
  END IF;

  htp.p('Sequence ID: ' || seq_id);
  htp.p('Saved the record"' || programName || '" successfully!');
EXCEPTION
WHEN ex_custom THEN
  -- if needed we log it
  utility.log_exception;
  -- Raise it for the client to handle
  raise;
END SaveApplicationData;

edited Dec 10 '14 at 16:50

answered Sep 28 '13 at 04:36

bdeem

899
9
14

Bradley - thanks for your help! Part of my frustration with my reqts is that they will not allow the use of a server side language. Everything from the HTML to the database is written in PL/SQL. I am struggling to change my thought process from a tiered application using a client, business and database layer to instead logically separate it in the PL/SQL code. It is much more difficult for me to create a similar structure completely in PL/SQL. That said, however, I think that I could accomplish the same thing in PL/SQL directly by handling it in the calling stored procedure though. Thanks! – Laura Ritchey Sep 28 '13 at 13:28
1

You are correct handling it in your calling stored procedure would be appropriate. Glad to help! – bdeem Sep 28 '13 at 17:21
Quick note for others...I think it is Raise_application_error rather than raise_application_exception. Otherwise it works great! – Laura Ritchey Sep 30 '13 at 15:33
Correct, raise_application_error rather than raise_application_exception. I've edited my answer. – bdeem Dec 10 '14 at 16:51

PL/SQL Rollback Transaction with multiple package calls

1 Answers1