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!”
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.
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.
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.
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.
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’
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.
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.