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.
Table of Contents
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}
| Option | Value(s) | Default Value | Description |
|---|---|---|---|
| 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. |
| QUOTE | ON|OFF | OFF | If 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;