Alternate Delimiter for Oracle not always respected

32 views
Skip to first unread message

Chris Young

unread,
Apr 29, 2021, 5:41:56 PM4/29/21
to SQL Workbench/J - DBMS independent SQL tool
Thomas,

After updating from 127.2 to 127.4, I've noticed that my profile's Alternate Delimiter of / works for PL/SQL blocks, but no longer works for DDL, such as

alter table TableA modify (
    column1 varchar2(20)
)
/


I receive an error:

An error occurred when executing the SQL command:
alter table TableA modify (
    column1 varchar2(20)
)
/

ORA-01735: invalid ALTER TABLE option

The slash seems to be sent to Oracle.

Also, If I have two alter table commands in the editor each with their own delimiter, the first delimiter isn't being respected so both alter commands and the delimiters are being sent to Oracle in one batch which fails with the same error as above.

Was there a behavior change for how the Alternate Delimiter now works?

Thanks,
Chris

Chris Young

unread,
Apr 29, 2021, 5:59:04 PM4/29/21
to SQL Workbench/J - DBMS independent SQL tool
I see a new WbDelimiter command but I don't see it documented in the PDF or online help, so I'm not sure how that is meant to work.  But I am wondering if there was a regression introduced for the DML/DDL case.  Again, it works fine for PL/SQL blocks.

Chris

Thomas Kellerer

unread,
Apr 30, 2021, 1:15:00 AM4/30/21
to sql-wo...@googlegroups.com
Yes, that's likely a regression from introducing the WbDelimiter command. I'll have a look.

I assume you have "/" defined as the alternate delimiter in the profile or globally?


WbDelimiter is documented in the devmanual (which should be part of the 127.4 download)

https://www.sql-workbench.eu/devmanual/understand-alternate-delim.html#wb-delimiter-usage


Thanks for the feedback.

Regards
Thomas

Chris Young

unread,
Apr 30, 2021, 11:33:06 AM4/30/21
to SQL Workbench/J - DBMS independent SQL tool
Yes, the slash is defined in the profile and is visible in the Alternate Delimiter field

Thomas Kellerer

unread,
Apr 30, 2021, 11:36:32 AM4/30/21
to sql-wo...@googlegroups.com
Hmm, I can not reproduce this.

If I have the following content in the editor:


alter table t1 modify (
c1 varchar2(20)
)
/

alter table t2 modify (
c2 varchar2(42)
)
/

This is correctly parsed and executed as two different statements.

Thomas
> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/94cdc41c-17d2-4964-930c-c41dcac180c5n%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/94cdc41c-17d2-4964-930c-c41dcac180c5n%40googlegroups.com?utm_medium=email&utm_source=footer>.

Chris Young

unread,
Apr 30, 2021, 12:16:32 PM4/30/21
to SQL Workbench/J - DBMS independent SQL tool
Let me see what I can do to isolate it on my end.  I will fall back to 127.2 and make sure it works there first, then see what I can figure out.

Chris Young

unread,
May 4, 2021, 7:15:41 PM5/4/21
to SQL Workbench/J - DBMS independent SQL tool
Thomas,

I get the correct behavior with 127.2, but not with 127.4.

Here's my profile:
profile.0003.alternate.delimiter=/
profile.0003.autocommmit=false
profile.0003.connection.properties=<properties><entry key="defaultRowPrefetch">1000</entry></properties>
profile.0003.driverclass=oracle.jdbc.OracleDriver
profile.0003.drivername=Oracle
profile.0003.fetchsize=1000
profile.0003.group=dev
profile.0003.idle.time=600000
profile.0003.ignore.drop.errors=true
profile.0003.password=*********
profile.0003.rememember.schema=true
profile.0003.script.connect=enableout;\
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
profile.0003.script.idle=select 1 from dual;
profile.0003.separate.connection=true
profile.0003.store.pwd=true
profile.0003.tags=
profile.0003.url=jdbc:oracle:thin:@oracle-d1:1524:d1
profile.0003.username=chris
profile.0003.workspace=chris.wksp

I do see a misspelling of rememember.  (Not sure if you want to fix that.)  :-)

I've varied the alternate delimiter character and that hasn't made a difference in behavior.  In your setup, are you using the properties or XML format for the profiles?  If XML, could that be an issue in why your 127.4 works but mine doesn't?

Let me know if there is something else I can do to help isolate this issue.

Thanks,
Chris

Chris Young

