MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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

Python3 on PostgreSQL

without comments

The necessary Python 3 driver for connections to the PostgreSQL database is python3-psycopg2, as qualified by this earlier post with full test examples for Red Hat distributions. You can install it on Ubuntu with the following command:

sudo apt-get install -y python3-psycopg2

As always, I hope this helps those looking for a solution. Also, remember the referenced post above provides Linux distribution neutral full solutions.

Written by maclochlainn

December 3rd, 2023 at 11:00 pm

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 1 v.s. SQL 3

without comments

In Alan Beaulieu’s wonderful book Learning SQL: Generate, Manipulate, and Retrieve Data, he uses a SQL 1 to demonstrates a CROSS JOIN in MySQL counting from 1 to 100. My students always find it difficult to read because the subqueries take so much space it makes it difficult to see the process, for example he gives this SQL 1 solution:

SELECT ones.x + tens.x + 1 AS counter
FROM
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x ) ones CROSS JOIN
 (SELECT 0 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 20 AS x UNION ALL
  SELECT 30 AS x UNION ALL
  SELECT 40 AS x UNION ALL
  SELECT 50 AS x UNION ALL
  SELECT 60 AS x UNION ALL
  SELECT 70 AS x UNION ALL
  SELECT 80 AS x UNION ALL
  SELECT 90 AS x ) tens
ORDER BY counter;

While anybody with a command of SQL should be able to see how it works, for those new to SQL it’s difficult. It’s more effective to use a Common Table Expression with the WITH clause because the derived tables become variables in the scope of the WITH clause and the final query works more like a CROSS JOIN between two tables:

WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT 0 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 20 AS x UNION ALL
  SELECT 30 AS x UNION ALL
  SELECT 40 AS x UNION ALL
  SELECT 50 AS x UNION ALL
  SELECT 60 AS x UNION ALL
  SELECT 70 AS x UNION ALL
  SELECT 80 AS x UNION ALL
  SELECT 90 AS x )
SELECT ones.x + tens.x + 1 AS counter
FROM ones CROSS JOIN tens
ORDER BY counter;

As always, I hope this helps somebody trying to sort out the syntax and workflow.

Written by maclochlainn

November 7th, 2023 at 10:10 pm

Posted in MySQL,MySQL 8,sql

Tagged with

PostgreSQL User Password

without comments

Miraculous events happen on my students’ computers almost weekly. For example, one couldn’t remember their student user’s password, which I set by default. How did they change it? They don’t recall.

Assuming you have sudoer authority on a Linux operating system, you can change a PostgreSQL user’s password as follows:

  1. Assume the root user’s role with the following command:

    sudo sh
  2. As the root user, assume the postgres user’s role with the following command:

    su - postgres
  3. As the postgres user, connect to the PostgreSQL database with the following command:

    psql -U postgres
  4. Assuming the user’s name is student and you want to set the password to a trivial value like student, use the following command:

    ALTER USER student WITH ENCRYPTED PASSWORD 'student';

As always, I hope this helps somebody trying to sort out the syntax and workflow.

Written by maclochlainn

November 7th, 2023 at 9:41 pm

PL/pgSQL Test Q?

without comments

Intriguing little PostgreSQL PL/pgSQL test question posed by an interviewer of one of my students. Basically, how many times will this loop and what will it return or will it generate an error. (BTW, they wrapped it into a named function.)

DO
$$
BEGIN
  FOR i IN 0.2..1.5 LOOP
    RAISE NOTICE '%', i;
  END LOOP;
END;
$$;

It’ll loop three times and return 0, 1, and 2 because the double numbers entered as boundaries to the for-loop are implicitly case as integers.

Written by maclochlainn

October 26th, 2023 at 7:46 pm

Parallels + Ubuntu

without comments

Installing Parallels on my iStudio (M2) was straightforward because I let it install Windows 11. Then, when I wanted to install Ubuntu it wasn’t quite that easy. You just need to understand that you click the Parallels’ Window menu option and Control Center option.

The Control Center option provides the following dialog.

Click the + symbol to create a new virtualization and you get the following dialog; and choose the Download Ubuntu with x86_64 emulation if you want to install a Docker image that’s not ported to ARM, like the Oracle Database 19c.

It’ll then explain in this dialog that is uses Apple’s Rosetta 2 technology, which means you should be able to install an Intel architecture Docker image with Oracle Database 23c.

As always, I hope this helps those trying to sort out how to leverage a new stack.

Written by maclochlainn

October 19th, 2023 at 3:41 pm

Install Docker Ubuntu

without comments

Contrary to the instructions, you should do the following as a sudoer user:

sudo apt install -y docker.io

Install all dependency packages using the following command:

sudo snap install docker

You should see the following:

docker 20.10.24 from Canonical✓ installed

You can verify the Docker install with the following command:

sudo docker --version

It should show something like this:

Docker version 24.0.5, build 24.0.5-0ubuntu1~22.04.1

You can check the pulled containers with the following command but at this point there should be no pulled containers.

sudo docker images

At this point, a docker group already exists but you need to add your user to the docker group with the following command:

sudo usermod -aG docker $USER

Using the Docker Commands:

  • To activate the logging, utilize the -f parameter.
  • To divide JSON, use Docker by default; to extract individual keys, use JQ.
  • In your Container file, there are quite a few areas where commands may be specified.
  • Posting to the volumes could be more effective while the picture is being built.
  • Docker offers a highly efficient way to create an alias for its own built-in commands. This makes it easier to set up and handle lengthy and enormous orders. These alias values are stored in the directories /.bashrc or and /.bash_aliases.
  • Docker offers further assistance to remove unused code fragments from the installation of the container.
  • Docker always favors reading statements from the container file that have not changed. Therefore, time savings may be realized by arranging what is shown in the container file in a way that ensures the elements that are susceptible to change are shown towards the end of the document and those that are most likely to undergo change are shown at the top.

As always, I hope this helps.

Written by maclochlainn

October 18th, 2023 at 10:34 pm

Posted in Linux,Ubuntu

Tagged with

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