Updating SQLcl, setting up the environment: Oracle Database Developer OCI Environment Part 2

This is part 2 of my Oracle Database Developer OCI Environment series. It contains just a few steps to make the environment a bit more useable.

Update sqlcl on OCI

SQLcl is the modern replacement for SQL*Plus, it is called via sql instead of sqlplus and it comes with the newer Oracle database Releases. Let’s see what we have included:

The Base Database service comes with the SQLcl version that comes with the database. So in this case, Oracle 23.7 comes with SQLcl 24.3.2. In most cases, this will absolutely be fine. But as a developer, you might want to get your hands on the latest and greatest features. At the time of writing, SQLcl 25.1.1 is available, so let’s see how to get an update.

Exit the oracle session back to the opc user, so we have sudo access. Seach the yum repository for sqlcl

sudo dnf se sqlcl

There is a match, so install it

sudo dnf in sqlcl

Wait for the installer to complete

Switch back to the oracle user and check sqlcl. So we have a new sqlcl und /usr/bin/sql, which is version 25.1.0.101. That is close to the latest version 25.1.113 available on oracle.com/sqlcl . That should be close enough for normal usage.

But for getting into latest features, I recommend to always use the latest version of SQLcl directly from it’s homepage under https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

or get it via wget

cd /u01
wget -P /tmp https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
unzip -d /tmp /tmp/sqlcl-latest.zip
mv /tmp/sqlcl /u01/sqlcl-25-1-1
ln -sfn sqlcl-25-1-1 sqlcl

Doing so, when a new version arrives or you need to go back to an older one, just change the sqlcl link. Add it to the Path at your own risk.

echo export PATH=/u01/sqlcl/bin:\$PATH >> ~/.bashrc
~/.bashrc

Optional: configure SQLcl Syntax Highlighting

Create the directory /home/oracle/sql and inside a file login.sql with the following content (source: https://www.thatjeffsmith.com/archive/2023/08/your-ideal-oracle-database-command-line-experience):

mkdir ~/sql
cat >> ~/sql/login.sql <<EOF
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 white
SET highlighting string foreground yellow
SET highlighting NUMBER foreground cyan
SET highlighting comment background white
SET highlighting comment foreground black
alias cls=clear screen; 
EOF

Check highlightning with some sql

Optional: TNSNAMES

To use TNSNAMES, add ORCLPDB to tnsnames.ora

lsnrctl stop
echo "export TNS_ADMIN=$ORACLE_HOME/network/admin" >> ~/.bashrc
. ~/.bashrc
cat >> $TNS_ADMIN/tnsnames.ora <<EOF
ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb.subxxxxxxxx.vcndb.oraclevcn.com)
    )
  )
EOF
lsnrctl start
tnsping orclpdb1

Optional: Demo Schema

To have a schema to test ORDS Database Actions, we will use the HR-Schema from Oracle.

Optional: to have some sample data to test with, the required scripts to setup the HR-schema can be found on GitHub under https://github.com/oracle-samples/db-sample-schemas. For easy setup, you could use hr_quick_start.sql from https://github.com/connormcd/misc-scripts.

mkdir git
cd git
git clone https://github.com/oracle-samples/db-sample-schemas
git clone https://github.com/connormcd/misc-scripts
cd misc-scripts
sql / as sysdba
alter session set container=orclpdb;
@hr_quick_start_23.sql
@sh_quick_start_23.sql

Do not forget to set a password for both schemas if needed.