Complete Guide to Generating CSV Files Using SQL*Plus

SQL*Plus CSV Output Overview

In Oracle database management environments, SQL*Plus serves as the standard command-line tool with robust data export capabilities. Converting query results to CSV (Comma-Separated Values) format is a common business requirement, particularly in data exchange and report generation scenarios. Based on actual Q&A data and reference documentation, this article systematically introduces the technical implementation of generating CSV files using SQL*Plus.

Simplified Method in Oracle 12.2

For users running Oracle 12.2 or later versions, SQL*Plus introduces the set markup csv on command, significantly simplifying CSV output configuration. This command automatically handles complex settings such as field separation and format optimization, requiring users to only execute the following simple steps:

set markup csv on
spool myfile.csv
select table_name, tablespace_name 
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;
spool off

This method automatically handles configurations including: comma separation between fields, removal of page headers, and output format optimization. Compared to traditional methods, the code is more concise with significantly reduced maintenance costs.

Detailed Traditional Configuration Method

For earlier Oracle versions or scenarios requiring finer control, traditional configuration methods remain applicable. The following code demonstrates a complete configuration solution:

set colsep ','     -- Set field separator to comma
set pagesize 0     -- Disable pagination display
set trimspool on   -- Remove trailing whitespace
set headsep off    -- Disable header separator
set linesize 200   -- Set line width, adjust based on actual field widths
set numw 20        -- Set numeric field width to avoid scientific notation

spool output.csv

select table_name, tablespace_name
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;

spool off

In-depth Configuration Parameter Analysis

colsep Parameter: Defines the separator character between fields, typically a comma for CSV format. Some scenarios may require alternative separators such as pipe | or semicolon ;.

pagesize Control: Setting to 0 completely disables pagination, preventing insertion of duplicate header rows in output files. To retain headers, set pagesize to a value larger than the expected record count.

linesize Optimization: This parameter should be set to the sum of all output field widths, ensuring data is not accidentally truncated. Calculation example: if three fields have widths of 30, 50, and 20 characters respectively, linesize should be at least 100.

trimspool Function: When enabled, automatically removes trailing whitespace characters from each line, which is crucial for maintaining CSV file cleanliness.

Advanced Formatting Techniques

For data fields containing special characters, additional processing is required:

set echo off
set feedback off
set verify off
set trimspool on
set pagesize 0
set linesize 1000

spool formatted_output.csv

select '"' || replace(table_name, '"', '""') || '",' ||
       '"' || replace(tablespace_name, '"', '""') || '"'
  from all_tables
 where owner = 'SYS';

spool off

This method ensures quotes within fields are properly escaped, complying with CSV standard format requirements.

Script Execution Solution

To improve efficiency and repeatability, it's recommended to encapsulate configuration and queries into script files:

-- save as export_csv.sql
set colsep ','
set pagesize 0
set trimspool on
set headsep off
set linesize 200
set numw 20
set feedback off
set echo off

spool &1

select table_name, tablespace_name
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;

spool off
exit

Execute using: @export_csv.sql output.csv

Performance Optimization and Considerations

When handling large-volume data exports, pay attention to the following key points:

• Memory Management: Appropriately adjusting the arraysize parameter can improve processing performance for large result sets
• File Size Limitations: Some system environments have single file size limits, requiring consideration of split file output
• Character Encoding: Ensure output file character encoding is compatible with target systems
• Error Handling: Add error checking mechanisms in scripts to ensure export process reliability

Version Compatibility Considerations

Different Oracle versions of SQL*Plus have varying feature support:

• Oracle 12.2+: Supports set markup csv on simplified command
• Oracle 11g and earlier: Require traditional configuration methods
• iSQL*Plus: Web version may not support spool functionality, requiring alternative solutions

By appropriately selecting technical solutions suitable for the current environment, efficient and reliable data export from SQL*Plus to CSV can be achieved.