Hello,
I try to replace my sqlplus script that generate a csv file,
by a new one generated by sqlcl, since sqlcl seems to manage this "natively"
However, I encounter several problems, it is not so easy... (it is not possible, actually, to replace sqlplus by sqlcl to generate a csv file)
there is several differences with the ouput made with my script in sqlplus. let me explain
NB: please note I'm french so:
* default csv separator is ; (but I could accept that separator is ,)
* decimal separator is ','
SQLplus script
set echo off
set feedback off
set term off
set timing off
set head off
set Embedded on
set pagesize 0
set pause off
set NEWP NONE
set SPACE 0
set TAB OFF
set recsep off
set underline off
COLUMN resource_type FORMAT A10 HEADING TYPEE
alter session set nls_date_format = 'dd/mm/yyyy';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
set LINESIZE 30000
set trimspool on
Spool test.csv
SELECT '"sysdate";"COL1";"COL2";"LENGTH"' from dual;
SELECT
'"' || sysdate ||'";"'
'"' || trunc(sysdate) ||'";"'
|| TO_CHAR(sysdate,'DD/MM/YYYY HH24:MI:SS') ||'";"'
|| RM.LENGTH || '"'
FROM
TABLE RM
WHERE
RM.CODE='ABCD';
spool off
exit
SQLCL script
set echo off
set feedback off
set term off
set timing off
set pause off
set trimspool on
set sqlformat csv
Spool test2.csv
SELECT
sysdate,
trunc(sysdate),
TO_CHAR(sysdate,'DD/MM/YYYY HH24:MI:SS'),
RM.LENGTH
FROM
TABLE RM
WHERE
RM.COL='ABCD'
;
spool off
exit
SQLplus CSV output (and result is correct)
"sysdate";"COL1";"COL2";"LENGTH"
"05/11/2015";"05/11/2015";"05/11/2015 11:11:27";"6,07"
SQLCL CSV output
<blank line>
"SYSDATE","TRUNC(SYSDATE)","TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS')","LENGTH"
05/11/15 11:12:12,000000000,05/11/15 00:00:00,000000000,"05/11/2015 11:12:12",6,07
--------
You can notice in the SQL CL output
* a blank first line (???)
* sysdate fields is followed by ,0000000, which breaks the structure of csv file. Don't know why this appears.
* sysdate value is fine (date + hour) in sqlcl, but Trunc(sysdate) should not show time (minor)
* in sqlplus while I have not used instruction ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', '
the numeric data is 6,07 (while it should be 6.07, like in default sqlplus output. (In sqlplus in order to have 6,07, I need to put the NLS_NUMERIC instruction above))
* in sqlplus, only char data are enclosed in brackets. In my case it would be necessary to have each data enclosed to be sure that CSV strucutre is safe.
So too many problems,I currently cannot use sqlcl to generate a csv result...
Don(t know if it is the place to talk about SQL imporvement, but, It would be great if sqlcl could be improved :
* the date issue (,000000) would be fixed (unless there is a workaround ?)
* Default numeric output should be with '.'. And after having ability to change decimal separator, like in SQLPLUS
* We would have abilty to choose if data must be enclosed or not in brackets (even for numeric/date data)
* we would be able to change the csv separator
* no first blank line ould be generated (minor)
Thank you for any suggestion you could provide, because for now, I cannot use sqlcl to output to csv, while it have very promising output features