This post will show you how to use the SQLFORMAT command from SQL Developer to transform a query output to CSV.

Out of the box CSV

Enter the following into SQL Developer, I am using SQL Developer version 18.1 and execute it as a script by pressing F5.

set sqlformat csv
select e.employee_id, 
       e.last_name, 
       e.salary
  from employees e
 where e.department_id = 50;

and here is the output:

"EMPLOYEE_ID","LAST_NAME","SALARY"
198,"OConnell",2600
199,"Grant",2600
120,"Weiss",8000
121,"Fripp",8200
122,"Kaufling",7900
...

What customisation options are available?

The SQLFORMAT DELIMITED option can be used to change the delimiter and the enclosures. The command takes the following format:

SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure> 

Here is the same query as above changed to use # as the delimiter and single quote enclosures.

set sqlformat csv
set sqlformat delimited # ' '
select e.employee_id, 
       e.last_name, 
       e.salary
  from employees e
 where e.department_id = 50;

'EMPLOYEE_ID'#'LAST_NAME'#'SALARY'
198#'OConnell'#2600
199#'Grant'#2600
120#'Weiss'#8000
121#'Fripp'#8200
122#'Kaufling'#7900

Alternatives to SQLFORMAT?

Check out the answers to this StackOverflow question.

Need more help?

Please get in touch and I will be glad to help.

Acknowledgements

The SQLcl documentation where you can find other formats that are supported by the SQLFORMAT command such as JSON.