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:

<code class="language-sql">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)</code>

Next, begin spooling the output to a CSV file:

<code class="language-sql">SPOOL myfile.csv</code>

Then, execute your SQL query. For example:

<code class="language-sql">SELECT table_name, tablespace_name
FROM all_tables
WHERE owner = 'SYS'
  AND tablespace_name IS NOT NULL;</code>

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:

<code class="language-bash">sed 's/\s+,/,/' myfile.csv</code>

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!