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