Skip to Main Content

Setting delimiter for LOAD to semicolon. Glitch?

Erik van RoonOct 11 2020

Awesome enhancement in SQLcl 20.2 where we now can influence the behavior of the LOAD commant through two variables:
set load
set loadformat
With "set loadformat" we can now, among many other things, set a custom delimiter other than the default comma.
This works pretty good.
But when I tried to set it to a semicolon, the most common delimiter that is not a comma, I noticed something strange. First of all it took some experimenting to find out how to 'escape' the semicolon so it's not treated as an 'end-of-command' character. This appeared to be just making it two double quotes.
That actually was the first thing I tried. The reason it still took me longer than nescessary is because it didn't seem to work! That's because,
It DOES work when done in a script,
But it DOESN'T work from the command line if you execute it with <ENTER>
However, from the command line, but executing it with <CTRL-R>, it suddenly DOES work.
I can hardly imagine this is intentional behavior, so I hope this will be fixed in the next version.
If it is intentional than I think it should be mentioned it in the help. And a mention in the help of the way of escaping the semicolon would be helpfull too I think.
Demo:
If I have a script that looks like this:

prompt ================
prompt Clear LoadFormat
prompt ================

set loadformat default
show loadformat

prompt
prompt =======================
prompt Set SemiColon delimiter
prompt =======================

set loadformat delimited delimiter ;;
show loadformat

it all works as expected:

ERO@EVROCS>@loadformat.sql

================
Clear LoadFormat

Load Format Cleared
default

format CSV
column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

=======================
Set SemiColon delimiter

format CSV
column_names on
delimiter ;
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

If I type the same commands at the command line, executing by pressing <ENTER> I get an "Invalid Load Format option specified delimiter" error

ERO@EVROCS>set loadformat default
Load Format Cleared
ERO@EVROCS>show loadformat
default

format CSV
column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

ERO@EVROCS>set loadformat delimited delimiter ;;
Invalid Load Format option specified delimiter. Remaining options are ignored

ERO@EVROCS>show loadformat

format CSV
column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

But if I do the same thing, but use <CTRL-R> to execute the statement that sets the delimiter to semicolon, it works

ERO@EVROCS>set loadformat default
Load Format Cleared
ERO@EVROCS>show loadformat
default

format CSV
column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

ERO@EVROCS>set loadformat delimited delimiter ;;

ERO@EVROCS>show loadformat

format CSV
column_names on
delimiter ;
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator
skip_rows 0
skip_after_names

Comments
Post Details
Added on Oct 11 2020
0 comments
83 views