title: Oracle multi-row 'insert if not exists' from select joined with table literal
body: <p>Suppose I have the following table structure:</p>
  
  <pre><code>a (a_id number primary key, a_code varchar unique not null)
  b (b_id number primary key, a_id number foreign key references a(a_id), b_value varchar
  </code></pre>
  
  <p>I need to insert on table <code>b</code> a couple hundred pairs of <code>(a_id, b_value)</code> if they do not already exist, and the information I have is <code>(a_code, b_value)</code>. I'm coming from a Postgres background where doing this would be trivial, but I'm having trouble finding a way of pulling it off in Oracle without resorting to creating and subsequently dropping a materialized view, or doing one insert per row.</p>
  
  <p>This was the least ugly solution I was able to come up with:</p>
  
  <pre><code>insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(b_value, iun_b_value) */
  into b (b_id, a_id, b_value)
  select b_id.nextval, a_id, b_value
  from a
  inner join
  
  (select 'code1' a_code, 'value1' b_value from dual union all
  select 'code2' a_code, 'value2' b_value from dual union all
  select 'code3' a_code, 'value3' b_value from dual union all
  select 'code4' a_code, 'value4' b_value from dual) new
  
  on a.a_code = new.b_value
  </code></pre>
  
  <p>But I was told I can't use that semantic hint for handling duplicate rows on production, so I tried this:</p>
  
  <pre><code>merge into b using
    (select a_id, b_value
    from a inner join 
  
    (select 'code1' a_code, 'value1' b_value from dual union all
    select 'code2' a_code, 'value2' b_value from dual union all
    select 'code3' a_code, 'value3' b_value from dual union all
    select 'code4' a_code, 'value4' b_value from dual) new
  
    new on a.a_code = new.a_code) insert_codes
  
  on (insert_codes.a_code = b.a_code and insert_codes.b_value = b.b_value)
  when not matched then
    insert (b.b_id, b.a_id, b.b_value)
    values (b_id.nextval, insert_codes.a_id, 1, insert_codes.b_value)
  </code></pre>
  
  <p>But I get the same <code>"ORA-00980: synonym translation is no longer valid"</code> error I was getting when I was referencing an alias in a subselect while trying to solve this using a <code>where not exists</code> clause.</p>
  
  <p>Is there any way to do it without materialized views or multiple inserts?</p>