MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle DBA’ tag

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

OracleDB Python Tutorial 1

without comments

This shows you how to get Python working with the Oracle Database 23c in Docker or Podman on Ubuntu. You can find useful connection strings for this in Oracle Database Free Get Started.

  1. First step requires you to install the pip3/span> utility on Ubuntu.

    sudo apt install -y python3-pip

  2. Second step requires that you pip3 install the oracledb library:

    sudo pip3 install oracledb --upgrade

  3. Third step requires you write a Python program to test your connection to Oracle Database 23c Free, like:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Print a connection message.
      print("Connected to the Oracle", db.version, "database.")
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close connection. 
      db.close()

    The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.

    It should print:

    Connected to the Oracle 23.3.0.23.9 database.
  4. Fourth step requires you write a Python program to test querying data from an Oracle Database 23c Free instance. I created the following avenger table and seeded it with six Avengers.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    /* Conditionally drop the table. */
    DROP TABLE IF EXISTS avenger;
     
    /* Create the table. */
    CREATE TABLE avenger
    ( avenger_id      NUMBER
    , first_name      VARCHAR2(20)
    , last_name       VARCHAR2(20)
    , character_name  VARCHAR2(20));
     
    /* Seed the table with data. */
    INSERT INTO avenger VALUES (1,'Anthony','Stark','Iron Man');
    INSERT INTO avenger VALUES (2,'Thor','Odinson','God of Thunder');
    INSERT INTO avenger VALUES (3,'Steven','Rogers','Captain America');
    INSERT INTO avenger VALUES (4,'Bruce','Banner','Hulk');
    INSERT INTO avenger VALUES (5,'Clinton','Barton','Hawkeye');
    INSERT INTO avenger VALUES (6,'Natasha','Romanoff','Black Widow');

    Then, I extended the program logic to include a cursor and for loop to read the values from the avenger table:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Create a cursor.
      cursor = db.cursor()
     
      # Execute a query.
      cursor.execute("SELECT   character_name " +
                     ",        first_name " +
                     ",        last_name " +
                     "FROM     avenger " +
                     "ORDER BY character_name")
     
      # Read the contents of the cursor.
      for row in cursor:
        print(row[0] + ':',row[2] + ',',row[1])
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close cursor and connection.
      cursor.close() 
      db.close()

    The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.

    It should print:

    Black Widow: Romanoff, Natasha
    Captain America: Rogers, Steven
    God of Thunder: Odinson, Thor
    Hawkeye: Barton, Clinton
    Hulk: Banner, Bruce
    Iron Man: Stark, Anthony
  5. Fifth step requires you write a Python program to test querying data filtered by a local variable from an Oracle Database 23c Free instance. This example looks only for the Hulk among the six Avengers.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Create a cursor.
      cursor = db.cursor()
     
      # Execute a query.
      stmt = "SELECT   character_name "            \
             ",        first_name "                \
             ",        last_name "                 \
             "FROM     avenger "                   \
             "WHERE    character_name = :avenger " \
             "ORDER BY character_name"
     
      # Execute with bind variable.
      cursor.execute(stmt, avenger = "Hulk")
     
      # Read the contents of the cursor.
      for row in cursor:
        print(row[0] + ':',row[2] + ',',row[1])
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close cursor and connection. 
      cursor.close() 
      db.close()

    It should print:

    Hulk: Banner, Bruce

As always, I hope this puts everything together for setting up Python with Oracle Database 23c Free.

Written by maclochlainn

December 10th, 2023 at 12:27 am

Oracle 23c MLE JavaScript

without comments

Oracle Database 23c has some really great features. One of those features is the ability to write JavaScript functions inside the database. Unfortunately, I noticed a couple omissions in Oracle’s JavaScript Developer’s Guide. Specifically, I encountered a privilege error while testing the example in the 5.1 Call Specifications for Functions example.

After having typed it all in to a simple script file, I encountered the following error message when trying to create an MLE MODULE:

CREATE OR REPLACE MLE MODULE jsmodule
*
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES
Help: https://docs.oracle.com/error-help/db/ora-06575

That was easy enough to fix. As the system user you need to grant the following two additional privileges to the user (based on my earlier sandbox pluggable user configuration user setup), which in my case is the c##student pluggable user:

GRANT CREATE MLE TO c##student;
GRANT EXECUTE ON JAVASCRIPT TO c##student;

