Oracle Plugin Tokenizer – valid SQL gets ORA-0090x error stack

[pstueck]

SQuirreL 4.7.1 with ojdbc11 on Ubuntu 22.04 connected to Oracle 19c gives a ORA-0090x error stack (pls see below) for ...

with        FUNCTION leftAlign(val VarChar2)    return VarChar2 is
        begin
            return val;
        end;
select leftAlign('xxx')     from    dual
/

which is a completely valid statement since 12.1.0.1 (see also Oracle Blog). Compatibility shouldn’t be a problem (all test sessions report 19.0.0 from v$parameter).
~Only when I either change the Statement Separator from ; to /, or completely disable the Custom Query Tokenizer the statement would work.~ Corr: Only when I change the Statement Separator from ; to /—or better § … which is less likely to be used in queries—the statement would work. Disabling the Custom Query Tokenizer doesn’t help either (no idea, why I thought it worked on April 11). BTW: / as statement separator renders queries like select 1/2 from dual invalid.

Expected behavior: the statement works, regardless of tokenizer settings.

---

Also tested the very same statement with two other JDBC based SQL-Clients (one even with the very same ojdbc11 as SQuirreL).
Both clients accepted the statement gracefully and the 19c database answered as expected.

The error stack mentioned above is ...

Error : 905, Position : 15, Sql = with      FUNCTION leftAlign(val VarChar2)    return VarChar2 is
        begin
            return val
, OriginalSql = with        FUNCTION leftAlign(val VarChar2)    return VarChar2 is
        begin
            return val
, Error Msg = ORA-00905: missing keyword

Error occurred in:
with        FUNCTION leftAlign(val VarChar2)    return VarChar2 is
        begin
            return val

Error : 900, Position : 0, Sql = end
, OriginalSql = end
, Error Msg = ORA-00900: invalid SQL statement

Error occurred in:
end

Error : 904, Position : 7, Sql = select leftAlign('xxx')        from    dual
, OriginalSql = select leftAlign('xxx')     from    dual
, Error Msg = ORA-00904: "LEFTALIGN": invalid identifier

Error occurred in:
select leftAlign('xxx')     from    dual

Edit Apr 11, 2024: corrected wrong information

[gerdwagner]

When the Oracle Plugin's "Custom Query Tokenizer" is disabled SQuirreL's default statement separator is used, see menu Files --> New Session Properties --> tab SQL --> lower part of panel.

FYI statement separators can consist of multiple characters which may help you to define a separator that is unlikely to be used by chance. I just added some documentation concerning statement separators which will be available in future snapshots and versions. Excerpt from change log:

Added description for single character and multiple character SQL statement separators.
  See menu Files --> New Session Properties --> tab SQL --> lower part of panel.

As the defaults of the statement separators have been defined many years ago they will not be changed.

[pstueck]

So … for my example to work I practically must use a special statement separator? :disappointed:

Any plans teaching the Oracle plugin (I’d assume) new tricks?
So it is cool with the—required for anonymous PL/SQL—statement separators within a CTE?
And maybe even with merge? As in: returning “xxx rows integrated”?

As shown in the error stack, Oracle obviously only gets …

with        FUNCTION leftAlign(val VarChar2)    return VarChar2 is
        begin
            return val

… which of course cannot be a valid SQL statement.

[gerdwagner]

1. Disable the Oracle Plugin's "Custom Query Tokenizer".

2. At menu Files --> New Session Properties --> tab SQL --> lower part of panel set the separator to "/" if you insist on keeping the "/" at the end of your statement.

3. If you don't insist on keeping the "/" at the end of your statement you may for example use GO.

For some information on "/" in Oracle see https://stackoverflow.com/questions/4857488/what-does-do-in-pl-sql

[pstueck]

Ah … now … THAT worked. :bow: :+1:
And although GO is soooo MS-SQL-Server, it definitely is way better than / (unless one never does divisions with SQL :wink:).

Still think it would be nice to teach the Oracle PlugIns this new trick (besides—pesky me—merge).

Please pin your last answer as solution and close the problem as “workaround-ed”.