title: ORA-00001: unique constraint violated when inserting 0 rows
body: <p>I am trying to insert 0 rows into a table that has a unique constraint and I am getting ORA-00001: unique constraint violated...</p>
  <p>Below is the PL/SQL block I've used to hopefully capture the issue well</p>
  <pre><code>declare
    l_cnt number;
  begin
    set transaction isolation level serializable;
    
    select count(*) into l_cnt from test_view;
    
    dbms_output.put_line('count = ' || to_char(l_cnt));
    
    insert into &lt;table&gt;(&lt;columns&gt;)
      select &lt;columns&gt; from test_view
      log errors ('run1')
      reject limit 0
    ;
    
    dbms_output.put_line('success');
    
  exception
    when others then
      dbms_output.put_line('ERRROR!');
      dbms_output.put_line(sqlerrm);
      rollback;
  end;
  /
  </code></pre>
  <p>This is the output</p>
  <pre><code>count = 0
  ERRROR!
  ORA-00001: unique constraint (&lt;SCHEMA&gt;.&lt;CONSTRAINT&gt;) violated
  </code></pre>
  <p>And sure enough, there is a record in the <code>ERR$_&lt;table&gt;</code> table...</p>
  <p>If I add <code>where 1 = 0</code> to the in the insert statement, everything works, nothing is inserted.</p>
  <p>I still don't believe what I am seeing :)</p>
  <p><code>Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production</code></p>
  <hr />
  <h2>Update 1</h2>
  <p>Below sample without using <code>count(*)</code> on the view as this could lead to a different query plan that selecting all required values for the insert.</p>
  <pre><code>declare
    l_cnt number;
  begin
    set transaction isolation level serializable;
    
    insert into testx_table
    select * from testx_view d;
    
    select count(*) into l_cnt from testx_table;
  
    dbms_output.put_line('count = ' || to_char(l_cnt));
  
    insert into &lt;table&gt;(&lt;columns&gt;)
    select * from testx_view d
    log errors ('run2')
    reject limit 0;
  
    dbms_output.put_line('success');
  
  exception
    when others then
      dbms_output.put_line('ERRROR!');
      dbms_output.put_line(sqlerrm);
      rollback;
  end;
  /
  </code></pre>
  <h2>UPDATE 2</h2>
  <p>I was able to reproduce the behaviour.</p>
  <pre><code>CREATE TABLE A(ID NUMBER PRIMARY KEY)
  /
  
  CREATE FUNCTION F(ID_ NUMBER) RETURN NUMBER
  AS
    L_ID NUMBER;
  BEGIN
    SELECT ID INTO L_ID FROM A WHERE ID = ID_;
    RETURN L_ID;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;
  /
  
  BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG('A');
  END;
  /
  
  BEGIN
    INSERT INTO A VALUES (1);
    
    INSERT INTO A SELECT 1 FROM DUAL WHERE F(1) IS NULL
    LOG ERRORS INTO ERR$_A;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  /
  
  SELECT * FROM ERR$_A
  /
  </code></pre>
  <p><a href="http://sqlfiddle.com/#!4/74325/1" rel="nofollow noreferrer">sqlfiddle</a></p>
  <p>And it all boils down to querying table that is being modified from within the function. The functions throws the <code>ORA-04091: table A is mutating, trigger/function may not see it</code> but the code catches all exceptions and returns null.</p>
  <p>Obviously, selecting from table that's mutating is a nono and must be fixed.</p>
  <p>And I am quite angry as I cannot count the number of times I told my collegues to stop using <code>exception when others then return null</code>. This is again an example where it completely masked the issue and I've spent the whole day deubgging it.</p>