title: PL/SQL transactions when invoking procedures
body: <p>I have 2 tables in a master-detail composition, User and Employee (this is because Employee is a type of user and there are other types of users). When an user is added to the system, in many cases I must also add details to Employee, and the same happens when updating those tables. That means I must guarantee atomicity for those 2 operations (insert and update).</p>
  
  <p>One requirement is to use stored packages and procedures to do CRUD (tables are never directly consumed).</p>
  
  <p>I wrote the following simple example in PL/SQL to try to understand how transactions work in this context:</p>
  
  <pre><code>CREATE TABLE test_a
  (
      campo_1 VARCHAR2(10) NOT NULL
  );
  CREATE TABLE test_b
  (
      campo_2 VARCHAR2(10) NOT NULL
  );
  /
  CREATE PROCEDURE ins_a
  (
      texto_1 IN VARCHAR2
  )
  IS
  BEGIN
      INSERT INTO test_a (campo_1) 
      VALUES (texto_1);
  END;
  /
  CREATE PROCEDURE ins_b
  (
      texto_2 IN VARCHAR2
  )
  IS
  BEGIN
      INSERT INTO test_b (campo_2) 
      VALUES (texto_2);
  END;
  /
  CREATE PROCEDURE ins_todos
  (
      texto_1 IN VARCHAR2,
      texto_2 IN VARCHAR2
  )
  IS
  BEGIN
      ins_a(texto_1);
      ins_b(texto_2);
  END;
  /
  </code></pre>
  
  <p>If I run </p>
  
  <pre><code>begin
  ins_todos('alfa', '1234567846513216549');
  end;
  /
  </code></pre>
  
  <p>the procedure obviously fails because of the length of the second parameter, but the first procedure does not insert the first parameter, which surprised me because I expected the first procedure to succeed anyways.</p>
  
  <p>My questions are:</p>
  
  <ul>
  <li>Is this because of an implicit transaction running somewhere?</li>
  <li>If so, does this free me from explicitly managing transaction in my PL/SQL code? (I still think I should explicitly use transactions in the code.)</li>
  <li>How can I ensure that calling multiple procedures inside another procedure will respect rollbacks and commits? My purpose is that all inner procedures must rollback if one of them throws an exception.</li>
  <li>And last, can I do all of this in PL/SQL or do I need to manage it in my C# data access layer?</li>
  </ul>