MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle 23c Free SQL*Plus

without comments

It’s always frustrated me when using the sqlplus command-line interface (CLI) that you can’t just “up arrow” to through the history. At least, that’s the default case unless you wrap the sqlplus executable.

I like to do my development work as close to the database as possible. The delay from SQL Developer to the database or VSCode to the database is just too long. Therefore, I like the native sqlplus to be as efficient as possible. This post shows you how to install the rlwarp utility to wrap sqlplus and create a sandboxed student user for a local development account inside the Oracle 23c Free container. You should note that the Docker or Podman Container is using Oracle Unbreakable Linux 8 as it’s native OS.

You can connect to your Docker version of Oracle Database 23c Free with the following command:

docker exec -it -u root oracle23c bash

You can’t just use dnf to install rlwrap and get it to magically install all the dependencies. That would be too easy, eh?

Attempting to do so will lock your base OS and eventually force you to kill with prejudice the hung dnf process (at least it forced me to do so). You need to determine the rlwrap dependencies and then install them first. In that process, I noticed that the which utility program wasn’t installed in the container.

Naturally, I installed the which utility first with this command:

dnf install -y which

The rlwrap dependencies are: glibc, ncurses, perl, readline, python, and git. Only the perl, python, and git are missing from the list of formal dependencies but there’s another dependency the epel-release package.

If you want to verify whether a package is installed, you can use the rpm command like this:

rpm -qa | grep package_name

I installed the perl programming environment (a big install) with this command:

dnf install -y perl

I installed the python3 with this command:

dnf install -y python3

I installed the git module with this command:

dnf install -y git

I installed the epel-release container with this command:

dnf install -y epel-release

After installing all of these, you’re now ready to install the core rlwrap utility program. Like the other installations, you use:

dnf install -y rlwrap

At this point, you need to create a sandboxed user account for the Docker instance because as a developer using the root user for simple tasks is a bad idea. While you could do this with a Docker command, the Oracle 23c Free edition raised a lock on the /etc/group file when I tried it. Naturally, that’s not a problem because you can connect as the root user with this syntax:

docker exec -it -u root oracle23c bash

As the root user, create a student account as a developer account in the Oracle 23c Free container:

useradd -u 501 -g dba -G users -d /home/student -s /bin/bash/ -c "Student" -n student

You’ll be unable to leverage the tnsnames.ora file unless you alter the prior command to replace dba with oinstall or add the following command:

usermod -a -G oinstall student

Exit the Oracle 23c Free container as the root user and reconnect as the student user with this syntax:

docker exec -it --user student oracle23c bash

While you’re connected as the root user, you should create an upload directory as a subdirectory of the $ORACLE_BASE directory. The $ORACLE_BASE directory in the Oracle Database 23c Free Docker image is the /opt/oracle directory.

You should use the following syntax to create the upload directory and change its permission to that of the Oracle Database 23c Free installation (for a future blog post on developing external table deployment on the Docker image):

mkdir /opt/oracle
chown -R oracle:install /opt/oracle/upload

You also can add the following student function to the Ubuntu student user’s .bashrc file. It means all you need to type to connect to the Oracle Database 23c Free Docker instance is “student“. I like shortcuts like this one, which let you leverage one-line Python commands.

student () 
{
    # Discover the fully qualified program name. 
    path=`which docker 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "docker" ]]; then
        python -c "import subprocess; subprocess.run(['docker exec -it --user student oracle23c bash'], shell=True)" 
    else
        echo "Docker is unavailable: Install the docker package."
    fi
}

Open a Ubuntu Terminal shell and type a student function name to connect to the Docker Oracle Database 23c Free instance where you can now test things like external tables with the SQL*Plus command line without installing it on the Ubuntu local operating system.

student@student-virtual-machine:~$ student
[student@d28375f0c43f ~]$ sqlplus c##student/student@free
 
SQL*Plus: Release 23.0.0.0.0 - Production on Wed Jan 3 02:14:22 2024
Version 23.3.0.23.09
 
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
 
Last Successful login time: Wed Jan 03 2024 01:56:44 +00:00
 
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
 
SQL>

Then, I added this sqlplus function to the /home/student/.bashrc file, which is owned by the student user. However, I also added the instruction to change to the student user’s home directory because the Oracle 23c Free container will connect you to the /home/oracle directory by default. I also added the default long list (ll) alias to the .bashrc file.

sqlplus () 
{
    # Discover the fully qualified program name. 
    path=`which rlwrap 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi;
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}"
    else
        echo "Command-line history unavailable: Install the rlwrap package."
        $ORACLE_HOME/bin/sqlplus "${@}"
    fi
}
 
# Change to the user's home directory.
cd ${HOME}
 
# Create a long list alias:
alias ll='ls -l --color=auto'

After you’ve configured your student user, you can configure the oracle user account to work like a regular server. Exit the Docker Oracle Database 23c Free as the student user, then connect as the root user with this command:

docker exec -it -u root oracle23c bash

As the root user you can become the oracle user with the following command:

su - oracle

Now, add the following .bashrc shell in the /home/oracle directory:

# The oracle user's .bashrc
 
# Source global definitions
if [ -f /etc/bashrc ]; then
	. /etc/bashrc
fi
 
# User specific environment
if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]
then
    PATH="$HOME/.local/bin:$HOME/bin:$PATH"
fi
export PATH
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions
export ORACLE_SID=FREE
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree
export PATH=$PATH:/$ORACLE_HOME/bin
 
# Change to the user's home directory.
cd ${HOME}
 
# Create a long list alias:
alias ll='ls -l --color=auto'
 
sqlplus () 
{
    # Discover the fully qualified program name. 
    path=`which rlwrap 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi;
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}"
    else
        echo "Command-line history unavailable: Install the rlwrap package."
        $ORACLE_HOME/bin/sqlplus "${@}"
    fi
}

You need to manually source the .bashrc for the oracle user because it’s not an externally available user. Use this syntax to connect as the internal user:

sqlplus / as sysdba

It’ll display:

SQL*Plus: RELEASE 23.0.0.0.0 - Production ON Wed Jan 3 07:08:11 2024
Version 23.3.0.23.09
 
Copyright (c) 1982, 2023, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 23c Free RELEASE 23.0.0.0.0 - Develop, Learn, AND Run FOR Free
Version 23.3.0.23.09
 
SQL>

After all this, I can now click the “up arrow” to edit any of the sqlplus command history. If you like to work inside sqlplus natively, this should help you.

Written by maclochlainn

December 20th, 2023 at 11:11 pm