You need to enable the SQL*PLus SERVEROUTPUT environment parameter for Oracle’s code example to work when you run the greet procedure from SQL*Plus command-line interface (CLI).

Below is the modified example file (unfortunately, the GeSHi formatting promotes log and return in the JavaScript functions to uppercase because they’re assumed as keywords in Oracle SQL):

  1. Conditionally drop the MLE MODULE.

    DROP MLE MODULE IF EXISTS jsmodule;
  2. Create a MLE Call Specification.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    CREATE OR REPLACE
      MLE MODULE jsmodule
      LANGUAGE JAVASCRIPT AS
     
      export FUNCTION greet(str) {
        console.LOG(`Hello ${str}!`)
      }
     
      export FUNCTION CONCAT(str1, str2) {
        RETURN str1 + " " + str2 + "!";
      }
    /
  3. The greet function doesn’t return a value and uses the Nodejs console.log() function to write a string, which means you must wrap the JavaScript function as a procedure because it returns a void type.

    1
    2
    3
    4
    5
    
    CREATE OR REPLACE
      PROCEDURE greet(str IN VARCHAR2)
      AS MLE MODULE jsmodule
      SIGNATURE 'greet(string)';
    /
  4. Wrap the concatenate function as a function because it returns a value inside the JavaScript.

    1
    2
    3
    4
    5
    6
    7
    
    CREATE OR REPLACE
      FUNCTION concatenate
      ( str1  VARCHAR2
      , str2  VARCHAR2 ) RETURN VARCHAR2 AS
      MLE MODULE jsmodule
      SIGNATURE 'concat(string, string)';
    /
  5. Enable to the SERVEROUTPUT environment variable to display messages printed from inside stored procedures or other PL/SQL blocks.

    SET SERVEROUTPUT ON SIZE UNLIMITED
  6. Call the greet() function’s procedure wrapper.

    CALL greet('Peter');

    It returns “Hello Peter!.

  7. Query the result from the concatenate() function’s function wrapper. */

    SELECT concatenate('Hello','World');

    It returns “Hello World!.

As always, I hope this helps somebody working through the same issue.

Written by maclochlainn

December 5th, 2023 at 12:41 am

SQL Developer on Ubuntu

without comments

The following steps show how to install and configure SQL Developer on Ubuntu 22.0.4 to work with Oracle Database 23c Free in a Docker container. The first steps are installing the Java Runtime Environment and Java Development Kit, and then downloading, installing and configuring SQL Developer. These are the required steps:

  1. Install the Java Runtime Environment:

    sudo apt install default-jre

    The log file for this is:

  2. Install the Java Runtime Environment:

    sudo apt install -y default-idk

    The log file for this is:

  3. Download SQL Developer from here; and then install SQL Developer to the /opt directory on your Ubuntu local instance:

    Use the following command to unzip the SQL Developer files to the /opt directory:

    sudo unzip ~/Downloads/sqldeveloper-23.1.0.097.1607-no-jre.zip
  4. Create the following /usr/local/bin/sqldeveloper symbolic link:

    sudo ln -s /opt/sqldeveloper/sqldeveloper.sh /usr/local/bin/sqldeveloper
  5. Edit the /opt/sqldeveloper/sqldeveloper.sh file by replacing the following line:

    cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

    with this version:

    /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper $*
  6. Now, you can launch SQL Developer from any location on your local Ubuntu operating system, like:

    sqldeveloper
  7. You can now connect as the system user through SQL Developer to the Oracle Database 23c Free Docker instance with the following connection information:

  8. You can also create a Desktop shortcut by creating the sqldeveloper.desktop file in the /usr/share/applications directory. The SQL Developer icon is provided in the sqldeveloper base directory.

    You should create the following sqldeveloper.desktop file to use a Desktop shortcut:

    [Desktop Entry]
    Name=Oracle SQL Developer
    Comment=SQL Developer from Oracle
    GenericName=SQL Tool
    Exec=/usr/local/bin/sqldeveloper
    Icon=/opt/sqldeveloper/icon.png
    Type=Application
    StartupNotify=true
    Categories=Utility;Oracle;Development;SQL;

You can create a sandboxed container c##student user with the instructions from this earlier post on Oracle Database 18c, which remains the correct syntax.

As always, I hope this helps those trying to accomplish this task.

Written by maclochlainn

December 3rd, 2023 at 12:11 am

Disk Space Allocation

without comments

It’s necessary to check for adequate disk space on your Virtual Machine (VM) before installing Oracle 23c Free in a Docker container or as a podman service. Either way, it requires about 13 GB of disk space. On Ubuntu, the typical install of a VM allocates 20 GB and a 500 MB swap. You need to create a 2 GB swap when you install Ubuntu or plan to change the swap, as qualified in this excellent DigitalOcean article. Assuming you installed it with the correct swap or extended your swap area, you can confirm it with the following command:

sudo swapon --show

It should return something like this:

NAME      TYPE SIZE USED PRIO
/swapfile file 2.1G 1.2G   -2

Next, check your disk space allocation and availability with this command:

df -h

This is what was in my instance with MySQL and PostgreSQL databases already installed and configured with sandboxed schemas:

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           388M  2.1M  386M   1% /run
/dev/sda3        20G   14G  4.6G  75% /
tmpfs           1.9G   28K  1.9G   1% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
/dev/sda2       512M  6.1M  506M   2% /boot/efi
tmpfs           388M  108K  388M   1% /run/user/1000

Using VMware Fusion on my Mac (Intel-based i9), I changed the allocated space from 20 GB to 40 GB by navigating to Virtual Machine, Settings…, Hard Disk. I entered 40.00 as the disk size and clicked the Pre-allocate disk space checkbox before clicking the Apply button, as shown in below. This added space is necessary because Oracle Database 23c Free as a Docker instance requires almost 10 GB of local space.

After clicking the Apply button, I checked Ubuntu with the “df -h” command and found there was no change. That’s unlike doing the same thing on AlmaLinux or a RedHat distribution, which was surprising.

The next set of steps required that I manually add the space to the Ubuntu instance:

  1. Start the Ubuntu VM and check the instance’s disk information with fdisk:

    sudo fdisk -l

    The log file for this is:

    After running fdisk, I rechecked disk allocation with df -h and saw no change:

    Filesystem      Size  Used Avail Use% Mounted on
    tmpfs           388M  2.1M  386M   1% /run
    /dev/sda3        20G   14G  4.6G  75% /
    tmpfs           1.9G   28K  1.9G   1% /dev/shm
    tmpfs           5.0M  4.0K  5.0M   1% /run/lock
    /dev/sda2       512M  6.1M  506M   2% /boot/efi
    tmpfs           388M  108K  388M   1% /run/user/1000
  2. So, I installed Ubuntu’s user space utility gparted:

    sudo apt install gparted

    The log file for this is:

  3. After installing the gparted utility (manual can be found here), you can launch it with the following syntax:

    sudo apt install gparted

    You’ll see the following in the console, which you can ignore.

    GParted 1.3.1
    configuration --enable-libparted-dmraid --enable-online-resize
    libparted 3.4

    It launches a GUI interface that should look something like the following:

    Right-click on the /dev/sda3 Partition and the GParted application will present the following context popup menu. Click the Resize/Move menu option.

    The attempt to resize the disk at this point GParted will raise a read-only exception like the following:

    You might open a new shell and fix the disk at the command-line but you’ll need to relaunch gparted regardless. So, you should close gparted and run the following commands:

    sudo mount -o remount -rw /
    sudo mount -o remount -rw /var/snap/firefox/common/host-hunspell

    When you relaunch GParted, you see that the graphic depiction has changed when you right-click on the /dev/sda3 Partition as follows:

    Click on the highlighted box with the arrow and drag it all the way to the right. It will then show you something like the following.

    Click the Resize button to make the change and add the space to the Ubuntu file system and see something like the following in Gparted:

    Choose Edit in the menu bar and then Apply All Operations to effect the change in the disk allocation. The last dialog will require you to verify you want to make the changes. Click the Apply button to make the changes.

    Click the close for the GParted application and then you can rerun the following command:

    df -h

    You will see that you now have 19.5 GB of additional space:

    Filesystem      Size  Used Avail Use% Mounted on
    tmpfs           388M  2.2M  386M   1% /run
    /dev/sda3        39G 19.5G   23G  39% /
    tmpfs           1.9G   28K  1.9G   1% /dev/shm
    tmpfs           5.0M  4.0K  5.0M   1% /run/lock
    /dev/sda2       512M  6.1M  506M   2% /boot/efi
    tmpfs           388M  116K  388M   1% /run/user/1000
  4. Finally, you can now successfully download the latest Docker version of Oracle Database 23c Free with the following command:

    docker run --name oracle23c -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=cangetin container-registry.oracle.com/database/free:latest

    Since you haven’t downloaded the container, you’ll get a warning that it is unable to find the image before it discovers it and downloads it. This will take several minutes. At the conclusion, it will start the Oracle Database Net Listener and begin updating files. the updates may take quite a while to complete.

    The basic download console output looks like the following and if you check your disk space you’ve downloaded about 14 GB in the completed container.

    Unable to find image 'container-registry.oracle.com/database/free:latest' locally
    latest: Pulling from database/free
    089fdfcd47b7: Pull complete 
    43c899d88edc: Pull complete 
    47aa6f1886a1: Pull complete 
    f8d07bb55995: Pull complete 
    c31c8c658c1e: Pull complete 
    b7d28faa08b4: Pull complete 
    1d0d5c628f6f: Pull complete 
    db82a695dad3: Pull complete 
    25a185515793: Pull complete 
    Digest: sha256:5ac0efa9896962f6e0e91c54e23c03ae8f140cf6ed43ca09ef4354268a942882
    Status: Downloaded newer image for container-registry.oracle.com/database/free:latest

    My detailed log file for the complete recovery operation is:

  5. You can connect to the Oracle Database 23c Free container with the following syntax:

    docker exec -it -u root oracle23c bash

    At the command-line, you connect to the Oracle Database 23c Free container with the following syntax:

    sqlplus system/cangetin@free

    You have arrived at the Oracle SQL prompt:

    SQL*Plus: Release 23.0.0.0.0 - Production on Fri Dec 1 00:13:55 2023
    Version 23.3.0.23.09
     
    Copyright (c) 1982, 2023, Oracle.  All rights reserved.
     
    Last Successful login time: Thu Nov 30 2023 23:27:54 +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>

