1 Reply Latest reply on Mar 15, 2020 11:34 PM by user6350053

    Exporting numbers using SQLcl while retaining the original decimal and thousand separator

    3841192

      Good afternoon,

       

      The last few days I've been struggling to export data using SQLcl while retaining the original decimal and thousand separator. The database I am exporting from uses American NLS settings (NLS_NUMERIC_CHARACTERS = '.,') and the machine I am running SQLcl on uses Dutch format settings (a comma as the decimal separator). The script I execute with SQLcl looks like this:

       

      CONNECT "&1"/"&2"@&3

      SET SQLFORMAT DELIMITED |

      SET FEEDBACK OFF

      SET TERMOUT OFF

      SPOOL #TARGET_LOCATION

       

      SELECT

      number_column
      FROM table

      ;

       

      SPOOL OFF   

      EXIT -1

       

      If I export like this, the decimal separator in the number is changed from a dot to a comma, and the number is not encapsulated in quotes. I have tried to find information on the defaults forced by SET SQLFORMAT,
      specifically whether it uses the NLS settings from the source database, or the regional settings of the system the export is run on, but I have been unable to find any.  The following is the only solution I have found so far:

       

      SELECT

      TRIM(TO_CHAR(<number_column>,'999999999999D99','NLS_NUMERIC_CHARACTERS = ''. '''))

      FROM table

       

      While I am glad to have found a working solution, it seems very unlikely that there isn't a (much) easier way to achieve the same result. Would someone be so kind to point it out me?

       

      Kind regards,

       

      Marc

       

      Message was edited by: 3841192, completed some sentences