You have three ways to make your standard terminal more colorful with Oracle’s command-line interface for the Oracle Database:

  • The SQL Prompt
  • The editor/content you type/paste/execute
  • The results of your SQL queries

Let’s look at each of these in more detail. Note that between Kris and I, we’ve talked about all of this before, but I wanted a single post to make it easier for folks to find and start using.

Your Prompt

set sqlprompt "@|blue,bold,underline  _USER|@@@|red _CONNECT_IDENTIFIER|@@|blue ->|@"

This results in a SQL Prompt that looks like this –

Not the most interesting of prompts, but you get the idea.

If you’re on a Mac/Linux, you can also easily throw in emojis into your prompts. See this post from Kris on how to make your prompts dynamic using some javascript.

For an overview of all of your prompt markup/highlighting/coloring possibilities, see this post from Kris waaay back in 2015 land.

The ‘definitive’ gist with a few thousand combos to choose from can be found here.

Your Code

I just covered this a few weeks ago, but here it is again, in a nutshell.

Run ‘help set highlighting’ for help and examples.

I tried to emulate the Dracula theme with mine, and have it set into my login.sql a la :

set highlighting on
set highlighting keyword foreground green
set highlighting identifier foreground magenta
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment background white
set highlighting comment foreground black 

Your Query Results

Kris again first talked about this feature in 2019. Now there are a few ways to go about this, but I’m going to use some REGEX patterns to do highlighting based on the type of data coming back, vs looking for SPECIFIC data coming back.

I want numbers in one color, and anything that’s not strictly a number in another color. Now, I’m not a regex expert. In fact, I am a ‘copies REGEX from StackOverflow’ type of user. Now dear reader, I will trust that YOU can in fact figure out the rest based on my example.

Our command is

set sqlformat ansiconsole -config=highlight.json

And in this ‘highlight.json’, we will have some rules and/or expressions that tell the formatter code not only how to express the output in terms of spacing, but how to color it as well.

Here’s my highlight.json file:

{"highlights":[ 
                {
                 "type":"regex",
                 "test":"^[0-9]+$",
                 "color":"RED"
                },
                {
                 "type":"regex",
                 "test":"^[^0-9]+$",
                 "color":"GREEN"
                }
               ]
 }

And then if we tell SQLcl to use that, and run a query…

I could spend the time to come up with rules for how my dates are printed, but I’m OK to stop here.

Feel free to share your favorite ‘look and feels’ here for others to beg, borrow, and steal. Just don’t ask me for RegEx help.

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

1 Comment

  1. Avatar

    Jeff, Thank You for answering my question during today’s Oracle Database World session by posting me link to this page. Actually I mean highlighting not only columns (like this example shows), but values in the columns, based on amount or name. That would make to notice bigger numbers or special text in the same query more easily.
    Here probably is not very good quick example, but if to run:

    $ORACLE_HOME/sqldeveloper/sqldeveloper/bin/sql
    set sqlformat ansiconsole

    select
    case WHEN owner like ‘SYS%’ THEN ‘@|yellow,bold ‘ || owner || ‘ |@’ ELSE ‘@|BLUE ‘ || owner || ‘ |@’ END “OWNER”,
    CASE WHEN sum(bytes) 1000000 and sum(bytes) < 50000000 THEN '@|yellow ' || sum(bytes) || '|@' ELSE '@|red ' || sum(bytes) || ' |@' END "BYTES"
    from dba_segments group by owner ;

    Output looses the formatting and if to try to use rpad or lapd functions, it makes even worse. (I tried to put output of several columns in one row with fixed spaces, and it works like walk around if the keywords of the colors is the same length like: green, white, black – 5 letters.) I understand what is the issue, but do not find how to resolve it.

Write A Comment

RSS
Follow by Email
LinkedIn
Share