Hello out there,
I found some strange edge case in SQL Developer and also the VS Code extension. Even sqlcl is affected.
Have a look at this:
create table test_1 (id number(5),wert varchar2(1 char));
create table test_2 (id number(5),wert varchar2(1 char));
merge into test_1 l
using test_2 r
on (l.id=r.id)
when matched then update set
wert=case when r.wert='J' then 'N' else 'J' end;
This works in SQL*Plus and does nothing as both tables are empty.
But when entering the merge command in SQL Developer or the VC Code extension I get this:
Fehler beim Start in Zeile: 4 in Befehl -
merge into test_1 l
using test_2 r
on (l.id=r.id)
when matched then update set
wert=case when r.wert='J' then 'N' else 'J' end;
Fehler bei Befehlszeile: 8 Spalte: 51
Fehlerbericht -
SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
https://docs.oracle.com/error-help/db/ora-00933/00933. 00000 - "unexpected keyword at or near %s"
*Cause: An unexpected keyword was encountered in the SQL statement at
or near the position printed in the error message.
One of the following occurred:
1. You had a typo in your SQL statement.
2. Unsupported syntax was encountered for a clause in the
statement.
3. An unsupported clause was encountered in the statement.
4. A string was terminated prematurely leading to the rest
of the string to be interpreted as keywords. For example, an
apostrophe in the string may be causing it to
end prematurely.
*Action: Take the action that corresponds with the Cause
1. Check that your SQL statement has no typos.
2. Check Oracle Database documentation to find the
correct syntax for the clause and update the problematic
clause appropriately.
3. Check Oracle Database documentation to find the correct
syntax for the statement and remove the unsupported clause.
4. Enter two single quotes instead of one to represent an
apostrophe within a string.
*Params: 1) keyword_value
keyword near the keyword causing the error. The keyword value
may be truncated for readability if it is too long.
More Details :
https://docs.oracle.com/error-help/db/ora-00933/
This also happens in script mode. Even sqlcl shows the error. Any ideas, what is going on here?
It seems that the error occurs, when the last line of the update part of the merge is a case expression. Even stranger ist this
merge into test_1 l
using test_2 r
on (l.id=r.id)
when matched then update set
wert=case when r.wert='J' then 'N' else 'J' end;
test
This runs even if the test command at the end is not executed and when it is it gives the expected error.