As always, I hope this helps those trying to work with the newest Oracle stack.

Written by maclochlainn

December 1st, 2023 at 3:08 pm

SQL Developer & PostgreSQL

without comments

I had a request from one of the adjunct professors to connect SQL Developer to the PostgreSQL database. This is in support of our database programming class that teaches students how to write PL/SQL against the Oracle database and pgPL/SQL against the PostgreSQL database. We also demonstrate transactional management through Node.js, Python and Java.

Naturally, this is also a frequent step taken by those required to migrate PostgreSQL data models to an Oracle database. While my final solution requires mimicking Oracle’s database user to schema, it does work for migration purposes. I’ll update this post when I determine how to populate the database drop-down list.

The first step was figuring out where to put the PostgreSQL JDBC Java ARchive (.jar) file on a Linux distribution. You navigate to the end-user student account in a Terminal and change to the .sqldeveloper directory. Then, create a jdbc subdirectory as the student user with the following command:

mkdir /home/student/.sqldeveloper/jdbc

Then, download the most current PostgreSQL JDBC Java ARchive (.jar) file and copy it into the /home/student/.sqldeveloper/jdbc, which you can see afterward with the following command:

ll /home/student/.sqldeveloper/jdbc

It should display:

-rw-r--r--. 1 student student 1041081 Aug  9 13:46 postgresql-42.3.7.jar

The next series of steps are done within SQL Developer. Launch SQL Developer and navigate to Tools and Preferences, like this:

Inside the Preferences dialog, navigate to Database and Third Party JDBC Drivers like shown and click the Add Entry button to proceed:

Inside the Select Path Entry dialog, select the current PostgreSQL JDBC Java ARchive (.jar) file, which is postgresql-42-3.7.jar in this example. Then, click the Select button.

You are returned to the Preferences dialog as shown below. Click the OK button to continue.

After completing the 3rd Party Java Driver setup, you attempt to create a new connection to the PostgreSQL database. You should see that you now have two available Database Type values: Oracle and PostgreSQL, as shown below:

When you click on the PostgreSQL Database Type, the dialog updates to the following view. Unfortunately, I couldn’t discover how to set the values in the list for the Choose Database drop down. Naturally, a sandboxed user can’t connect to the PostgreSQL database without qualifying the database name.

Unless you qualify the PostgreSQL database or connect as the postgres user with a privileged password, SQL Developer translates the absence of a database selection to a database name equivalent to the user’s name. That’s the default behavior for the Oracle database but differs from the behavior for MySQL, PostgreSQL, and Microsoft SQL Server. It returns the following

