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
/
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.
