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

Formatting Columns in SQL Developer a la SQL*Plus

2 Mins Read

Does SQL developer support, col command that is used to format query results in sqlplus. like – col id heading app_id format a10

My answer was: ‘Yes.’

But I figured I should put up or shut-up a little bit, so here goes.

You can read more about column formatting in SQL*Plus here (Docs).

I used those same queries as a test in SQL Developer.

Remember that when you use this button:

Runs queries through the script engine, standard output back, versus one or more data grids.
Runs queries through the script engine, standard output back, versus one or more data grids.

… we emulate SQL*Plus and run your query(ies) as a script. That is, they go through our script engine. We support, or are in the process of supporting, near 100% of what SQL*Plus does. This same engine is what powers SQLcl.

In fact, it’s the same code. What we use to do SQLcl is the same code that we use in SQL Developer (more or less.)

So here we go.

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN LAST_NAME FORMAT A10
COLUMN SALARY FORMAT $99,990
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
 
COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'
 
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
Remember, execute with F5
Remember, execute with F5

I had to write the query twice, because unlike SQL*Plus and SQLcl, there’s no concept of a ‘current query’ to run when using the ‘/’ command. There’s just where you have your cursor, or what you have highlighted. A consequence of having a GUI and a CLI…

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

5 Comments

  1. J Thvedt Reply

    I’ve never been able to use a format with a decimal point, in any version of SQLcl. Currently I’m on version 19.1, and it still gives the same old error:

    > col x format 9.90
    SP2-0158: unknown COLUMN option “.90”

    As a result, some scripts I use regularly give me hard-to-scan results like this (well, sort of like this — at least in SQLcl it’s a mono-spaced font so everything gets right-justified):

    .1
    .316666667
    .466666667
    .4
    .4
    .05
    .316666667

  2. Mauricio Reply

    This is one of my SQL Developer favorite tips because I tend to make a query and copy the results in a comment (just for sake of have present certain codes from certain tables) and I tended to use tab (or spaces) by align the results in plain text (which is frustrating because the tab-spaces differences in Notepad++ and SQL Developer’s text editor), but know with this tip, I can get the results much easier.
    Again, thank you!.

    PS: Would be possible to add a new line with a separator (for separate the column headers from the results?)

    like this example?

    TBL_CODE SAMPLE_CURRENT_DATE
    ——– ——————–
    1 17-JUN-2019 17:35:03
    2 17-JUN-2019 17:35:03
    10 17-JUN-2019 17:35:03
    11 17-JUN-2019 17:35:03
    14 17-JUN-2019 17:35:03

    I think I had read in some of your post for add a line-break by using:

    COLUMN HEADING ‘MONTHLY|SALARY’; — but this work in views, it seems to me…

    Or even, using the “dbms_xplan.display”
    like you use here: https://www.thatjeffsmith.com/archive/2014/09/30-sql-developer-posts-in-30-days-day-22-its-all-in-the-font/

    I hope I was clear (because I know I made my comment a bit unclear).

  3. Fernando Reply

    This works on sqlplus but returns an error on sqlcl:

    SQLcl: Release 4.2.0 Production on Thu Feb 09 14:09:29 2017
    …
    SQL> col mycol for 9,999,999.99
    SP2-0158: unknown COLUMN option “,999,999.99”
    SQL>

    Any ideas?

    Thanks.

Write A Comment