How Can I Export SQLPLUS Query Results to a CSV File?

Exporting SQLPLUS Query Results to CSV

This guide details how to export SQLPLUS query results into a CSV file using a series of commands.

First, set the necessary SQLPLUS parameters for CSV formatting:

SET COLSEP ','     -- Comma as column separator
SET PAGESIZE 0   -- Suppress header rows
SET TRIMSPOOL ON -- Remove trailing spaces
SET HEADSEP OFF  -- Suppress header lines
SET LINESIZE X   -- Adjust total column width (replace X with desired value)
SET NUMW X       -- Adjust numeric field width (replace X with desired value)

Next, begin spooling the output to a CSV file:

SPOOL myfile.csv

Then, execute your SQL query. For example:

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

The query results will be written to myfile.csv.

Finally, for optimal CSV formatting, use a post-processing command (like sed) to remove any leading whitespace before commas:

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

This ensures a clean, consistent CSV structure. Remember to replace X in SET LINESIZE and SET NUMW with appropriate values based on your data.

The above is the detailed content of How Can I Export SQLPLUS Query Results to a CSV File?. For more information, please follow other related articles on the PHP Chinese website!