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.
The SQLcl documentation where you can find other formats that are supported by the SQLFORMAT command such as JSON.