Thanks for the question, Ionut.

Asked: December 09, 2021 - 8:14 am UTC

Last updated: December 13, 2021 - 2:37 am UTC

Version: Oracle 12c, SQL Developer 20.2.0

Viewed 1000+ times

You Asked

Hi Tom,

[I realized that this topic was opened as reply to an old question, so I opened a separate one.]

I have the following piece of code:

merge /*+ WITH_PLSQL */ into test a
    using (
        with function to_upper(val varchar2) return varchar2 is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = b.c2)
when matched then 
    update set a.c3 = upper(a.c1)

Especially I didn't put any ending char in the end (neither ";", nor "/").
The script works very nice only when I am ending it with "/".
When I end the script with ";", I receive the following error: ORA-00933: SQL command not properly ended
In order to pass over compilation, I can use a dynamic SQL but I want to compile it into a procedure.

At your advice, I tried to check the sqlterminator character but I receive the P2-0735: unknown SHOW option beginning "sqltermina..." error. When I am trying to set it says it is obsolete.

I don't understand why for other pieces of code it works to compile but for this, it doesn't.

Thank you,
Ionut.

and Connor said...

Thanks for logging a new question - in the review I could not tell you were in SQL Developer. (SQL Plus has the 'sqlterminator' setting)

This looks like a bug to me. I'll log one with the SQL Dev team.

Only workaround I can see is to remove the final semicolon

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

from PL/SQL

Rajeshwaran, Jeyabal, December 10, 2021 - 4:45 am UTC

but when i took the above dml into a PL/SQL compilation it goes like this.

to get it compiled, dynamic sql is one possible way.

but static sql is not possible to get it compiled here? (the below demo was from 21c XE database )

demo@XEPDB1> set feedback off
demo@XEPDB1> drop table test purge;
demo@XEPDB1> create table test( c1 varchar2(5), c2 varchar2(5), c3 varchar2(5) );
demo@XEPDB1> insert into test(c1,c2) values ('A','A');
demo@XEPDB1> commit;
demo@XEPDB1> set feedback 6
demo@XEPDB1>
demo@XEPDB1> merge /*+ WITH_PLSQL */ into test a
  2      using (
  3          with function to_upper(val varchar2) return varchar2 is
  4          begin
  5              return upper(val);
  6          end;
  7          select to_upper(c2) as c2 from test
  8      ) b
  9  on (upper(a.c1) = b.c2)
 10  when matched then
 11      update set a.c3 = upper(a.c1)
 12  /

1 row merged.

demo@XEPDB1> create or replace procedure p1 as
  2  begin
  3     merge /*+ WITH_PLSQL */ into test a
  4             using (
  5                     with function to_upper(val varchar2) return varchar2 is
  6                     begin
  7                             return upper(val);
  8                     end;
  9                     select to_upper(c2) as c2 from test
 10             ) b
 11     on (upper(a.c1) = b.c2)
 12     when matched then
 13             update set a.c3 = upper(a.c1) ;
 14  end;
 15  /

Warning: Procedure created with compilation errors.

demo@XEPDB1> show err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PL/SQL: SQL Statement ignored
5/18     PL/SQL: ORA-00905: missing keyword
9/4      PLS-00103: Encountered the symbol "SELECT"
10/3     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         . , @ ; for <an identifier>
         <a double-quoted delimited-identifier> group having intersect
         minus order partition start subpartition union where connect
         sample

demo@XEPDB1> create or replace procedure p1 as
  2  begin
  3     merge /*+ WITH_PLSQL */ into test a
  4             using (
  5                     with function to_upper(val varchar2) return varchar2 is
  6                     begin
  7                             return upper(val);
  8                     end;
  9                     select to_upper(c2) as c2 from test
 10             ) b
 11     on (upper(a.c1) = b.c2)
 12     when matched then
 13             update set a.c3 = upper(a.c1)
 14     /

Warning: Procedure created with compilation errors.

demo@XEPDB1> end;
SP2-0042: unknown command "end" - rest of line ignored.
demo@XEPDB1> show err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PL/SQL: SQL Statement ignored
5/18     PL/SQL: ORA-00905: missing keyword
9/4      PLS-00103: Encountered the symbol "SELECT"
10/3     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         . , @ ; for <an identifier>
         <a double-quoted delimited-identifier> group having intersect
         minus order partition start subpartition union where connect
         sample

December 13, 2021 - 2:37 am UTC

That is a known bug - there have been a few places in PLSQL where dynamic SQL is required to access SQL facilities.

from PL/SQL

Rajeshwaran, Jeyabal, December 10, 2021 - 5:32 am UTC

one workaround i see here is by pushing the with clause function inside a a view and get it referenced in "merge" statement using static sql.

something like this.

anything other workaround here?

demo@XEPDB1> create or replace view v as
  2  with function to_upper(val varchar2) return varchar2 is
  3  begin
  4     return upper(val);
  5  end;
  6  select to_upper(c2) as c2 from test
  7  /

View created.

demo@XEPDB1> create or replace procedure p1 as
  2  begin
  3     merge /*+ WITH_PLSQL */ into test a
  4             using v b
  5     on (upper(a.c1) = b.c2)
  6     when matched then
  7             update set a.c3 = upper(a.c1) ;
  8  end;
  9  /

Procedure created.

demo@XEPDB1> exec p1;

PL/SQL procedure successfully completed.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

from PL/SQL

from PL/SQL

More to Explore

PL/SQL demos

PL/SQL docs

The usage of WITH_PLSQL hint into a MERGE statement

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

from PL/SQL

from PL/SQL

More to Explore

PL/SQL demos

PL/SQL docs