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 semicolons.
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 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