I have some data coming in a collection that I need to insert into a table if not found, update if found. So merge should work. But, if the collection is not unique I get "ORA-30926: unable to get a stable set of rows in the source tables" if the table has rows or "ORA-00001: unique constraint violated" if table is empty.
My database and setup
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
drop type vc256;
create or replace type vc256 is table of varchar2(256);
/
drop table t purge;
create table t (
v varchar2(30) not null
, n number not null
, constraint t_pk primary key ( v )
)
/
declare
c vc256 := vc256('apple','orange','apple');
begin
merge into t
using ( select column_value from table(c) ) s
on ( t.v = s.column_value )
when not matched then
insert ( t.v, t.n )
values ( s.column_value, 1 )
when matched then
update set t.n = t.n + 1;
commit;
end;
/
In the case of vc256('apple','orange','apple') I want to insert for the first 'apple' and update for the second 'apple', I get pk violation error if the table is empty or "unable to get stable set of rows" if the table already has some rows. If the collection has unique elements then the merge statement works.
Is my option row-by-row processing?