Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

More SET SQLFORMAT fun in SQLcl

1 Min Read

DELIMITED text files are popular ways of passing data around.

CSV anyone? The C stands for ‘Comma’ – regardless of what your smug European friends may have told you 😉 #TonguePlantedFIRMLYInCheek

Anyways, in SQL Developer, when using the export dialog to get a DELIMITED export for your dataset, you can set the delimiter and the string enclosure for your columns.

Don't like commas as delimiters? Set your own.
Don’t like commas as delimiters? Set your own.

So in the command line interface AKA SQLcl:

The first argument defines the delimiter, the 2nd defines the left enclosure, and the 3rd defines the right enclosure.
The first argument defines the delimiter, the 2nd defines the left enclosure, and the 3rd defines the right enclosure.

So you could have BEER emoji separated values files…

Speaking of SET SQLFORMAT…

Back in October, we made a tweak to the ANSICONSOLE. It’s VERY configurable now in terms of how you want numbers displayed. Don’t miss this awesome post from @krisrice.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

13 Comments

  1. Robin Reply

    Hi Jeff,

    is it possible to remove the left and right enclosure, so that the output will look like:

    C1;C2;C3
    1;John;Doe

    Otherwise i have to trim the output.

    Thanks

    Robin

    • bruno

      Hi Jeff,

      I have the same doubt. Can you help us?

      Thanks
      Bruno

    • thatjeffsmith Post Author

      not in SQLcl – but if you do it in the GUI, you can export a result set and set your string enclosures to null in the Export dialog

  2. Jørgen Rasmussen Reply

    Hi Jeff!

    Uh oh – I’m one of those pesky europeans insisting comma is decimal point, so my default for NLS_NUMERIC_CHARACTERS is always comma/point.

    I’m quite fond of “set sqlformat” – but quite often I run into problems if I don’t consider my NLS_NUMERIC_CHARACTERS settings.

    Observe this (SQLcl: Release 4.2.0.15.349.0706):

    SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

    A B C
    ———- ———- ———-
    1 2 3,4

    Everything is nice and as I usually want my data displayed on the console when it contains numbers with decimals.

    SQL> SET sqlformat delimited , ” ”
    SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

    “A”,”B”,”C”
    1,2,3,4

    Notice suddenly 4 columns instead of 3

    I always have to remember:
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘.,’;

    SQL> SET sqlformat delimited , ” ”
    SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

    “A”,”B”,”C”
    1,2,3.4

    And then toggle back afterwards:
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘,.’;

    My wish could be that SQLcl either:

    a) numeric columns with contents conflicting with delimiters are enclosed as a string: 1,2,”3,4″

    or

    b) delimiter character is automatically switched to point and then back again after execution of statement

    Thank you for a great product
    Regards
    Jørgen

  3. Alex Reply

    Hi,

    I’m new to SQLcl, I’m exploring it’s use to migrate a large amount of clob (xml) data to Postgres because of it’s formatting features. Unfortunately, right out of the gate I’m noticing it’s slapping double quotes around the data. Is there anyway to modify this behavior? I would expect strings to be single quotes, and numbers to have neither.

    Thanks.

  4. ricardo fonseca Reply

    Hi Jeff,

    I have been using quite a bit sqlcl so far and the options to export data in various format and it works great !

    Now in regards of viewing the data, I found that sometimes it takes some time to view the information you need. I explain myself ., I have tables with multiple columns ( more than 80 – 100 ) . Sometimes I m “browsing” the tables data without knowing which field have the information I want.

    In such scenario I found mysql / mariadb command line \G option to be really intresting by somehow unpivoting record data attributes to horizantally presentation it gives you a grasp of what ‘s inside very fast so afterwards you can proceed with a more selective attributes approach.

    I don’t know if it’s possible or something but it I think it will be GREAT to ad this capability to sqlcl . Maybe by adding a new sqlformat ? Personnaly I will call it “unpivot” 😉

    MariaDB [mysql]> select * from db\G
    *************************** 1. row ***************************
    Host: localhost
    Db: crossword
    User: crossword
    Select_priv: Y
    …..
    …..

    Thanks for reading.

    Ricardo

    • ricardo fonseca

      Nice , I ll try that !

      BTW : Just to let you know , on your site the inputbox below search posts ( the google search thingy ) when you write text on it the color is white and as the background is white as well . It s hard to read what we are typing

  5. R Eamon Reply

    It would be nice to:

    * control the record delimiter (perhaps that is set another way?)
    * don’t enclose a field if it does not contain a record or field delimiter or an enclosure char (left and right are almost always the same–indeed I’ve never seen them not be the same); or the corollary: only enclose a column in a record if the data in that record column contains a character with special meaning (the delimiters and enclosures)

    • Jason

      I agree. Is there any way to get rid of the enclosure characters all together?

  6. ricardo fonseca Reply

    Hi Jeff,

    Thanks for the post, Is this part of the sqlcl new release made available on the dec15th?

    I use the occasion to wish you a nice christmas time and happy new year!

    Thank you

    Ricardo

Write A Comment