How to Create a login.sql File for SQLcl

When using SQLcl (Oracle’s command line interface), you can create a login.sql file, and have it run each time you run SQLcl.

This allows you to have commands executed each time you run SQLcl. For example, you can specify your formatting preferences in the login.sql file, set up aliases, etc.

Here I show you how to create a login.sql file and how to make it run each time you run SQLcl.

Create the login.sql File

Create a file called login.sql and save it in your preferred directory.

Add whatever commands you need to the file.

Here’s an example of a 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

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, you might not always want to launch SQLcl from that same directory all the time, in which case, you can specify its location in your SQLPATH environment variable.

Here’s an example:

export SQLPATH=~/Applications/sqlcl/

In my case, I saved the login.sql file to my ~/Applications/sqlcl/ directory, so that’s the path that I used here. Change this to the directory that you saved your login.sql file in.

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, add it to your ~/.zshrc file.

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.