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