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 –
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.
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…
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.
3 Comments
Kinda late to the party, but just wanted to share a piece of code I put together to enrich Jeff’s prompt formatting.
Some times I need to connect as sysdba, other just as an ordinary user, so I thought it would be nice if the prompt showed the sys user more vibrant, just to make it visually easier to notice I’m connected as sysdba. Kinda hard to make such test given sqlplus limitations working with variables and all, but here is how I managed to get it done:
set head off
SET TERMOUT OFF
undefine var1
COLUMN new_prompt NEW_VALUE var1
SELECT case when user in (‘SYS’,’SYSTEM’) THEN ‘(@|red,bold _USER|@@@|’
ELSE ‘(@|cyan _USER|@@@|’
end || ‘cyan _CONNECT_IDENTIFIER|@) sql> ‘as NEW_PROMPT
from dual ;
set sqlprompt &var1
set head on
SET TERMOUT on
The firsts SET are used to suppress the code output (I put it in a login.sql script to have the prompt formatted at login time, so I like to have a clean connected prompt, without useless output).
Then I created a new column referencing a variable. Next I select my prompt format into that variable accordingly to the user – if SYS or SYSTEM, red and bold; any other user just plain cyan.
After I effectively change the prompt using the prepared variable based on the column generated by the SELECT CASE.
And to finish I re-enable the heading and the regular output for the rest of the session.
It works fairly nice to my needs. The SELECT CASE can even be improved to color red (or any other color) not only for the fixed users SYS and SYSTEM, but instead check the roles of the connected user, like if SYSDBA is assigned to non standard users, or even if other roles have higher privileges.
Thanks for sharing!
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.