Oracle merge constants into single table

In Oracle, given a simple data table:

create table data (
    id       VARCHAR2(255),
    key      VARCHAR2(255),
    value    VARCHAR2(511));

suppose I want to "insert or update" a value. I have something like:

merge into data using dual on 
    (id='someid' and key='testKey')
when matched then 
    update set value = 'someValue' 
when not matched then 
    insert (id, key, value) values ('someid', 'testKey', 'someValue');

Is there a better way than this? This command seems to have the following drawbacks:

• Every literal needs to be typed twice (or added twice via parameter setting)
• The "using dual" syntax seems hacky

If this is the best way, is there any way around having to set each parameter twice in JDBC?

From stackoverflow David Citron

• Use a stored procedure

  From shyam

• I would hide the MERGE inside a PL/SQL API and then call that via JDBC:

  data_pkg.merge_data ('someid', 'testKey', 'someValue');
  

  As an alternative to MERGE, the API could do:

  begin
     insert into data (...) values (...);
  exception
     when dup_val_on_index then
        update data
        set ...
        where ...;
  end;
  

  From Tony Andrews

• I don't consider using dual to be a hack. To get rid of binding/typing twice, I would do something like:

  merge into data
  using (
      select
       'someid' id,
       'testKey' key,
       'someValue' value
      from
       dual
  ) val on (
      data.id=val.id
      and data.key=val.key
  )
  when matched then 
      update set data.value = val.value 
  when not matched then 
      insert (id, key, value) values (val.id, val.key, val.value);
  

  From Craig

• I prefer to try the update before the insert to save having to check for an exception.

  update data set ...=... where ...=...;
  
  if sql%notfound then
  
      insert into data (...) values (...);
  
  end if;
  

  Even now we have the merge statement, I still tend to do single-row updates this way - just seems more a more natural syntax. Of course, merge really comes into its own when dealing with larger data sets.

  David Aldridge : I think you're right that it seems a more natural syntax, but I prefer the single-transaction approach of the merge myself -- no chance of anything untoward happening between update and insert.

  Nick Pierpoint : Nothing untoward can happen between the update and the insert - this is an Oracle consistent transaction view.

  Nathan Feger : Does this syntax work in oracle9i?

  Nick Pierpoint : Yes, this syntax works in Oracle 9i.
  From Nick Pierpoint

• When your source and target table are same,you need to use DUAL.

  From $pageUsers[$post.author].name