Video Overview of SET SQLFORMAT ANSICONSOLE

thatjeffsmith SQL Developer 8 Comments

Tell Others About This Story:

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

Tell Others About This Story:

Comments 8

  1. SET sqlformat ansiconsole;

    This removes the breakers between the results e.g.

    Test Result
    1 2

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

    1. thatjeffsmith Post
      Author
  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.

    1. thatjeffsmith Post
      Author

      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

      1. 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;

      2. thatjeffsmith Post
        Author
  3. 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.

    1. thatjeffsmith Post
      Author

      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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *