How to Export Oracle Database Queries to CSV Using SQLPLUS?

*Exporting Oracle Database Data to CSV using SQLPlus**

SQL*Plus offers a straightforward method for exporting data from Oracle databases to CSV files, eliminating the need for complex tools. This guide details how to effectively spool your queries to a CSV.

To generate a CSV file, configure the following SQL*Plus settings:

SET COLSEP ','     -- Use comma as column separator
SET PAGESIZE 0   -- Suppress header rows
SET TRIMSPOOL ON -- Remove trailing spaces
SET HEADSEP OFF  -- Optional; may improve heading formatting
SET LINESIZE X   -- X represents the total width of all columns
SET NUMW X       -- X defines the desired width for numeric fields (prevents scientific notation)

Next, create your SQL query and spool the results to a CSV file:

SPOOL myfile.csv

SELECT table_name, tablespace_name
FROM all_tables
WHERE owner = 'SYS'
  AND tablespace_name IS NOT NULL;

SPOOL OFF

The resulting myfile.csv will contain comma-separated values, free of extra whitespace.

For a more streamlined approach, consider using sed to remove any remaining whitespace before commas:

sed 's/\s+,/,/' myfile.csv > myfile_cleaned.csv

This command cleans the CSV, ensuring a consistent and readily importable format. The output is written to myfile_cleaned.csv.

The above is the detailed content of How to Export Oracle Database Queries to CSV Using SQLPLUS?. For more information, please follow other related articles on the PHP Chinese website!