Forum Stats

  • 3,727,354 Users
  • 2,245,375 Discussions
  • 7,852,753 Comments

Discussions

Setting delimiter for LOAD to semicolon. Glitch?

Erik van Roon
Erik van Roon Member Posts: 57 Red Ribbon

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:

[email protected]>@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

[email protected]>set loadformat default
Load Format Cleared
[email protected]>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

[email protected]>set loadformat delimited delimiter ;;
Invalid Load Format option specified delimiter. Remaining options are ignored

[email protected]>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

[email protected]>set loadformat default
Load Format Cleared
[email protected]>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

[email protected]>set loadformat delimited delimiter ;;

[email protected]>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
berx
Sign In or Register to comment.