SQLcl: Setup on a Mac

Simple and straightforward steps about how to setup Oracle’s new SQL command line tool on a macOS X environment.

Download

• Download the mac kit from the Oracle site.
• Unzip the kit into the correct directory:

% unzip Downloads/sqlcl-23.1.0.089.0929.zip -d Applications
Archive:  Downloads/sqlcl-23.1.0.089.0929.zip
   creating: Applications/sqlcl/
   creating: Applications/sqlcl/bin/
   creating: Applications/sqlcl/lib/
   creating: Applications/sqlcl/lib/ext/
  inflating: Applications/sqlcl/README.md  
  inflating: Applications/sqlcl/Release-Notes.md  
  inflating: Applications/sqlcl/third-party-licenses.txt  
  inflating: Applications/sqlcl/23.1.0.089.0929  
  inflating: Applications/sqlcl/bin/version.txt  
  inflating: Applications/sqlcl/bin/license.txt  
  inflating: Applications/sqlcl/bin/dependencies.txt  
  inflating: Applications/sqlcl/bin/sql  
  inflating: Applications/sqlcl/bin/sql.exe  
  inflating: Applications/sqlcl/bin/README.md  
  inflating: Applications/sqlcl/lib/pom.xml  
  inflating: Applications/sqlcl/lib/ext/dbtools-cpat.jar  
  inflating: Applications/sqlcl/lib/ext/cpat.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-apex.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-liquibase.jar  
  inflating: Applications/sqlcl/lib/ext/liquibase-core.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-dg.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-aq.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-modeler-cli-ext.jar  
  inflating: Applications/sqlcl/lib/ext/dbtools-modeler-common.jar  
  inflating: Applications/sqlcl/lib/xmlparserv2_sans_jaxp_services.jar  
  inflating: Applications/sqlcl/lib/antlr-runtime.jar  
  inflating: Applications/sqlcl/lib/antlr4-runtime.jar  
  inflating: Applications/sqlcl/lib/jakarta.json-api.jar  
  inflating: Applications/sqlcl/lib/parsson.jar  
  inflating: Applications/sqlcl/lib/dbtools-common.jar  
  inflating: Applications/sqlcl/lib/ojdbc11.jar  
  inflating: Applications/sqlcl/lib/guava-with-lf.jar  
  inflating: Applications/sqlcl/lib/xdb.jar  
  inflating: Applications/sqlcl/lib/ST4.jar  
  inflating: Applications/sqlcl/lib/jackson-core.jar  
  inflating: Applications/sqlcl/lib/jackson-jr-objects.jar  
  inflating: Applications/sqlcl/lib/jackson-jr-stree.jar  
  inflating: Applications/sqlcl/lib/orajsoda.jar  
  inflating: Applications/sqlcl/lib/dbtools-data.jar  
  inflating: Applications/sqlcl/lib/dbtools-http.jar  
  inflating: Applications/sqlcl/lib/slf4j-jdk14.jar  
  inflating: Applications/sqlcl/lib/slf4j-api.jar  
  inflating: Applications/sqlcl/lib/httpclient5.jar  
...

Application and Environment Setup

Attempt to start the application:

sqlcl % bin/sql

Error: SQLcl requires Java 11 and above to run.
       Found Java version .
       Please set JAVA_HOME to appropriate version.

Setup Java

Download Java from the Oracle site. As I am on a mac wit M processor (ARM) , I cannot install jre-8u371-macosx-x64.dmg, so I cannot use the x86 package that is the only one currently delivered by Oracle for macs!! Therefore, I decided to use a trick: considering that I already have the Oracle SQL Developer installed successfully, I will use the its Java environment and not bother for now with installing Java:

% export JAVA_HOME=/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/jdk/
% ./sql /nolog                                                                        


SQLcl: Release 23.1 Production on Wed May 03 23:56:58 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> 
...
 % ./sql       


SQLcl: Release 23.1 Production on Wed May 03 23:57:33 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Username? (''?) 
Password? (**********?) 
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/orcl
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=6nfTi6oYRBW4r112R/IJIg==)
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/xe
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=2ZgmV/URRlqpT0CZjyrw8A==)
Username? (RETRYING) ('/'?) 
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/orcl
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=RCp+IBF3TU2KJ/xdE3+4bw==)
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/xe
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=DCzOU34uQFSg7Nlebcma3Q==)
Username? (RETRYING) ('/'?) 
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/orcl
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=5sqB77jVQuqj9VasEn7BJw==)
  USER          = 
  URL           = jdbc:oracle:thin:@localhost:1521/xe
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=Wv0+02HfTlePD/TwFKRCkg==)

As the setting seems to work, we can now persist it. So from the $HOME directory:

~ % echo 'export JAVA_HOME=/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/jdk/' >> ~/.zshenv
~ % echo 'export PATH=/Users/radu.parvu/Applications/sqlcl/bin:$PATH' >> ~/.zshenv

In order to test the setup, quit Terminal and reopen it:

Last login: Thu May  4 00:17:09 on ttys000
radu.parvu@AMAC ~ % sql /nolog


SQLcl: Release 23.1 Production on Thu May 04 00:21:23 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> 

Connect to an autonomous instance

Copy the wallet in the $HOME directory, after that it is quite straightforward to connect:

SQL> 
SQL> SET cloudconfig Wallet_lab19c01.zip
SQL> CONNECT radu@lab19c01_high
Password? (**********?) **************
Connected.
SQL> 
SQL> 

To connect from the shell with one command:

~ % sql -cloudconfig Wallet_lab19c01.zip radu@lab19c01_high  
~ % sql -cloudconfig Wallet_lab19c01.zip radu/PASSWORDHERE@lab19c01_high

Desktop Shortcuts (Aliases in macOS X terminology)

Next, I will create desktop shortcuts for easy launch of the oracle database connection.

1. Reopen Terminal app and navigate to the Desktop:
   • % cd ~/Desktop
2. create a new file called lab19c01_high_nopass on the desktop and open it with (Nano) text editor:
   • % nano lab19c01_high_nopass
3. We will enter the commands as a short shell script into the named file and save it:
   • !/bin/bash
   • sql -cloudconfig ~/Wallet_lab19c01.zip radu@lab19c01_high
4. Make the file executable:
   • Desktop % chmod u+x lab19c01_high_nopass
5. And that’s it. Now the file should be on the Dektop and the connection can be open by double-click operation
6. Note: in a similar way, we can create other connections foe example towards profiles with other resource management usage or by embedding the password. But in the latest case, please be mindful of the security implications of storing a password in clear text and do not use towards databases that contain any kind of sensitive data!

In conclusion, the setup is relatively straightforward and i will try to use this client as much as possible going ahead.

Please stay tuned for my next post regarding how do I customize SQLcl for my own convenience :)