title: Add Conditional Transactional Behavior In Oracle Procedure
body: <p>In oracle 10g, I have one stored procedure per table per operation (insert, select, update, delete). Actually there can be multiple procedures per table per operation, for example in case of select it can be SelectList, SelectOneRecord, Search (with dynamic query).</p>
  
  <p>None of these procedures have transactions. </p>
  
  <p>Sometimes I have to combine multiple operations in a transaction. For example insert in one table and update in another table, all in one transaction. For this I make a separate procedure which has transaction. This procedure then call the two procedures.</p>
  
  <p>To enable the above combination-of-procedure-calls-in-single-transaction, I do not put any transaction behavior in the procedures, as discussed above.</p>
  
  <p>Most of the time I have to perform only one operation, such as insert in one table. Since the insert procedure do not have transaction behavior therefore I have to make a separate procedure that have transaction behavior and that procedure call the insert procedure.</p>
  
  <p>I end up with lots of basic procedures (one table, one operation) and lots of transaction procedures that are basically wrappers around basic procedures.</p>
  
  <p>My question is, is there some way to have conditional transactional behavior in the basic procedures. By this I mean that some if-condition where I can put the transaction logic, so that the transaction behavior can be on or off based on some parameter that I pass. Then when I want to do only one operation, such as insert in a table, I call the basic procedure with transaction behavior; and when I want to call two procedures in a transaction, such as insert in one table and update in another table all in one transaction, then I make a separate transaction procedure and call the two basic procedure without transaction behavior.</p>
  
  <p>The following is a transaction procedure that call another procedure and wrap it in a transaction:</p>
  
  <pre><code>BEGIN
      SAVEPOINT the_start;
  
      BasicProcedure(&lt;list of parameters&gt;);
  
      COMMIT;
  
      EXCEPTION
           WHEN OTHERS THEN
  
           BEGIN
               ROLLBACK TO the_start;
               RAISE;
           END;
  END;
  </code></pre>
  
  <p>I can very well put the savepoint line and the commit line in if-statements, but can I also put the exception block in a if-statement. Do I have to put the exception block in if-statement? What if I catch exception in procedure, would it automatically rollback when exception comes?</p>