title: PL/SQL - Insert command doesn't execute in EXCEPTION block
body: <p>I have this function in PL/SQL where i want to return the salary of a given employee. Also I want to insert values in a table created by me every time the function executes. The problem is that the function inserts the values when it executes without errors, but doesn't inserts when it enters the    <code>EXCEPTION</code>    block. I don't get any errors at compile, it just doesn't execute the insert.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION f(
      v_name employees.last_name%TYPE DEFAULT 'Bell')
    RETURN NUMBER
  IS
    v_salary       employees.salary%type;
    v_user         my_info.user%type;
    v_data         my_info.data%type;
    v_command      my_info.command%type := 'f';
    v_no_lines     my_info.no_lines%type;
    v_error        my_info.error%type;
  BEGIN
    SELECT USER INTO v_user FROM DUAL;
    SELECT SYSDATE INTO v_data FROM DUAL;
    SELECT SALARY INTO v_salary FROM employees WHERE last_name = v_name;
    v_no_lines := SQL%ROWCOUNT;
    INSERT INTO my_info VALUES(v_user, v_data, v_command, v_no_lines, 'No error'); 
    RETURN v_salary;
  
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      v_no_lines := SQL%ROWCOUNT;
      v_error := 'No employees with given name';
      INSERT INTO my_info VALUES(v_user, v_data, v_command, v_no_lines, v_error); --doesn't make the insert
      RAISE_APPLICATION_ERROR(-20000, 'No employees with given name');  
  WHEN TOO_MANY_ROWS THEN 
      v_no_lines := SQL%ROWCOUNT;
      v_error := 'More than one employee with given name';
      INSERT INTO my_info VALUES(v_user, v_data, v_command, v_no_lines, v_error); --doesn't make the insert
      RAISE_APPLICATION_ERROR(-20001, 'More than one employee with given name');
  
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002,'Other error!');
  END f;
  / 
  </code></pre>
  
  <hr>
  
  <p><strong>Later Edit</strong></p>
  
  <p>I finally managed to make an insert, but only after i execute the function.
  I modified the function and deleted all the inserts from it and made it return salary + number of rows affected</p>
  
  <pre><code>DECLARE
    v_user         my_info.user%type;
    v_data         my_info.data%type;
    v_command      my_info.command%type := 'f';
    v_no_lines     my_info.no_lines%type;
    v_error        my_info.error%type;
  BEGIN 
    SELECT USER INTO v_user FROM DUAL;
    SELECT SYSDATE INTO v_data FROM DUAL;
  
    DBMS_OUTPUT.PUT_LINE(f('Bad Input', v_no_lines));
    v_error := 'No error';
    INSERT INTO my_info VALUES(v_user, v_data, v_command, v_no_lines, v_error);
  
    EXCEPTION
    WHEN OTHERS THEN
      v_no_lines := 0;
      v_error := SQLERRM;
      INSERT INTO my_info VALUES(v_user, v_data, v_command, v_no_lines, v_error);
  END;
  /
  </code></pre>