In this post I will summarize the steps I’ve taken to customize SQLcl on my Mac working environment. We assume that the reader is familiar with the procedures outline din my previous post on SQLcl on setting up SQLcl on Mac environment.
First I start with changing the terminal a bit in order to suit my preferences:
- Open the Terminal preferences and navigate to Profiles
- Choose the profile that you will customize to your needs. In my case, Man Page.
- Duplicate the profile and give it a name. e.g. radu.01
- Mark the newly created profile to be Default
- In the Text tab of the profile I change the font to be Courier New 14
- in the Window tab, I do following changes:
- double the number of columns to be 160
- increase the number of rows to be 100
- Back on General tab choose your newly created profile for the option: ‘On startup, open:‘
Now the terminal windows will open the SQLcl connection with the desired fonts, color and size. We can proceed to the next step.
Customize SQLcl environment
When starting SQLcl use can use a
login.sql file, and have it run each time you run SQLcl. Its usage is not so well documented in the SQLcl documentation, therefore I will add a few details below.
Add to your
If you put the file in the current directory, and then run SQLcl from that directory, it will use the
login.sql file. However, this might not always be convenient, in which case, you can specify its location in your
SQLPATH environment variable.
The file you add this line to will depend on your shell:
- If you use BASH, add that line to your
- If you use ZSH which is the default Mac shell, add it to your
oracle % cd ~ % echo 'export SQLPATH=~/Dropbox/etc/oracle/' >> ~/.zshrc ~ % more .zshrc export SQLPATH=~/Dropbox/etc/oracle/ .zshrc (END)
Once this is done, SQLcl will check your
login.sql file whenever you use it to connect to Oracle Database. And you will no longer need to configure your preferences every time you connect.
Going ahead I will use the login.sql file as much as possible to store the customization’s.
Change the prompt
SQLcl supports SQL*Plus environment settings, commands, and behaviors. By default the SQLcl Prompt(SQL>) is same as SQL*plus.
I added the following to the login.sql file in order to customize the prompt:
alter session set nls_date_format = 'YYYY/mm/dd hh24:mi:ss'; set sqlprompt "@|red _DATE|@:@|green _CONNECT_IDENTIFIER|@@|blue ? >|@"
to look like this at login:
% /Users/radu.parvu/Desktop/lab19c01_high ; exit; SQLcl: Release 23.1 Production on Thu May 04 15:12:54 2023 Copyright (c) 1982, 2023, Oracle. All rights reserved. Session altered. Last Successful login time: Thu May 04 2023 15:12:55 +03:00 Connected to: Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 - Production Version 188.8.131.52.0 2023/05/04 15:12:55:lab19c01_high? >
I added the following to my login.sql file:
set sqlformat ansiconsole set highlighting on set highlighting keyword foreground blue set highlighting identifier foreground magenta set highlighting string foreground green set highlighting number foreground cyan set highlighting comment foreground yellow set highlighting comment background red
And here is a picture of how my SQLcl environment looks now. I admit is not perfect but is good for my eyes and is still work in progress
Further experiments with the PROMPT
Quite a long one
alter session set nls_date_format = 'DY-dd-hh24:mi:ss'; -- set sqlprompt "@|red _DATE|@:@|green _CONNECT_IDENTIFIER|@@|blue ? >|@" set sqlprompt "@|magenta _DATE|@\@|blue _USER|@@@|green _CONNECT_IDENTIFIER|@:@|red _O_VERSION|@@|blue =>|@"
Session altered. Last Successful login time: Sat May 06 2023 12:19:25 +03:00 Connected to: Oracle Database 19c Enterprise Edition Release 184.108.40.206.0 - Production Version 220.127.116.11.0 SAT-06-12:19:25\RADU@lab19c01_high:Oracle Database 19c Enterprise Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.0=>
- On autonomous (?) seem that the variables _SQL_ID and _PRIVILEGE do not seem to work?!
- I do not manage to insert a newline character in the rpompt or at the end of it. This is dead-easy on an UNIX shell!
- Having Fun With Prompts – and Saving Yourself from Mistakes
- How To Change Sqlprompt In SQLcl (Doc ID 2626244.1)