How to Export SQL Query Results to CSV using SQLPLUS?

*Exporting SQL Query Results to CSV with SQLPlus**

This guide details how to efficiently export SQL query results to a CSV file using SQL*Plus, a particularly useful technique when alternative SQL clients or programming languages aren't readily available.

Steps:

1. Configure Output Settings: Begin by adjusting SQL*Plus's output formatting:

   • Set the column separator to a comma: SET COLSEP ','
   • Disable pagination: SET PAGESIZE 0
   • Remove trailing spaces: SET TRIMSPOOL ON
   • Adjust column width (optional): SET LINESIZE X and SET NUMWIDTH X (replace X with your desired width).

2. Start Spooling: Initiate the output file creation using the SPOOL command: SPOOL myfile.csv

3. Run Your Query: Execute the SQL query to retrieve the data you wish to export. For example:

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

4. Preview (Optional): Before proceeding, you can review the output format.

5. Stop Spooling: Once the query completes, stop the spooling process with: SPOOL OFF

6. Optional: Advanced Space Cleaning: For a perfectly formatted CSV, use sed to remove any extra spaces preceding commas: sed 's/s ,/,/g' myfile.csv > myfile_cleaned.csv This creates a new, cleaned CSV file (myfile_cleaned.csv).

This process ensures a clean and easily importable CSV file containing your SQL query results.

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