SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end For reasons, I am using the following here-doc approach to export data to CSV.
$ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh &gt; arca.csv &lt;&lt;EOF
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET NEWPAGE NONE
SELECT
    *
FROM ORDER.SALES
WHERE ROWNUM &lt; 100;
EOF

This however produces a CSV file with empty first line and with unquoted carriage return at the end, as complained by psql tool when I want to load to Postgres
Below error happens because the CSV file has blank row as first line. The column names are read as data instead, causing type error.
$ psql -c &quot;\COPY raw_data.arca FROM arca.csv CSV HEADER&quot;
ERROR:  invalid input syntax for type date: &quot;FILE_DATE&quot;
CONTEXT:  COPY arca, line 2, column file_date: &quot;FILE_DATE&quot;

If I remove the blank row manually, the following error then occurs because of carriage return character at the end of data
$ psql -c &quot;\COPY raw_data.arca FROM arca.csv CSV HEADER&quot;
ERROR:  unquoted carriage return found in data
HINT:  Use quoted CSV field to represent carriage return.
CONTEXT:  COPY arca, line 101

How do I correctly use sqlcl with here-doc approach to produce CSV file without first blank line, and carriage return ending?
Why don't you just use the UNLOAD TABLE command ?
sql -s scott/tiger@localhost:1521/spatialdb &lt;&lt;EOF
unload table us_cities
EOF

Or even this simple one-liner:
echo &quot;unload table us_cities&quot; | sql -s scott/tiger@localhost:1521/spatialdb

This creates file US_CITIES_DATA_TABLE.csv in your current directory.
Check the help about the UNLOAD command to get details about various options for formatting the output.