unread,
May 5, 2021, 2:17:29 PM5/5/21
to SQL Workbench/J - DBMS independent SQL tool
Thomas,

To simplify the case, here was the content of my editor:

create table abc (col_1 int null)
/

I tried it with and without a newline after the forward slash.  If I replace the slash with a semi-colon, it succeeds (ie, the syntax is otherwise correct).  Again, here's the error as listed in Messages tab:

An error occurred when executing the SQL command:
create table abc (col_1 int null)
/

Error at line 2:
ORA-00922: missing or invalid option
/
^
1 statement failed.

Execution time: 0.3s
(2021-05-05 11:13:57.336)


Again, it seems as if the alternate delimiter is not being found.  Is there a parameter for the Editor that might influence the searching of the Editor text causing the delimiter to not be found?  I am using Java Version: 11.0.10 (64-bit)

Thanks,
Chris

Thomas Kellerer

unread,
May 17, 2021, 4:54:34 AM5/17/21
to sql-wo...@googlegroups.com
I think I found the reason.

Can you please test 127.5? It should work with the new dev build.

Regards
Thomas


Chris Young schrieb am 05.05.2021 um 20:17:
> Thomas,
>
> To simplify the case, here was the content of my editor:
>
> create table abc (col_1 int null)
> /
>
> I tried it with and without a newline after the forward slash.  If I replace the slash with a semi-colon, it succeeds (ie, the syntax is otherwise correct).  Again, here's the error as listed in Messages tab:
>
> An error occurred when executing the SQL command:
> create table abc (col_1 int null)
> /
>
> Error at line 2:
> ORA-00922: missing or invalid option
> /
> ^
> 1 statement failed.
>
> Execution time: 0.3s
> (2021-05-05 11:13:57.336)
>
> Again, it seems as if the alternate delimiter is not being found.  Is there a parameter for the Editor that might influence the searching of the Editor text causing the delimiter to not be found?  I am using Java Version: 11.0.10 (64-bit)
>
> Thanks,
> Chris
> On Tuesday, May 4, 2021 at 4:15:41 PM UTC-7 Chris Young wrote:
>
> Thomas,
>
> I get the correct behavior with 127.2, but not with 127.4.
>
> Here's my profile:
> profile.0003.name <http://profile.0003.name>=d1
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/9ed6506b-fc0f-4910-b063-887bd6b1b50an%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/9ed6506b-fc0f-4910-b063-887bd6b1b50an%40googlegroups.com?utm_medium=email&utm_source=footer>.

Chris Young

unread,
May 17, 2021, 3:11:30 PM5/17/21
to SQL Workbench/J - DBMS independent SQL tool
Thomas,

Yes, that worked.  Thanks!

But... I'm using FlatLaf Dark and in 127.5, the Data Display result window is now showing grid lines whereas in 127.4 and prior I was able to remove the grid lines.  I can't recall what I did to remove the lines, so I wanted to know if there was a behavior change in 127.5 where they always show or is there an option I can set to remove the lines.  (I didn't see anything obvious.)  I can certainly open a new topic on this.

Thomas Kellerer

unread,
May 17, 2021, 3:19:17 PM5/17/21
to sql-wo...@googlegroups.com
Thanks, glad to hear that.

Regarding FlatLaf:

I think a grid without grid lines doesn't really make sense, so I changed the defaults for FlatLaf :)

But you can completely configure FlatFlaf yourself now, by creating the file FlatLaf.properties in the "ext" subdirectory.

To disable the gridlines just add the two lines:

Table.showHorizontalLines = false
Table.showVerticalLines = false

You can check https://www.formdev.com/flatlaf/properties-files/ for more information on the properties file.

The file from the ext directory is loaded after the built-in configurations (from FlatLaf itself and SQL Workbench), so it can override any default setting.

Regards
Thomas
> > profile.0003.name <http://profile.0003.name> <http://profile.0003.name>=d1
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/732a99b2-b14f-4999-b277-a9e2f694dbcdn%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/732a99b2-b14f-4999-b277-a9e2f694dbcdn%40googlegroups.com?utm_medium=email&utm_source=footer>.

Chris Young

unread,
May 17, 2021, 4:01:15 PM5/17/21
to SQL Workbench/J - DBMS independent SQL tool
Thanks.  The properties removed those unuseful, in-the-way grid lines.  :-)  Much appreciated!
Reply all
Reply to author
Forward
0 new messages