Customise SQLcl using an auto-run script on each connection. Set it and forget it!

In one of my latest blog posts about customising SQLcl, I promised to show how to set it up once and forever.

What can we do instead of typing those commands each time?

SQLcl allows you to set a script that can be executed each time you start your SQLcl session. Let's see how to do it.

But first, to show you the difference now and at the end of this blog post, let's have a look at how my SQLcl terminal looks right now.

Simple look, no parameters set.

Create a script that will be executed at each SQLcl session

I called my script login.sql and create it in /Users/rg/apps/sqlcl_scripts folder - you can put it wherever you want.

Add the script location to your PC path

That's the way I did it on my Mac

export SQLPATH="/Users/rg/apps/sqlcl_scripts/"

Generally (for Mac & Windows), you do it in a similar way as when you installed SQLcl ( instructions here)

Put your settings in the login.sql file

Now, everything I want to set when SQLcl start I put into the login.sql file.

set sqlprompt "@|blue _USER|@@@|green _CONNECT_IDENTIFIER|@ > "
set serveroutput on
set sqlformat ansiconsole
set statusbar on 
set statusbar default username git project cwd txn timing

Result

Ok, let's see how it looks now

Below is my enhanced version of login.sql. Pick whatever settings you want or customise your login.sql on your own.

/* --------------------------
SQLcl auto-login script executed at each SQLcl session start.
-----------------------------

*/

/*-----------------------------------------
        Customize SQLcl terminal look & feel & set parameters
-------------------------------------------*/
/* How to check your actual settings? 
show define
show feedback
show serveroutput
or 
show all
*/

/* SET SQLPrompt 
By default each line starts with "SQL>"
Turn it off:
SET SQLPROMPT ""
Change to custom one:
SET SQLPROMPT "MySQLclCustomPrompt>>>"
*/
set sqlprompt "@|blue _USER|@@@|green _CONNECT_IDENTIFIER|@ > "

/* LINENUMBERS - controls whether to display line numbers in the SQL editor
SET LINENUMBERS ON [default]
SET LINENUMBERS OFF
*/


/* DEFINE  - enables & prompting
set define on [default]
set define off
Can be changed to e.g. # 
set define '#'
*/

/* FEEDBACK - controls whether to display the number of records returned/affected by a statement
set feedback on [default]
set feedback off
set feedback only - shows only the number of records returned/affected by a statement, without showing the actual data.
*/

/* SERVEROUTPUT - print or not output generated by DBMS_OUTPUT 
set serveroutput on
set serveroutput off [default]
*/
set serveroutput on

/* TERMOUT - controls whether to display the results of SQL commands when running a script
set termout on [default]
set termout off
TERMOUT only affects commands executed from a script file
*/

/* TIMING - controls whether to display the time taken to execute each SQL command or PL/SQL block
set timing on 
set timing off [default]
*/

/* SQLFORMAT  - change a way how your data is exported or displayed. 
                Resizes the columns to the width of the data to save space
set sqlformat

default is "default" - some old, ugly look

set sqlformat csv
set sqlformat ansiconsole
set sqlformat json
set sqlformat insert
set sqlformat loader
set sqlformat xml
set sqlformat html
set sqlformat delimited |
*/
set sqlformat ansiconsole

/* STATUSBAR - toolbar in your SQLcl session 
set statusbar on
set statusbar default linecol git project cwd  editmode txn 

linecol     - shows the current line and column number in the status bar
git         - shows the current git branch in the status bar
project     - shows the current project name in the status bar
cwd         - shows the current working directory in the status bar
txn         - shows the current transaction status
editmode   - shows the current editing mode (e.g., insert, overwrite)
timing     - shows the timing status (on/off) in the status bar
*/
set statusbar on 
set statusbar default username git project cwd txn timing

You may also want to check those nice blog posts about customising SQLcl

• "How to maximise your command line experience with Oracle", by Jeff Smith -> link

• This blog is about SQLPlus, but you can use those tips in SQLcl -> "Conditionally calling a script in a SQL*Plus control script", by Mike Smithers, link

PS What's the photo? That's an Eastern gray squirrel (Sciurus carolinensis) somewhere in Florida in 2026