How to Export a Table to CSV in Oracle

Galaxy Glossary

How do I export an Oracle table to a CSV file using SQL*Plus?

Exporting a table to CSV in Oracle means using SQL*Plus formatting commands and SPOOL to write query results to a comma-separated file.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I export an Oracle table to CSV with one script?

Open SQL*Plus, apply formatting commands, start SPOOL, run a SELECT, then stop spooling. The resulting file is a clean CSV ready for Excel or any ETL job.

Which SQL*Plus commands format perfect CSV output?

Use SET COLSEP ',' for commas, SET PAGESIZE 0 and SET FEEDBACK OFF to remove blank lines, SET HEADING ON for column names, and SET TERMOUT OFF to hide screen output while spooling.

What does a full export script look like?

-- export_products.sql SET COLSEP ',' SET PAGESIZE 0 SET FEEDBACK OFF SET TERMOUT OFF SPOOL /tmp/products.csv SELECT id, name, price, stock FROM products ORDER BY id; SPOOL OFF EXIT

How can I handle commas or newlines inside text columns?

Wrap sensitive columns in double quotes: SELECT '"' || name || '"' AS name. For dates use TO_CHAR(order_date,'YYYY-MM-DD') so spreadsheet tools read them correctly.

Best practices for reliable exports

Always ORDER BY a stable key, place the file in a directory the OS user can write to, and timestamp filenames (products_&YYYYMMDD..csv) to avoid overwriting.

How do I automate daily exports?

Create a shell script that calls sqlplus -s user/pass@db @export_products.sql and schedule it with cron or Windows Task Scheduler. Pass dates or table names with substitution variables (&1, &2) to reuse the script.

Can PL/SQL generate CSV directly?

Yes—use UTL_FILE to open a server-side file, loop through a cursor, build comma-separated strings, and write each line. SQL*Plus is simpler for ad-hoc needs; PL/SQL suits fully automated jobs.

Why How to Export a Table to CSV in Oracle is important

How to Export a Table to CSV in Oracle Example Usage


-- Export an ecommerce Orders table
SET COLSEP ','
SET PAGESIZE 0
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/orders_20240101.csv
SELECT id,
       customer_id,
       TO_CHAR(order_date,'YYYY-MM-DD') AS order_date,
       total_amount
FROM   orders
WHERE  order_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-01'
ORDER  BY id;
SPOOL OFF
EXIT

How to Export a Table to CSV in Oracle Syntax


-- SQL*Plus one-liner syntax
SET COLSEP ','                 -- column separator
SET PAGESIZE 0                 -- remove page breaks
SET FEEDBACK OFF               -- hide row count
SET TERMOUT OFF                -- silence console
SPOOL <file_path>              -- begin writing
SELECT <column_list>
FROM   <table_or_join>
[WHERE <filter>]
[ORDER BY <column>];
SPOOL OFF                      -- finish
EXIT                           -- close SQL*Plus

Common Mistakes

Mistake: Forgetting SET COLSEP and getting fixed-width output. Fix: Add "SET COLSEP ','" before SPOOL so each column is comma-separated.
Mistake: Leaving PAGESIZE at its default, causing page headers in the CSV. Fix: "SET PAGESIZE 0" eliminates page and header breaks.

Frequently Asked Questions (FAQs)

Do I need DBA access to spool a file?

No. You only need read access to the table and OS write permission to the directory where the CSV is created.

Can I include the header row?

Yes. Keep SET HEADING ON (default). To exclude headers use SET HEADING OFF.

How big a table can SQL*Plus export?

Millions of rows are fine; SQL*Plus streams results. Disk space and network speed are the limiting factors, not SQL*Plus itself.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie