How to maximize your command line experience with Oracle

Gimme the perfect prompt!

What would that look like when working with an Oracle database?

In this post I’ll show a few things you can setup and configure with SQLcl to make your fingers “sing!”

Wondering why I’m not talking about SQLPlus?

Also, I know there’s no such thing as perfect, and many of you will bicker with SQLcl, but we are always aiming for ‘getting better!’ So, share your favorite config or setup items in the comments or feel free to share what you think could make your experience, better.

These are a list of things I take care of when setting up SQLcl on a new machine, VM, or container.

login.sql

This is a SQL script that executes for any/every connection made by SQLcl (or SQL Developer or SQL*Plus).

I keep my login in the SQLcl ‘bin’ folder, and SQLcl warns me. You don’t want someone to ‘inject’ SQL into a connection by simply dropping a login.sql into various folders, hence the heads-up.

Let’s look at my login.sql script –

SET statusbar ON
SET statusbar ADD editmode 
SET statusbar ADD txn
SET statusbar ADD timing
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

Highlighting and Statusbar

You may like or not like these features, or you may not like HOW I setup these features, but it’s up to you how or IF they appear.

If you hate my color styles, you can simply adjust via ‘set highlighting’

‘help set highlighting’ will give you an idea of what’s available.

You can turn these on, off, or change what’s shown. I have a transactions ‘pending’, and my last script took 5 and some seconds to run. Note that the execution time is the client’s perspective, not the database’s. So it took SQLcl 5.218 seconds to receive the response from the database.

‘help set statusbar’ will help you setting it up to your taste.

Deeper dive on Statusbar and Highlighting.

Your default editor

As you can see above, the current editor is ’emacs’ – I chose that just to rile up the vi vs emacs crowd, I really don’t care why you pick one or the other.

Here’s an animation of me using vi commands in the statement buffer.

Preferences/Options

DDL

Generating DDL for scripts or for Liquibase changeSets, you’ll want to setup how SQLcl uses DBMS_METADATA.

‘help set ddl’ will give you what you need to know.

SQL History

Do you want specific types of commands recorded? I often make mistakes, so I like to see them in my history IN my session, but not to be recorded ‘FOREVER.’ I also don’t put SHOW, HISTORY, CONNECT, and CLEAR statements in there. That’s all controlled by ‘set history’

Learn more on the HISTORY feature in SQLcl.

SQL Formatter

I can bring my rules in, straight from SQL Developer.

Looking for the ‘best’ formatting rules? We got ’em here!

Oracle Cloud Infrastructure (OCI)

You can configure SQLcl as an OCI Client using your private keys and PROFILE. So ORDS will know where to find my key and what REGION to be operating in, via the OCI command.

I little more exotic than simply taking CSV in the object store to tables in your database, I want to collect information from my DB and do stuff with that data in OCI.

I’m going to use SQLcl to gather database performance stats and pipe them to my OCI Metrics service, where I can then setup some dashboards and alerts.

Features Requiring JavaScript

This would be CLIENT side JavaScript, not js running in the database itself. So if you’ve seen some of Kris’ code samples for doing things like unloading BLOBs in a table to files on your local machine, that uses the JavaScript engine (Nashorn) in the Java Virtual Machine – something that goes away after Java 11.

So either use Java 11, or switch to Oracle GraalVM Java 17 and add the optional JavaScript Runtime Plugin.

Coming Soon, SQL Developer Integration

In our upcoming extension for VS Code, you’ll be able to take your worksheet of SQL and PL/SQL and have it executed in a SQLcl terminal window, automatically. No need to setup the connection or install SQLcl.