SQLFormat - CSV, JSON, XML - Format output of SQL in SQLDeveloper

A lot of time we want results of our SQL statements in CSV, JSON, XML formats or wish to generate insert statement for our data. For these requirements, we can use SET SQLFormat in our favorite database tools - SQL Developer and SQLcl.

Oracle has provided a lot of SQLFormat options, the most commonly used are -

SET SQLFORMAT CSV
SET SQLFORMAT CSV

SET SQLFORMAT JSON

SET SQLFORMAT INSERT
SQL Developer also provides us option to directly put the SQLFORMAT in the comment in SQL itself as

Apart from these wonderful SQLFORMAT, we can use XML, HTML and LOADER with SQLDeveloper and SQLcl. There are others too but are not commonly used, and if you want to learn, explore the Oracle Documentation.

Related Posts:
- Oracle SQL Developer - Autotrace - Insufficient Privileges
- SQL Loader Express Mode - Loading data in Oracle database can't be more easy
- JSON in Oracle Database with Examples
- Top 18 features of Oracle 18c
- Oracle 12c Partitioning New Features - Top 10
Labels: ,

6 comments:

  1. Pandey PiyushOctober 8, 2018 at 3:46 PM

    Hello Nimish,

    Is there any way to change the position of the column , for E.g:-

    TableA
    --
    Name
    Tel
    Email
    Address
    I want to add a new column (mobile) in between Tel & Email:

    TableA
    --
    Name
    Tel
    Mobile
    Email
    Address

    ReplyDelete
  2. AnonymousOctober 16, 2018 at 5:48 PM

    Hi Nimish,
    I am using Oracle 12.1.0.1 and when I am trying to execute the “SET SQL FORMATCSV” it is throwing an error i.e “SP2-0158: unknown SET option "SQL" , Please share your comments.

    ReplyDelete
    Replies
    1. AvijitMay 9, 2020 at 9:50 PM

      instead of "SET SQL FORMATCSV", run "SET SQLFORMAT CSV". Note this, it works only in SQLDeveloper. not in SQLPLUS. Also, run your select query using F5 (and not F9).

      Delete
  3. UnknownDecember 7, 2018 at 11:25 PM

    Even I am receiving the error when i use set sqlformat csv

    ReplyDelete
  4. UnknownMarch 3, 2019 at 12:49 PM

    same error

    ReplyDelete
  5. Stellar Gold CoinDecember 13, 2023 at 7:07 AM

    Thanks for writiing

    ReplyDelete

Subscribe to: Post Comments (Atom)