An intro to using Oracle SQLcl on Mac

Oracle SQLcl blurb, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
SQLcl…my newest crush?

Did you know you can use Homebrew to install Oracle’s SQLcl on Mac? I just realized this about a week ago (always the bridesmaid, never the bride…amirite??).

Homebrew

First you’ll need to install Homebrew (I’m sure there are other ways to install SQLcl, but installing through Homebrew was a breeze).

You can install Homebrew on your Mac by first opening up a new terminal window and typing/entering:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

That Shell script should walk you through the setup.

DISCLAIMER: I didn't go that route, but if you follow the directions on the Homebrew site I assume it should work.

If you want a more hands-on approach, visit this site for a complete walk through of setting up your new Mac for application development. You may not need to do everything on that site, but read up on the Homebrew & Cask sections.

Installing SQLcl

I’ve since learned that you are really installing the SQLcl app via Cask (which is included in Homebrew). Cask allows the installation of “large binary files” (see the site from the paragraph above for more details). A list of the current Cask applications available.

cask applications sqlcl now available, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
We’re giving Pi a run for its money with that semantic versioning…

Once you are all updated with Homebrew, you can then open up a new terminal and enter the following:

brew install sqlcl 

As it installs, you’ll see a lot of activity in the terminal window. Once complete, you’ll see something that looks like this (I’ve already installed/reinstalled it tons of times, so there may be some slight difference):

sqlcl install complete, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Don’t forget to review the Caveats section!

Caveats

The main things to review are in the “Caveats” section. First, you’ll need Java 11+ or higher for this to work (i.e., connect to an Oracle database). I didn’t realize this, but we give you a command to update to the latest Java version. I wish I had known that, as I spent way too much time figuring out the best way to update.

brew command for upgrading java, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Upgrading Java through Homebrew

Second, you’ll need to add a new line to your “PATH environment variable”.

Line to be added to PATH environment variable, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database copy
New line to be added to your PATH Environment Variable

What is “PATH?”:

PATH is an environment variable on Unix-like operating systems, DOS, OS/2, and Microsoft Windows, specifying a set of directories where executable programs are located. In general, each executing process or user session has its own PATH setting.

Wikipedia.org

I understand this, as specific applications will only work if you’ve predefined the locations of their dependencies. You can indicate where your operating system looks for these dependencies by updating the PATH Environment Variable (a separate file; more on this in a second). We have another excellent resource here (it explains PATH and CLASSPATH well).

Locating PATH on Mac

On a Mac, there are a couple of ways you can find PATH.

PRO TIP: PATH export definitions are located in a .zprofile file.

The easiest way (for me) to find this file is by typing/entering in a terminal window:

open .zprofile 

LEARN ZSH: Want to learn all there is about zsh , .zshenv, .zprofile, .zshrc or .zlogin? Bookmark this manual for future use.

From there, your .zprofile file will appear in a new window. Mine looks like this:

Initial zprofile file without new sqlcl line, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
A look at my .zprofile file.

If you recall from the “Caveats” section, you may need to add a line to your PATH. I’ve already done that; I added a comment for reference (optional, but make sure the comment is preceded with a “#”).

zprofile file for PATH environment variable, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
.zprofile file with the new line added.

Remember to save (with CMD + S)! After which, you can close out the window.

Also, it’s a good idea to close any active terminals and open a new one (this way your terminal picks up any changes you’ve made).

You can also perform a check to see what is installed via Homebrew with the following command:

brew list

You’ll see something akin to this (depending on what you have installed):

brew list printout, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Use brew list to see current Homebrew installs.

Dive into SQLcl

Okay, now we are ready to explore SQLcl!

DISCLAIMER: I'm not connecting to my database yet (I will be in my next post as I'm just working out the kinks on my Podman setup…containers, baby!). 

I’ll keep this next section simple. Begin with a new terminal and type/enter:

sql -h 

or

sql -help 

You’ll see the following printout:

sqlcl initial help screen, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Help printout.

If you look closely, you’ll see information for Usage 1 and Usage 2.

README: When in doubt, refer to the help!

Usage 1

Usage 1 – great for reviewing in-context help documentation as well as version information.

Usage 1 sqlcl initial help screen, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Usage 1 focus.

Regarding help, I’ve tried the following (they all work):

  • sql -h
  • sql -help
  • sql -Help
  • sql -H
  • sql -HELP
HINT: Type/enter exit into the command line to exit the SQLcl help screen.
type exit to return to the shell, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Using the exit command.

Usage 2

Usage 2 sqlcl initial help screen, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Usage 2 focus.

In Usage 2, you’ll find information for two login options:

  • Login with a “Connect Identifier”
  • No logon

The Connect Identifier can be either:

  • “Net Service Name”
  • “Easy Connect”

Wut r theez?

I found some information relating to the “Net Service Name” method of connection; you can refer to that here. Be forewarned – there seems to be some configuration required to use the Net Service Name method (I’ve not tested this yet).

Conversely, the Easy Connect Method looks well…easier. I found a good resource here. This was the method I used when experimenting with containers and Podman (blog coming soon!).

Now, if you are like me and want to explore SQLcl (without connecting to an Oracle database), you can log in using the /NOLOG option. Make sure you exit out of the SQLcl help screen first.

Once you’re out, type/enter the following command:

sql /NOLOG 
NOTE: Make sure you have a space between the "l" in sql and the "/" of /NOLOG. 

Once you hit enter, you should see a screen like this:

sqlcl nolog option, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Logging in with the /NOLOG option.

Unimpressive, right? Well, allow me to whet your appetite some. From here, you have two more options. Those are:

  • h
  • help

Entering h will reveal a history of the most recent shell commands you’ve executed.

sqlcl history, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
Shell command history.

Type/enter help and you’ll; reveal a list of the available SQLcl commands and options. It looks like this:

help screen once logged on with sqlcl, chris hoina, senior product manager, oracle database tools, command line, oracle autonomous database
So. Many. Options.

Pretty cool, eh?

You can take this one step further by typing/entering a topic of interest. Here are a couple random topics I explored (ALIAS and MODELER):

Final thoughts

While I have yet to take full advantage of what SQLcl offers, I see the potential time savings for application developers who want to stay in a text editor while coding (without switching to another GUI application).

I’ll include the SQLcl documentation so you have it for reference. But be forewarned we’re updating this document; some instructions may be changed.

And check back in a week or two once I get Podman fully working with one of our Database Containers. I’ll test SQLcl, ORDS, and an Oracle Enterprise database 21.3.x (if you’re curious about our available containers, you can find them here).

Want to learn even more about SQLcl? Check out these helpful resources:

And finally…

Don’t forget to follow

Be sure to share or follow if you aren’t already:


Leave a Comment