Status: Failure - Test failed: FATAL: database "student" does not exist

As seen in the diaglog’s result when testing the connection:

Based on my hunch and not knowing how to populate the database field for the connection, I did the following:

  • Created a Linux OS videodb user.
  • Copied the .bashrc file with all the standard Oracle environment variables.
  • Created the /home/videodb/.sqldeveloper/jdbc directory.
  • Copied the postgresql-42.3.7.jar into the new jdbc directory.
  • Connected as the postgres super user and created the PostgreSQL videodb user with this syntax:

    CREATE USER videodb
      WITH ROLE dba
           ENCRYPTED PASSWORD 'cangetin';
  • As the postgres super user, granted the following privileges:

    -- Grant privileges on videodb database videodb user.
    GRANT ALL ON DATABASE "videodb" TO "videodb";
     
    -- Connect to the videodb database.
    \c
     
    -- Grant privileges.
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO videodb;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO videodb;
  • Added the following line to the pg_hba.conf file in the /var/lib/pgsql/15/data directory as the postgres user:

    local   all             videodb                                 peer
  • Connected as the switched from the student to videodb Linux user, and launched SQL Developer. Then, I used the Tools menu to create the 3rd party PostgreSQL JDBC Java ARchive (.jar) file in context of the SQL Developer program. Everything completed correctly.
  • Created a new PostgreSQL connection in SQL Developer and tested it with success as shown:

  • Saving the new PostgreSQL connection, I opened the connection and could run SQL statements and display the catalog information, as shown:

    Connected as the videodb user to the videodb database I can display tables owned by student and videodb users:

    -- List tables.
    \d
     
                       List of relations
     Schema |           Name           |   Type   |  Owner
    --------+--------------------------+----------+---------
     public | new_hire                 | table    | student
     public | new_hire_new_hire_id_seq | sequence | student
     public | oracle_test              | table    | videodb
    (3 rows)

    In SQL Developer, you can also inspect the tables, as shown:

At this point, I’m working on trying to figure out how to populate the database drop-down table. However, I’ve either missed a key document or it’s unfortunate that SQL Developer isn’t as friendly as MySQL Workbench in working with 3rd Party drivers.

Written by maclochlainn

August 8th, 2023 at 11:29 pm

Listener for APEX

without comments

Unless dbca lets us build the listener.ora file, we often leave off some component. For example, running listener control program the following status indicates an incorrectly configured listener.ora file.

lsnrctl status

It returns the following, which displays an endpoint for the XDB Server (I’m using Oracle Database 11g XE because it’s pre-containerized and has a small testing footprint):

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 00:59:06
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                21-MAR-2023 21:17:37
Uptime                    2 days 3 hr. 41 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

The listener is missing the second SID_LIST_LISTENER value of CLRExtProc value. A complete listener.ora file should be as follows for the Oracle Database XE:

# listener.ora Network Configuration FILE:
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

With this listener.ora file, the Oracle listener control utility will return the following correct status, which hides the XDB Server’s endpoint:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 02:38:57
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-MAR-2023 02:38:15
Uptime                    0 days 0 hr. 0 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

It seems a number of examples on the web left the SID_LIST_LISTENER value of CLRExtProc value out of the listener.ora file. As always, I hope this helps those looking for a complete solution rather than generic instructions without a concrete example.

Written by maclochlainn

March 24th, 2023 at 1:00 am

GROUP BY Quirk

without comments

It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:

“How would you get all students’ names and for each student the number of courses that the
student has registered for?”

They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.

For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:

SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;

While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.

In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:

SELECT   a.studentname
         *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

The correct way to write the GROUP BY is:

SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

Then, it would return:

Student Name                   Course IDs
------------------------------ ----------
Montgomery Scott                        1
Leonard McCoy                           2
James Tiberus Kirk                      3

For reference, here’s a complete test case for MySQL:

/* Drop table conditionally. */
DROP TABLE IF EXISTS students;
 
/* Create table. */
CREATE TABLE students
( studentID    int unsigned primary key auto_increment
, studentName  varchar(30));
 
/* Drop table conditionally. */
DROP TABLE IF EXISTS courses;
 
/* Create table. */
CREATE TABLE courses
( courseid    int unsigned primary key auto_increment
, coursename  varchar(40));
 
