Love the command line? Hate ugly, out-of-the-box formatting of your results?

You’re in luck, we have just the thing for you:

Oracle SQLcl.

It offers the ability to auto-pretty up your SQL results to the screen.

Here’s a quick video overview of what that is, and how it works.

For those of you w/o access to YouTube

How do you get through your day w/o cat videos and Marvel Studios movie trailers?

Anyways, here’s a quick recap:

SET SQLFORMAT ANSICONSOLE

Add that to your login.sql

When you startup your SQLcl, your queries will go from…

Default output style, no bueno.

…to this:

Better, yes?

Each page of results is measured for ‘best fit’. We can’t make your screen bigger, so this isn’t a silver bullet.

If you want to use ANSICONSOLE, don’t use the SQL*Plus Report formatting commands like COL – use one or the other, your choice.

And you have a few more formats you can use…

These should be self-explanatory.

What’s now self-explanatory is the ‘delimited’ format which isn’t listed in the Help for reasons (bug!)

SET SQLFORMAT DELIMITED

thatjeffsmith
Author

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

8 Comments

  1. SET sqlformat ansiconsole;

    This removes the breakers between the results e.g.

    Test Result
    1 2

    I need
    Test | Result
    —– ——–
    1 | 2

  2. Hi,

    Having problems displaying the output in sqldeveloper, since the update to 4.2 my outputs are all truncated, example

    OMENTO TIPOA CODIGOERRO CODIGOPROC
    ———————– —– ——————– ———-
    PROCESSO
    ——————————————————————————–
    DURACAO UID
    ———- —————————————————————-
    TOKENSESSAO
    ————————————————————
    2017-04-21 09:32:37.012

    I have the set linesize in the logon script, the show linesize shows linesize 1763, i already tried to set the sqlformat to ansiconsole but the output always comes broken.

    • thatjeffsmith

      linesize is auto-set to the width of your script output window

      you should be able to change it though in your script

      you can also try adding a
      SET SQLFORMAT ANSICONSOLE and we’ll try to best fit your columns

      4.2 more closely fits/matches what SQL*Plus output will give you

    • Ok,

      Found that the problem only shows when i’m printing the result of a ref_cursor sample code:

      var rc1 refcursor;

      BEGIN

      open :rc1 for
      select * from TABLE;

      END;
      /

      print rc1;

    • thatjeffsmith

      yeah, I think you found a bug. That sql*plus style print command for some reason isn’t observing LINESIZE

  3. Rajeshwaran, Jeyabal Reply

    our organization are blocking the videos from youtube and other streaming media – so it would be helpful if you continue with your old style ( few text and images) of blogging rather than this video format.

    • thatjeffsmith

      I need to cover both audiences, some folks don’t have time to read. I’ve amended today’s post and add a few screenshots and some text.

      I’m a little surprised you don’t run home to watch my videos from there though 🙂

Reply To JM Cancel Reply