Chapter 8 - Interactive Oracle PDB Selector Script for SQL*Plus (CDB Environment)

This is a Bash script that lets you interactively choose a PDB (Pluggable Database) and jumps you straight into SQL*Plus, already switched to the right container.

vi connect_pdb.sh

Paste the following inside vi

#!/bin/bash

export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=FREE

PDBS=("FREEPDB1" "FREEPDB2" "FREEPDB3" "FREEPDB4" "FREEPDB5")

list_pdbs() {
    echo "Available Oracle PDBs:"
    for i in "${!PDBS[@]}"; do
        echo "$((i+1)). ${PDBS[$i]}"
    done
}

echo "Connect to an Oracle PDB (interactive session)"
echo "----------------------------------------------"
list_pdbs
echo "0. Exit"

read -p "Select the PDB number: " choice

if [[ $choice -eq 0 ]]; then
    echo "Exiting..."
    exit 0
elif [[ $choice -gt 0 && $choice -le ${#PDBS[@]} ]]; then
    selected_pdb="${PDBS[$((choice-1))]}"
    echo "Connecting to PDB $selected_pdb..."

    TMP_SCRIPT=$(mktemp)
    {
        echo "alter session set container=$selected_pdb;"
        echo "show con_name;"
        echo "prompt Connected to PDB $selected_pdb."
    } > "$TMP_SCRIPT"

    sqlplus / as sysdba @"$TMP_SCRIPT"
    rm -f "$TMP_SCRIPT"
else
    echo "Invalid option!"
    exit 1
fi

Save and exit vi, then run:

chmod +x connect_pdb.sh

./connect_pdb.sh