/* Drop table conditionally. */
DROP TABLE IF EXISTS registeredcourses;
 
/* Create table. */
CREATE TABLE registeredcourses
( courseid    int unsigned
, studentid   int unsigned );
 
/* Insert into students. */
INSERT INTO students
( studentName )
VALUES
 ('James Tiberus Kirk')
,('Leonard McCoy')
,('Montgomery Scott');
 
/* Insert into courses. */
INSERT INTO courses
( coursename )
VALUES
 ('English Literature')
,('Physics')
,('English Composition')
,('Botany')
,('Mechanical Engineering');
 
/* Insert into registeredcourses. */
INSERT INTO registeredcourses
( studentid
, courseid )
VALUES
 (1,1)
,(1,3)
,(1,4)
,(2,2)
,(2,5)
,(3,4); 
 
/* Check global sql_mode to ensure only_full_group_by is set. */
SELECT @@GLOBAL.SQL_MODE;
 
/* Query with a column not found in the SELECT-list. */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;
 
/* Query consistent with ANSI SQL:1992 */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

and, another complete test case for Oracle:

/* Drop tabhe unconditionallly. */
DROP TABLE students;
 
/* Create table. */
CREATE TABLE students
( studentID    NUMBER PRIMARY KEY
, studentName  VARCHAR(30));
 
/* Drop table unconditionally. */
DROP TABLE courses;
 
/* Create table. */
CREATE TABLE courses
( courseid    NUMBER PRIMARY KEY
, coursename  VARCHAR(40));
 
/* Drop table unconditionally. */
DROP TABLE registeredcourses;
 
/* Create table. */
CREATE TABLE registeredcourses
( courseid    NUMBER
, studentid   NUMBER );
 
/* Insert values in student. */
INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk');
INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy');
INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott');
 
/* Insert values in courses. */
INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature');
INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics');
INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition');
INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany');
INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering');
 
/* Insert values into registeredcourses. */
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); 
 
/* Non-ANSI SQL GROUP BY statement. */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;
 
/* ANSI SQL GROUP BY statement. */
SELECT   a.studentname AS "Student Name"
,        COUNT(b.courseid) AS "Course IDs"
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

I hope this helps those learning the correct way to write SQL.

Written by maclochlainn

January 12th, 2023 at 11:30 pm

DML Event Management

without comments

Data Manipulation Language (DML)

DML statements add data to, change data in, and remove data from tables. This section examines four DML statements—the INSERT, UPDATE, DELETE, and MERGE statements—and builds on concepts of data transactions. The INSERT statement adds new data, the UPDATE statement changes data, the DELETE statement removes data from the database, and the MERGE statement either adds new data or changes existing data.

Any INSERT, UPDATE, MERGE, or DELETE SQL statement that adds, updates, or deletes rows in a table locks rows in a table and hides the information until the change is committed or undone (that is, rolled back). This is the nature of ACID-compliant SQL statements. Locks prevent other sessions from making a change while a current session is working with the data. Locks also restrict other sessions from seeing any changes until they’re made permanent. The database keeps two copies of rows that are undergoing change. One copy of the rows with pending changes is visible to the current session, while the other displays committed changes only.

ACID Compliant Transactions

ACID compliance relies on a two-phase commit (2PC) protocol and ensures that the current session is the only one that can see new inserts, updated column values, and the absence of deleted rows. Other sessions run by the same or different users can’t see the changes until you commit them.

ACID Compliant INSERT Statements

The INSERT statement adds rows to existing tables and uses a 2PC protocol to implement ACID- compliant guarantees. The SQL INSERT statement is a DML statement that adds one or more rows to a table. Oracle supports a VALUES clause when adding a single-row, and support a subquery when adding one to many rows.

The figure below shows a flow chart depicting an INSERT statement. The process of adding one or more rows to a table occurs during the first phase of an INSERT statement. Adding the rows exhibits both atomic and consistent properties. Atomic means all or nothing: it adds one or more rows and succeeds, or it doesn’t add any rows and fails. Consistent means that the addition of rows is guaranteed whether the database engine adds them sequentially or concurrently in threads.

Concurrent behaviors happen when the database parallelizes DML statements. This is similar to the concept of threads as lightweight processes that work under the direction of a single process. The parallel actions of a single SQL statement delegate and manage work sent to separate threads. Oracle supports all ACID properties and implements threaded execution as parallel operations. All tables support parallelization.

