Automatically Installing SQLcl for Oracle's Vagrant Database Boxes

_SQLcl_for_Oracle_Vagrant_Boxes.md

Automatically Installing SQLcl for Oracle's Vagrant Database Boxes

Oracle provides Vagrant configuration files for installing several versions of Oracle Database in the oracle/vagrant-boxes GitHub repository. The Oracle Database box installers allow running user-defined scripts automatically after the database has been set up and started. This document provides instructions and scripts that use this functionality to install SQLcl.

Overview:

• The procedure below installs SQLcl for the vagrant user only.
• Oracle envrionment variables are set for the vagrant user (using oraenv), so that SQLcl can find the database's tnsnames.ora file, and the vagrant user can run the database utilities, like SQL*Plus and RMAN.
• SQLcl requires Java 1.8 or above. The procedure installs the latest OpenJDK from the Oracle Linux Yum Server, and creates a script to allow the vagrant user to run SQLcl without Java version conflicts, while preserving the ORACLE_HOME environment variable.
• Oracle Database 18.3.0 already includes SQLcl 17.3.0, and Oracle Database 19.3.0 already includes SQLcl 19.1.0. The procedure installs the latest version (19.2.1.206.1649 as of 19 August 2019) without disturbing the original installation.

Steps:

• Clone or download Oracle's vagrant-boxes repository.
• Download the appropriate Oracle Database installer file(s) as instructed in the README.md file for the database version you want to install.
• Download the SQLcl zip file from https://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html to the same directory as the database installer file(s).
• Copy the script below for the database version that you want to install and place it in the userscripts directory (this is one level below the directory that contains the database and SQLcl installer files).
• Run vagrant up. SQLcl will be installed automatically.
• To run SQLcl, SSH to the box (vagrant ssh) and enter sql at the prompt.

install_SQLcl_for_11g_XE.sh

#!/bin/bash
#

# install OpenJDK
yum -y install java-latest-openjdk

# install SQLcl for vagrant user
unzip /vagrant/sqlcl*.zip -d /home/vagrant
chown -R vagrant:vagrant /home/vagrant/sqlcl

# create script to start SQLcl
cat > /home/vagrant/startSQLcl.sh << 'EOF'
#!/bin/bash

export SQLPATH=~
export TNS_ADMIN="$ORACLE_HOME"/network/admin
unset ORACLE_HOME
~/sqlcl/bin/sql "$@"
EOF

chown vagrant:vagrant /home/vagrant/startSQLcl.sh
chmod u+x /home/vagrant/startSQLcl.sh

echo 'alias sql="~/startSQLcl.sh"' >> /home/vagrant/.bashrc

# set Oracle Database env variables for vagrant user on login
echo '. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh' \
  >> /home/vagrant/.bash_profile

install_SQLcl_for_12c_18c_18cXE_19c.sh

#!/bin/bash
#

# install OpenJDK
yum -y install java-latest-openjdk

# install SQLcl for vagrant user
unzip /vagrant/sqlcl*.zip -d /home/vagrant
chown -R vagrant:vagrant /home/vagrant/sqlcl

# create script to start SQLcl
cat > /home/vagrant/startSQLcl.sh << 'EOF'
#!/bin/bash

export SQLPATH=~
export TNS_ADMIN="$ORACLE_HOME"/network/admin
unset ORACLE_HOME
~/sqlcl/bin/sql "$@"
EOF

chown vagrant:vagrant /home/vagrant/startSQLcl.sh
chmod u+x /home/vagrant/startSQLcl.sh

echo 'alias sql="~/startSQLcl.sh"' >> /home/vagrant/.bashrc

# set Oracle Database env variables for vagrant user on login
cat >> /home/vagrant/.bash_profile << EOF
export ORACLE_PATH=/home/vagrant
export ORACLE_SID=${ORACLE_SID:-XE}
export ORAENV_ASK=NO
. oraenv -s
export ORAENV_ASK=YES
EOF