1 2 Previous Next 16 Replies Latest reply on Apr 4, 2016 7:11 PM by Gary Graham-Oracle Branched to a new discussion.

    SQLCL csv output is different from SQLPLUS csv output

    3063144

      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

        1 2 Previous Next