After adding the rows to a table, the isolation property prevents any other session from seeing the new rows—that means another session started by the same user or by another user with access to the same table. The atomic, consistent, and isolation properties occur in the first phase of any INSERT statement. The durable property is exclusively part of the second phase of an INSERT statement, and rows become durable when the COMMIT statement ratifies the insertion of the new data.

ACID Compliant UPDATE Statements

An UPDATE statement changes column values in one-to-many rows. With a WHERE clause, you update only rows of interest, but if you forget the WHERE clause, an UPDATE statement would run against all rows in a table. Although you can update any column in a row, it’s generally bad practice to update a primary or foreign key column because you can break referential integrity. You should only update non-key data in tables—that is, the data that doesn’t make a row unique within a table.

Changes to column values are atomic when they work. For scalability reasons, the database implementation of updates to many rows is often concurrent, in threads through parallelization. This process can span multiple process threads and uses a transaction paradigm that coordinates changes across the threads. The entire UPDATE statement fails when any one thread fails.

Similar to the INSERT statement, UPDATE statement changes to column values are also hidden until they are made permanent with the application of the isolation property. The changes are hidden from other sessions, including sessions begun by the same database user.

It’s possible that another session might attempt to lock or change data in a modified but uncommitted row. When this happens, the second DML statement encounters a lock and goes into a wait state until the row becomes available for changes. If you neglected to set a timeout value for the wait state, such as this clause, the FOR UPDATE clause waits until the target rows are unlocked:

WAIT n

As the figure below shows, actual updates are first-phase commit elements. While an UPDATE statement changes data, it changes only the current session values until it is made permanent by a COMMIT statement. Like the INSERT statement, the atomic, consistent, and isolation properties of an UPDATE statement occur during the first phase of a 2PC process. Changes to column values are atomic when they work. Any column changes are hidden from other sessions until the UPDATE statement is made permanent by a COMMIT or ROLLBACK statement, which is an example of the isolation property.

Any changes to column values can be modified by an ON UPDATE trigger before a COMMIT statement. ON UPDATE triggers run inside the first phase of the 2PC process. A COMMIT or ROLLBACK statement ends the transaction scope of the UPDATE statement.

The Oracle database engine can dispatch changes to many threads when an UPDATE statement works against many rows. UPDATE statements are consistent when these changes work in a single thread-of-control or across multiple threads with the same results.

As with the INSERT statement, the atomic, consistent, and isolation properties occur during the first phase of any UPDATE statement, and the COMMIT statement is the sole activity of the second phase. Column value changes become durable only with the execution of a COMMIT statement.

ACID Compliant DELETE Statements

A DELETE statement removes rows from a table. Like an UPDATE statement, the absence of a WHERE clause in a DELETE statement deletes all rows in a table. Deleted rows remain visible outside of the transaction scope where it has been removed. However, any attempts to UPDATE those deleted rows are held in a pending status until they are committed or rolled back.

You delete rows when they’re no longer useful. Deleting rows can be problematic when rows in another table have a dependency on the deleted rows. Consider, for example, a customer table that contains a list of cell phone contacts and an address table that contains the addresses for some but not all of the contacts. If you delete a row from the customer table that still has related rows in the address table, those address table rows are now orphaned and useless.

As a rule, you delete data from the most dependent table to the least dependent table, which is the opposite of the insertion process. Basically, you delete the child record before you delete the parent record. The parent record holds the primary key value, and the child record holds the foreign key value. You drop the foreign key value, which is a copy of the primary key, before you drop the primary key record. For example, you would insert a row in the customer table before you insert a row in the address table, and you delete rows from the address table before you delete rows in the customer table.

The figure below shows the logic behind a DELETE statement. Like the INSERT and UPDATE statements, acid, consistency, and isolation properties of the ACID-compliant transaction are managed during the first phase of a 2PC. The durability property is managed by the COMMIT or ROLLBACK statement.

There’s no discussion or diagrams for the MERGE statement because it does either an INSERT or UPDATE statement based on it’s internal logic. That means a MERGE statement is ACID compliant like an INSERT or UPDATE statement.

Written by maclochlainn

January 1st, 2023 at 8:05 pm

AlmaLinux Install & Configuration

without comments

This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.

I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.

If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.