3 Replies Latest reply on Feb 8, 2019 11:20 PM by Glen Conway

    Semicolon issue with merge statement involving a query with an inline pl/sql function

    GregV

      Hi,

       

      My SQL Developer version is 18.4.0.376

      My DB version is 12.2.0.1.0

       

      I'm facing an error when adding a semicolon to complete a MERGE statement. The particularity is that the source query is using an inline pl/sql function (new 12.1 feature). Here's how you can reproduce:

       

      -- Create a table and populate it

      create table t1(id number primary key, val number);

      insert into t1 values(1, 1);

      insert into t1 values(2, 2);

      commit;

       

      -- Now, in a newly opened SQL sheet, try to run the following statement (notice the semicolon at the end):

      merge /*+ with_plsql */ into t1

      using (with function f(p_val in number) return number is

             begin

               return p_val * -1;

             end;

             q as (select level id from dual connect by level <= 5)

             select id, f(id) val

             from q

            ) v

      on (t1.id = v.id)

      when not matched then insert (id, val) values (v.id, v.val)

      when matched then update set t1.val = v.val

      ;

       

      I get the following error (either running it as a statement or as as script, whether I select the whole statement or not):

      Error at Command Line : 13 Column : 1

      Error report -

      SQL Error: ORA-00933: SQL command not properly ended

      00933. 00000 -  "SQL command not properly ended"

      *Cause:   

      *Action:

       

      If I remove the semicolon, it works:

      merge /*+ with_plsql */ into t1

      using (with function f(p_val in number) return number is

             begin

               return p_val * -1;

             end;

             q as (select level id from dual connect by level <= 5)

             select id, f(id) val

             from q

            ) v

      on (t1.id = v.id)

      when not matched then insert (id, val) values (v.id, v.val)

      when matched then update set t1.val = v.val

       

      5 rows merged.

       

      Is the editor getting confused with the semicolon of the pl/sql inline function? Is there a workaround?

      Thanks