Exporting an Oracle Query to CSV With SQLcl From the Shell

SQL*Plus has been exporting Oracle data for decades, and for most of them the result was a column-aligned report that a human could read and a CSV parser could not. SQLcl is the command-line client Oracle ships to replace it for scripting, and it fixes the one thing that mattered here: it can write a real CSV, header and quoting included, from a single setting. If you are comparing it with the other engines in this series, the closest relatives are psql's \copy and the MySQL client; the Oracle version needs two extra steps because dates and numbers print according to the session, not the data.

The shortest export

Start an interactive session and run four lines. This is the whole idea, and everything after it is making it repeatable:

SET SQLFORMAT CSV
SPOOL orders.csv
SELECT * FROM orders ORDER BY order_id;
SPOOL OFF

SQLFORMAT is the SQLcl setting that decides how a result is drawn. CSV gives a header row, commas between fields, and double quotes around anything that is not a number. A double quote inside a value is doubled, which is the rule from RFC 4180 and the same one covered in how CSV quoting works. For three sample rows, the file looks like this:

"ORDER_ID","CUSTOMER","PLACED_ON","TOTAL","NOTE"
1001,"Acme Ltd","04-MAR-24",1234.5,
1002,"Smith, Jones and Co","05-MAR-24",89.9,"rush order"
1003,"Dupont ""Fils""","05-MAR-24",,"gift wrap"

Two things in that output are wrong for a file you intend to share. The header is in upper case and quoted, because Oracle folds unquoted identifiers to upper case, and the date is 04-MAR-24, a two-digit year in a format that depends on a setting. Both are fixable. A NULL comes out as an empty field, and a number like 1234.50 loses its trailing zero because Oracle prints numbers without padding, so 1234.5 is the real value and not a rounding.

Set the session before the query

The header comes from the column names, so give the columns the case you want with an alias in double quotes:

SELECT order_id AS "order_id", customer AS "customer", placed_on AS "placed_on", total AS "total", note AS "note"
FROM orders ORDER BY order_id;

Dates are the bigger problem. A DATE column has no format of its own. When a client prints it, it converts to text using the session parameter NLS_DATE_FORMAT, and that parameter defaults from the database's territory settings and from the environment of the machine you run on. The same script then produces 04-MAR-24 on one laptop and 04/03/2024 on another. Fix it for the session:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

The last line matters when the database or the client runs with a German or French territory, where NLS_NUMERIC_CHARACTERS is ,. and a total of 1234.5 prints as 1234,5. In a comma-separated file, that is two fields unless it is quoted, and it is a decimal comma to the person who loads it. Setting ., explicitly removes the question. Keep the DATE pitfall in mind: an Oracle DATE carries a time of day, so with YYYY-MM-DD the time is dropped. If the time matters, use YYYY-MM-DD"T"HH24:MI:SS for NLS_DATE_FORMAT as well. For the target formats other tools read cleanly, see writing dates and booleans so other tools read them.

Alternatively, convert in the query, which makes the SQL carry the format with it and survive a copy to another session:

SELECT order_id, customer, TO_CHAR(placed_on, 'YYYY-MM-DD') AS placed_on, total FROM orders ORDER BY order_id;

I prefer ALTER SESSION at the top of the script when many columns are dates, and TO_CHAR when one query has one or two. Mixing them is what produces files nobody can reproduce.

Keep the noise out of the spool

SPOOL writes everything the session prints, not only the result. Left at their defaults, three things leak into the file: the echoed statement, the row-count line (3 rows selected.) and the prompts. Turn them off before spooling:

SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET SQLFORMAT CSV
SPOOL orders.csv
SELECT /* the query */ order_id, customer FROM orders ORDER BY order_id;
SPOOL OFF
EXIT

TERMOUT OFF stops the rows scrolling on the screen, which makes a large export run much faster when a script is executing, since the terminal is often the slowest part. It only has an effect when the SQL is run from a script file and not typed at the prompt. TRIMSPOOL ON removes trailing blanks on each spooled line. If you want to avoid a statement echo of a long query, put it in a separate file and call it with @query.sql.

The statement-level alternative is the csv hint, which changes the format of one query without changing the session setting:

SELECT /*csv*/ order_id, customer FROM orders;

That is handy in a long script that also prints a human summary, because it leaves the rest of the output alone. For a delimiter other than the comma, SQLcl has a delimited format in the same family, and its arguments are listed in the SQLcl documentation under SET SQLFORMAT; for plain CSV the csv setting is the one to use.

Run it from a shell script

Interactive use is a way to try things. The reason to learn this is to put it in a nightly job. SQLcl's launcher is sql, and the -S flag runs it silently, with no banner:

sql -S export_user@//db.example.test:1521/ORCLPDB1 @export_orders.sql
echo "exit status $?"

The password is not on the command line, so a process listing does not show it. With no password in the connect string, SQLcl prompts for one, which is not what a cron job wants. Use an Oracle wallet or a connection name saved with connmgr for unattended runs, and keep credentials out of the script and out of version control; see automating a nightly CSV export with cron for the wrapper that handles the schedule, the lock and the failure email.

Exit status is where the script earns its keep. If export_orders.sql fails, say with ORA-00942: table or view does not exist, SQLcl prints the error into the spool file and carries on unless you tell it to stop. Add WHENEVER SQLERROR EXIT FAILURE at the top so a bad statement ends the run with a non-zero status, and WHENEVER OSERROR EXIT FAILURE for file problems. Without it, a cron job reports success on a file that contains an Oracle error message, and the load that follows ingests it. After the run, check the first line of the file, because it is the quickest test of whether the header is the one you expect:

head -n 1 orders.csv
wc -l orders.csv

Encoding, big values and large tables

SQLcl is a Java program, so the file encoding follows the JVM unless you set it. SET ENCODING UTF-8 before SPOOL fixes the encoding explicitly, and it is worth writing in every script, because a Windows machine whose default is a legacy code page will otherwise write ? for anything outside it. If the file lands wrong anyway, detecting the encoding of a CSV file is the next stop.

Large objects need care. A CLOB column prints up to the length set by SET LONG, so a long text field is truncated unless you raise it, and a BLOB should not go in a CSV at all. Newlines inside text fields are preserved inside the quotes, which is correct CSV and a surprise for a script that counts lines.

Speed is acceptable and not impressive. SQLcl fetches through JDBC row by row from the client's point of view, so an export of 10 million rows takes minutes and not seconds, and the work happens on a single connection. What helps is SET TERMOUT OFF and writing to local disk rather than a network share. For a table you export every day, split by a key range into several spool files in parallel sessions, as in exporting large tables to CSV.

When SQLcl is the wrong tool

If the file is going to another Oracle database, do not use CSV at all. Data Pump (expdp) writes a dump with types, constraints and partitions intact, and it is faster because the work happens on the server. If you need a CSV on the database server's own disk, with no client in the path, UTL_FILE can write one from PL/SQL, at the price of building the quoting yourself.

SQLcl is also the wrong choice when the bytes must be exact. A Java client formats numbers and dates with locale rules, and a long text column can be truncated by a setting you forgot. Check one known row end to end before trusting a nightly feed, and compare a COUNT(*) with the line count of the file (checking row and column counts).

For the loading direction, SQL*Loader reads the file you have just written. Oracle's own pages for the tool are the SQLcl product page and the SQLcl documentation; the format settings are listed there under SET SQLFORMAT.