Formatting Columns in SQL Developer a la SQL*Plus

thatjeffsmith SQL Developer 3 Comments

Tell Others About This Story:

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…

Tell Others About This Story:

Comments 3

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

    1. thatjeffsmith Post
      Author

Leave a Reply

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