Generate CSV with SQL*Plus 12.2.0.1

From time to time it is required to get the rows of a table as comma separated values (CSV). With the release of Oracle 12.2.0.1, SQL*Plus has the ability to display the result of a query as CSV. This short blog post will demonstrate the usage of this new feature.

Demo Environment

To demonstrate the feature, I am using a table called BOOKS with three rows.

[SQL*Plus] CREATE TABLE BOOKS (
   BOOK_ID    NUMBER GENERATED ALWAYS AS IDENTITY,
   TITLE      VARCHAR2(250) NOT NULL,
   PAGES      NUMBER NOT NULL,
   AUTHOR     VARCHAR2(250) NOT NULL,
   CONDITION  VARCHAR2(4000),
   CONSTRAINT BOOKS_PK PRIMARY KEY (BOOK_ID),
   CONSTRAINT BOOKS_UQ UNIQUE (TITLE)
);

[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES ('The Hobbit', 322, 'J. R. R. Tolkien', 'Good condition');
[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES ('Harry Potter and the Philosopher''s Stone', 324, 'J.K. Rowling', 'New');
[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES ('The Holy Bible', 1328, 'Crossway Bibles', 'Good condition, but "mandatory" pages are missing');

Usage

SQL*Plus 12.2.0.1 introduced the new option MARKUP CSV for the SET command.

[SQL*Plus] SET MARK[UP] CSV {ON|OFF} DELIMI[TER] character QUOTE {ON|OFF}
OptionValue(s)Default ValueDescription
DELIMI[TER]Character,Character used to separate column values.

Only single characters are permitted, otherwise a "SP2-1660: Invalid option. Only a single character be specified." is raised.
QUOTEON|OFFOFFIf turned on, column values are surrounded by "". Double quotes in the text are escaped!

Only columns containing character data are surrounded by double quotes.

To change the column header or the format of its data, you can still use the following SQL*Plus commands.

  • COLUMN FORMAT
  • COLUMN HEADING
  • COLUMN NULL

Some SQL*Plus commands will be ignored after changing the markup to CSV.

Tests

Use quoting

In this test I will verify the quoting of the text columns – especially when a double quote is used inside the text.

[SQL*Plus] SET MARKUP CSV ON QUOTE ON
[SQL*Plus] SELECT * FROM BOOKS;

"BOOK_ID","TITLE","PAGES","AUTHOR","CONDITION"
1,"The Hobbit",322,"J. R. R. Tolkien","Good condition"
2,"Harry Potter and the Philosopher's Stone",324,"J.K. Rowling","New"
3,"The Holy Bible",1328,"Crossway Bibles","Good condition, but ""mandatory"" pages are missing"

As you can see, the non-text columns are not quoted. The double quotes used in the CONDITION column of the third book are escaped by a second double quote.

Use different delimiter

Instead of using the default delimiter, every other single character can be used.

[SQL*Plus] SET MARKUP CSV ON DELIMITER | 
[SQL*Plus] SELECT * FROM BOOKS; 

"BOOK_ID"|"TITLE"|"PAGES"|"AUTHOR"|"CONDITION"
1|"The Hobbit"|322|"J. R. R. Tolkien"|"Good condition"
2|"Harry Potter and the Philosopher's Stone"|324|"J.K. Rowling"|"New"
3|"The Holy Bible"|1328|"Crossway Bibles"|"Good condition, but ""mandatory"" pages are missing"

Works as designed. But be careful, the delimiter character used in the text columns are not escaped.

Command line

With the new SQL*Plus parameter -M, you are able to activate CSV markup for the current session. This is useful, when you want to create CSV files automatically.

[oracle] sqlplus -S -M "csv on" / as sysdba @/tmp/get_books.sql | sed '/^$/d'
"BOOK_ID","TITLE","PAGES","AUTHOR","CONDITION"
1,"The Hobbit",322,"J. R. R. Tolkien","Good condition"
2,"Harry Potter and the Philosopher's Stone",324,"J.K. Rowling","New"
3,"The Holy Bible",1328,"Crossway Bibles","Good condition, but ""mandatory"" pages are missing"

Comments:

  • Use -S parameter to run SQL*Plus in silent mode (no banner, no prompt, no echoing of commands)
  • Use sed to get rid of empty lines

Alternatives

Instead of using SQL*Plus to generate CSV, you can use alternatives like the SQL Developer or SQLcl – latter is shipped with Oracle Database 12.2.0.1 or available as a standalone download.

SQL Developer

In a SQL Developer worksheet, just set the SQLFORMAT or use the special comment in your query.

[SQL Developer] SET SQLFORMAT CSV
[SQL Developer] SELECT * FROM BOOKS;

[SQL Developer] SELECT /*csv*/ * FROM BOOKS;

References

Kommentar hinterlassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.