PL/SQL developer export query result as merge statements

Question

I just started to use PL/SQL Developers tools. Before I used to use Toad for Oracle. In Toad, I can generate Merge Statement from the Query result for every records and it give me following example result. (for single result)

MERGE user T
USING (
    select 
        1      as id, 
        'nam'  as name, 
        'sur'  as surname 
    from
    dual
) S
ON (S.id = T.id)
WHEN MATCHED
THEN UPDATE
     SET T.name = S.name,
         T.surname = S.surname
WHEN NOT MATCHED BY TARGET
THEN INSERT (id, name, surname)
     VALUES (t.id, t.name, t.surname);

How can I generate "Merge Statement" in PL/SQL developer? If don't, is anyway to generate this statement? Thanks for your help!

score 0 · Answer 1 · answered Oct 08 '20 at 07:35

Had the same problem here today and I just created an small script what generate it for me and put the output to DBMS Output. Just enable it an set the Buffer high enough. Then run following script, adjust table and column names:

BEGIN
   FOR r_cur IN (  SELECT column_one,
                          column_two,
                          column_three,
                          column_value
                     FROM some_table
                    WHERE column_one LIKE 'something%'
                 ORDER BY column_one, column_two, column_three)
   LOOP
      DBMS_OUTPUT.put_line (
            'MERGE INTO some_table A USING
 (SELECT\n
  ''' || r_cur.column_one || ''' as column_one,
  ''' || r_cur.column_two || ''' as column_two,
  ''' || r_cur.column_three || ''' as column_three,
  ''' || r_cur.column_value || ''' as column_value
  FROM DUAL) B
ON (A.column_one = B.column_one and A.column_two = B.column_two and A.column_three = B.column_three)
WHEN NOT MATCHED THEN 
INSERT (
  column_one, column_two, column_three, column_value)
VALUES (
  B.column_one, B.column_two, B.column_three, B.column_value)
WHEN MATCHED THEN
UPDATE SET 
  A.column_value = B.column_value;
'         );
   END LOOP;
END;
/

Thx for answer. It's good than nothing. But it is not same as Toad. Query's ON part must be table's primary key (not all column like column_one, column_two etc.). Also it is a little bit hard to use, because you should define all column in select query. Anyway, thank you for your concern @PanicMan — gourcam, Oct 09 '20 at 08:21
@gourcam No, it is an usual SQL, you can do what you want and adjust it like you want, it's even more flexible like in Toad, just play around with that. — PanicMan, Apr 14 '21 at 10:25

PL/SQL developer export query result as merge statements

1 Answers1