A Simple Guide to Creating Comma-Delimited Reports in Oracle

Need to extract data from your Oracle database into a simple, shareable format? Look no further than comma-separated values (CSV) reports! These plain text files, where each data value is separated by a comma, are incredibly versatile, and are used for importing data into Excel, or Google Sheets or migrations. With lots of reporting tools available for the Oracle database, sometimes you just need a simple, old-fashioned csv report. There are multiple ways in writing these reports, to obtain the same output. Some of them are easier than others. Let’s look at 4 ways to generate your report.

Method 1: The Power of SQL*Plus SET MARKUP Commands

For quick, ad-hoc exports and scripts, SQL*Plus, provides an efficient way to create CSV files. The magic lies in the SET commands. You can use the SET MARKUP CSV ON, to generate CSV format outputs. You can also specify the delimiter character, in case you don’t want the comma, with the DELIMITER option.
A good thing also is to set the linesize big enough to accommodate the longest row in your output and to prevent wrapping of your data.

You will notice in the example below, that the heading of columns is enclosed in double quotes and it appears only as the first row, even if you have a pagesize set to a certain number of lines. Each character datatype column is also enclosed in double quotes. This is crucial for handling values that contain the delimiter, in this case comma. If you want the script to generate a csv file, you will need to use the SPOOL command. If you are using older versions of Oracle this setting might not be available to you.

SET MARKUP CSV ON DELIMITER ','
SET LINESIZE 3000

SPOOL employees_1.csv

SELECT employee_id, first_name, last_name, salary
FROM hr.employees
WHERE department_id = 30;

"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","SALARY"
114,"Den","Li",11000
115,"Alexander","Khoo",3100
116,"Shelli","Baida",2900
117,"Sigal","Tobias",2800
118,"Guy","Himuro",2600
119,"Karen","Colmenares",2500

spool off

Method 2: Using the COLSEP SQL*Plus Command

This command basically set the column separator to a comma. The output is still formatted very nicely for regular text file report. Same SQL as above, different looks of the output formatting with COLSEP. Columns are not enclosed in double quotes. More formatting is required on your part to get a nice report. The good thing about this command is that is available in older versions of Oracle.

SET COLSEP ','

SPOOL employees_2.csv

SELECT employee_id, first_name, last_name, salary
FROM hr.employees
WHERE department_id = 30;

spool off

EMPLOYEE_ID,FIRST_NAME		,LAST_NAME		  ,    SALARY
-----------,--------------------,-------------------------,----------
	114,Den 		,Li			  ,	11000
	115,Alexander	,Khoo			  ,	 3100
	116,Shelli		,Baida			  ,	 2900
	117,Sigal		,Tobias 		  ,	 2800
	118,Guy 		,Himuro 		  ,	 2600
	119,Karen		,Colmenares		  ,	 2500

Method 3: Using concatenation of characters.

This is really old school. Or maybe you use it if you are not aware of the above two methods. With this approach, you concatenate the columns with the comma character, to obtain a one column output with values separated by commas.

SPOOL employees_3.csv

SELECT employee_id || ',' || first_name || ',' || last_name || ',' || salary as output
FROM hr.employees
WHERE department_id = 30;

OUTPUT
-------------------------------
114,Den,Li,11000
115,Alexander,Khoo,3100
116,Shelli,Baida,2900
117,Sigal,Tobias,2800
118,Guy,Himuro,2600
119,Karen,Colmenares,2500

spool off

Method 4: Using SQL Developer

Honestly I found this one the easiest method, especially if you are generating one time reports. If the script only has to run once and not scheduled, then SQL Developer is a very friendly option. You connect to the database, write your query and run it. Once the query completes, right click the results and choose Export. You have the option to generate a csv file, and you can customize it in many ways.

There you have it! Four ways to generate csv reports. Which one is your favorite?