Set SQL format

set sqlformat

This is a quick note for something I have to lookup time and again.

And it's such a great feature, that every developer should know ... the set sqlformat statement!

With set sqlformat statement you can actually influence the result of your query. Or at least the format of that result.
E.g. you can make the output look like a CSV file. Or even better like a JSON file.
But also ... like an insert statement. So easy to quickly copy a line in a table. Of course, you have to take care of the unique constraints yourself. But hey, if you quickly want to create (duplicate) some data. This is a very nice way of doing it!

Query

select *
  from emp
 where empno = 7839
 ;

Normal result

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17/11/1981       5000          
        10

Example 1

set sqlformat csv

Result 1

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7839,"KING","PRESIDENT",,17/11/1981,5000,,10

Example 2

set sqlformat json

Result 2

{"items":[
{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"17/11/1981","sal":5000,"deptno":10}]}

Example 3

set sqlformat insert

Result 3

REM INSERTING into emp
SET DEFINE OFF;
Insert into "emp" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7839','KING','PRESIDENT',null,to_date('17/11/1981','DD/MM/YYYY'),'5000',null,'10');

Available formats (as far as I know now)

• csv
• html
• xml
• json
• insert – INSERT statements
• loader – sql*loader
• fixed
• default – obviously this clears the format and sets it back to the "normal" format (see "Normal result")

Happy to share