1 Reply Latest reply on Oct 1, 2018 12:28 PM by thatJeffSmith-Oracle

    SQL Developer erroneously raises ORA-00933 when explaining MERGE statement

    Lukas Eder

      To reproduce this bug, create the following schema (taken from this Stack Overflow question):

       

      CREATE TABLE v (

        contract_id NUMBER(18) NOT NULL PRIMARY KEY,

        tenant_id NUMBER(18) NOT NULL

      );

      CREATE TABLE t (

        t_id NUMBER(18) NOT NULL PRIMARY KEY,

        -- tenant_id column is missing here

        account_no NUMBER(18) NOT NULL,

        contract_id NUMBER(18) NOT NULL REFERENCES v

      );

      CREATE TABLE u (

        u_id NUMBER(18) NOT NULL PRIMARY KEY,

        tenant_id NUMBER(18) NOT NULL,

        account_no_old NUMBER(18) NOT NULL,

        account_no_new NUMBER(18) NOT NULL,

        UNIQUE (tenant_id, account_no_old)

      );

       

      Close the tab again, and then paste this statement in a new tab:

       

       

      MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,

                          t.account_no, t.contract_id

                  FROM t) t

      USING (

        SELECT u.account_no_old, u.account_no_new, v.contract_id

        FROM u, v

        WHERE v.tenant_id = u.tenant_id

      ) s

      ON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)

      WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;

       

       

      With the statement selected, click on any of the Explain Plan (F10) or Autotrace (F6) buttons, and you should get an ORA-00933 error.

       

      Workaround: Prepend a SELECT statement to the MERGE statement, and then call Explain Plan or Autotrace on the MERGE statement (without selecting it!):

       

      SELECT 1 FROM dual;

       

      MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,

                          t.account_no, t.contract_id

                  FROM t) t

      USING (

        SELECT u.account_no_old, u.account_no_new, v.contract_id

        FROM u, v

        WHERE v.tenant_id = u.tenant_id

      ) s

      ON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)

      WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;