SQLcl: Customizing on a Mac

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.

Terminal customization

First I start with changing the terminal a bit in order to suit my preferences:

1. Open the Terminal preferences and navigate to Profiles
2. Choose the profile that you will customize to your needs. In my case, Man Page.
3. Duplicate the profile and give it a name. e.g. radu.01
4. Mark the newly created profile to be Default
5. In the Text tab of the profile I change the font to be Courier New 14
6. in the Window tab, I do following changes:
   • double the number of columns to be 160
   • increase the number of rows to be 100
7. 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

login.sql

When starting SQLcl use can use alogin.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 SQLPATH

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 ~/.bashprofile file. 
• If you use ZSH which is the default Mac shell, add it to your ~/.zshrc file:

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 19.0.0.0.0 - Production
Version 19.19.0.1.0

2023/05/04 15:12:55:lab19c01_high? >

Other customizations

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 19.0.0.0.0 - Production
Version 19.19.0.1.0

SAT-06-12:19:25\RADU@lab19c01_high:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.1.0=>

Still TODO

1. On autonomous (?) seem that the variables _SQL_ID and _PRIVILEGE do not seem to work?!
2. 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!

References

1. Having Fun With Prompts – and Saving Yourself from Mistakes
2. How To Change Sqlprompt In SQLcl (Doc ID 2626244.1)