X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Fast Generation of CSV and JSON from Oracle Database

Christopher Jones
Senior Principal Product Manager

Want a fast way to spool CSV or JSON from Oracle Database? Check out the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database 12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud Database Service.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus 12.2. Start with this script, t.sql:

  set feedback off
  select department_id, department_name from departments where department_id < 110;
  exit

Executed traditionally you get formatted output:

  SQL> @t.sql
  DEPARTMENT_ID DEPARTMENT_NAME
  ------------- ------------------------------
            10 Administration
            20 Marketing
            30 Purchasing
            40 Human Resources
            50 Shipping
            60 IT
            70 Public Relations
            80 Sales
            90 Executive
           100 Finance

Running it with the new CSV mode:

  SQL> set markup csv on
  SQL> @t.sql
  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"

Simple!

The full CSV syntax is:

  SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

The SET MARKUP option can also be enabled from the command line with the -m option:

  $ sqlplus -s -m 'csv on' cj@'"localhost/pdb1"' @t.sql
  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"

(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines, making output faster to generate. And using the -m 'csv on' option additionally invokes a faster I/O subsystem, and also automatically sets some SET options to the values of another new option sqlplus -f. Two of the changes are increasing SET ARRAYSIZE to 100 and SET ROWPRETCH to 2.

Increasing SET ARRAYSIZE allows larger amounts of data to be returned in each low level request to the database, thus improving overall efficiency. Having SET ROWPRETCH to 2 reduces the database 'round trips' required between SQL*Plus and the database for queries that return only single rows. You should adjust the values of these, and other, settings to suit your data size and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to Oracle Database 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational table output into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and with quoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

  set heading off
  set feedback off
  select json_object ('deptId' is d.department_id,
         'name' is d.department_name) department
  from departments d
  where department_id < 110;
  exit

Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@localhost/pdb1 @t2.sql
  {"deptId":10,"name":"Administration"}
  {"deptId":20,"name":"Marketing"}
  {"deptId":30,"name":"Purchasing"}
  {"deptId":40,"name":"Human Resources"}
  {"deptId":50,"name":"Shipping"}
  {"deptId":60,"name":"IT"}
  {"deptId":70,"name":"Public Relations"}
  {"deptId":80,"name":"Sales"}
  {"deptId":90,"name":"Executive"}
  {"deptId":100,"name":"Finance"}

Summary

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easy access to CSV and JSON data. If you don't yet have 12.2, or you want to create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledb driver for Node.js.

Join the discussion

Comments ( 5 )
  • Phuoc Tran Tuesday, June 19, 2018
    Hello,

    I'm facing problem below:

    SQL> set markup csv on
    SP2-0158: unknown SET option "csv"

    Can you please advice?
    Phuoc
  • Christopher Jones Tuesday, June 19, 2018
    @Phuoc Tran make sure you are using SQL*Plus 12.2
  • Phuoc Tran Tuesday, June 19, 2018
    Hello Christopher Jones,

    Thanks for your help, I will try update
  • Rolf Mikkelson Tuesday, December 11, 2018
  • Christopher Jones Tuesday, December 11, 2018
    Fixed. Thanks.

    This post is pretty old, so make sure to check out the latest documentation for all the new features.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.