How to do DBMS_OUTPUT print within oracle merge into?

Question

I have this

set serveroutput on size 30000

MERGE INTO ADV_TEST_INSTITUTION to_t
  USING INSTITUTION from_t
  ON (to_t.CALLISTA_INSTITUTION_CD = from_t.INSTITUTION_CD)

  WHEN NOT MATCHED THEN
    DBMS_OUTPUT.PUT_LINE('not match: ' || from_t.name)
  WHEN MATCHED THEN
    DBMS_OUTPUT.PUT_LINE('match: ' || from_t.name);

Error:

Error report - SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword"

Is it a way to print value of column?

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2017-12-15T06:30:44

MERGE INTO is a SQL statement and DBMS_OUTPUT.PUT_LINE is a PL/SQL procedure. You are trying to combine these two in a single statement, which is not possible.

It only works if you want to perform INSERT or UPDATE.

MERGE INTO ADV_TEST_INSTITUTION to_t
  USING INSTITUTION from_t
  ON (to_t.CALLISTA_INSTITUTION_CD = from_t.INSTITUTION_CD)

  WHEN NOT MATCHED THEN
    INSERT ( col1,col2,col3) VALUES ( fromt.col1,from_t.col2,from_t.col3) 
  WHEN MATCHED THEN
    UPDATE SET to_t.col1 = from_t.col1 , to_t.col12 = from_t.col2;

If your aim is only to compare the records in the two tables, and not to perform any DMLs,

You can simply use a LEFT join and CASE like this.

SELECT CASE
          WHEN to_t.CALLISTA_INSTITUTION_CD IS NULL
          THEN
             'not match: ' || from_t.name
          ELSE
             'match: ' || from_t.name
       END
          AS match_nomatch
  FROM INSTITUTION from_t
       LEFT JOIN ADV_TEST_INSTITUTION to_t
          ON to_t.CALLISTA_INSTITUTION_CD = from_t.INSTITUTION_CD;

How to do DBMS_OUTPUT print within oracle merge into?

1 Answers