2 Replies Latest reply on Jun 17, 2015 8:40 AM by berx

    sqlcl 4.1.0.15.106.1020 does not quote numbers if decimal marker is ","

    berx

      with this simple testcase

       

      ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ' ;
      
      ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.RR HH24:MI:SS';
      
      drop table MAMUE ;
      
      CREATE TABLE MAMUE
         (    SCODE VARCHAR2(54), 
              "EDATE" DATE,
              "AMOUNT" NUMBER(21,3) NOT NULL ENABLE,
              "LDATE" DATE 
         );
      
      Insert into MAMUE 
         values ('ccc',
                 to_date('24.12.15','DD.MM.RR HH24:MI:SS'),
                 19/2,
                 null
                 );
      
      set sqlformat csv
      spool MAMUE.csv
      select * from MAMUE;
      spool off;
      rollback;
      

       

      I get a csv like this:

       

      "SCODE","EDATE","AMOUNT","LDATE"
      "ccc",24.12.15 00:00:00,9,5,
      

       

      a LOAD afterwards fails:

       

      SQL> LOAD MAMUE MAMUE.csv
      --Insert failed for row  1
      --LDATE: GDK-05040: input value not long enough for the date format
      --Row 1 data follows:
      INSERT INTO MAMUE(SCODE,EDATE,AMOUNT,LDATE)
      VALUES ('ccc',to_date('24.12.15 00:00:00'),9.0,to_date('5'));
      --Number of rows processed: 0
      --Number of rows in error: 1
      1 - WARNING: Load processed with errors
      

       

      But the problem is the CSV-file, as it does not distinguish between the , as field separator and decimal marker.

       

      hth

      Martin