ORA-00001: unique constraint violated when inserting 0 rows

Question

I am trying to insert 0 rows into a table that has a unique constraint and I am getting ORA-00001: unique constraint violated...

Below is the PL/SQL block I've used to hopefully capture the issue well

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 <table>(<columns>)
    select <columns> 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;
/

This is the output

count = 0
ERRROR!
ORA-00001: unique constraint (<SCHEMA>.<CONSTRAINT>) violated

And sure enough, there is a record in the ERR$_<table> table...

If I add where 1 = 0 to the in the insert statement, everything works, nothing is inserted.

I still don't believe what I am seeing :)

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

---

Update 1

Below sample without using count(*) on the view as this could lead to a different query plan that selecting all required values for the insert.

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 <table>(<columns>)
  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;
/

UPDATE 2

I was able to reproduce the behaviour.

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
/

sqlfiddle

And it all boils down to querying table that is being modified from within the function. The functions throws the ORA-04091: table A is mutating, trigger/function may not see it but the code catches all exceptions and returns null.

Obviously, selecting from table that's mutating is a nono and must be fixed.

And I am quite angry as I cannot count the number of times I told my collegues to stop using exception when others then return null. This is again an example where it completely masked the issue and I've spent the whole day deubgging it.

Answer 1

Select returned 0 rows, that was ok.

However when inserting rows to table, we were actually querying the same table through a function. The function returned ORA-04091: table A is mutating, trigger/function may not see it but it was catched in an exception block and null was returned. This caused the query to return rows...

Never use exception when others then return null!!!