Skip to Main Content

SQLcl issue with sqlformat delimited

jpbenezetApr 30 2019 — edited May 3 2019

CREATE TABLE DATA

(

  key VARCHAR2(10 CHAR),

  val VARCHAR2(500 CHAR)

);

set sqlformat delimited , [ ]

Insert into DATA (KEY,VAL) values ('01','00');

Insert into DATA (KEY,VAL) values ('02','"');

Insert into DATA (KEY,VAL) values ('03','""');

Insert into DATA (KEY,VAL) values ('04','''');

Insert into DATA (KEY,VAL) values ('05','''''');

Insert into DATA (KEY,VAL) values ('06','test "a" val ''b'';');

Insert into DATA (KEY,VAL) values ('07','$');

Insert into DATA (KEY,VAL) values ('08','\"');

Insert into DATA (KEY,VAL) values ('09','\''');

Insert into DATA (KEY,VAL) values ('10','What');

Insert into DATA (KEY,VAL) values ('11','WhAt');

Insert into DATA (KEY,VAL) values ('12','whAt');

SQL> select  * from data;

[KEY],[VAL]

[01],[00]

[02],[""]

[03],[""""]

[04],[']

[05],['']

[06],[test ""a"" val 'b';]

[07],[$]

[08],[\""]

[09],[\']

[10],[What]

[11],[WhAt]

[12],[whAt]

Issue 1 => the sqlformat option (delimited) duplicate quotes as it's the case without the (delimited) option but it's not necessary in this case.

SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure> allows you to set a custom delimited format.

should be

SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure> <escape>

allows you to set a custom delimited format.

With <escape> the value of the escape character added to the row data when exporting

eg :

set sqlformat csv == SET SQLFORMAT DELIMITED , " " "

and if the string contain the escape character it should be duplicated

Issue 2  => The load should be impacted by sqlformat option to be able to load the data exported with sqlformat

LOAD [schema.]table_name[@db_link] file_name

proposal 1 => alow to limit the number of error to a specific number

LOAD [schema.]table_name[@db_link] file_name [10] => limit the number of errors to 10.

proposal 2 => allow the query result to be redirected to a file without any other server output

UNLOAD [schema.]table_name[@db_link] file_name

the unload function will take care of the set sqlformat option to unload the datas to file_name

Comments
Post Details
Added on Apr 30 2019
4 comments
1,134 views