MacLochlainns Weblog

It’s always interesting to see students find the little nuances that SQL*Plus can generate. One of the first things we cover is the concept of calling PL/SQL interactively versus through an embedded call. The easiest and first exercise simply uses an insecure call like:

sqlplus -s student/student @call.sql

to the call.sql program:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3  BEGIN
  4    lv_input := '&1';
  5    dbms_output.put_line('['||lv_input||']');
  6  END;
  7  /

It prints the following to console:

Enter value for 1: machine
old   4:   lv_input := '&1';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

Then, we change the '&1' parameter variable to '&mystery' and retest the program, which prints the following to the console:

Enter value for mystery: machine
old   4:   lv_input := '&mystery';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

After showing a numeric and string input parameter, we remove the quotation from the lv_input input parameter and raise the following error:

Enter value for mystery: machine
old   4:   lv_input := &mystery;
new   4:   lv_input := machine;
  lv_input := machine;
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

The point of the exercise is to spell out that the default input value is numeric and that if you pass a string it becomes an identifier in the scope of the program. So, we rewrite the call.sql program file by adding a machine variable, like:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3    machine   VARCHAR2(20) := 'Mystery Machine';
  4  BEGIN
  5    lv_input := &mystery;
  6    dbms_output.put_line('['||lv_input||']');
  7  END;
  8  /

It prints the following:

Enter value for mystery: machine
old   5:   lv_input := &mystery;
new   5:   lv_input := machine;
[Mystery Machine]
 
PL/SQL procedure successfully completed.

The parameter name becomes an identifier and maps to the variable machine. That mapping means it prints the value of the machine variable.

While this is what we’d call a terminal use case, it is a fun way to illustrate an odd PL/SQL behavior. As always, I hope its interesting for those who read it.

PL/SQL is a great programming language as far as it goes but it lacks true type inheritance for its collections. While you can create an object type and subtype, you can’t work with collections of those types the same way. PL/SQL object type inheritance, unlike the Java class hierarchy and parallel array class hierarchy, only supports a class hierarchy. Effectively, that means:

• You can pass a subtype as a call parameter, or argument, to a parent data type in a function, procedure, or method signature, but
• You can’t pass a collection of a subtype as a call parameter, or argument, to a collection of parent type in a function, procedure, or method signature.

The limitation occurs because collections have their own data type, which is fixed when you create them. Worse yet, because Oracle has never seen fit to fix their two underlying code trees (23 years and counting since Oracle 8i), you have two types of collections using two distinct C/C++ libraries. You define collections of Attribute Data Types (ATDs) when you create a collection of a standard scalar data type, like NUMBER, VARCHAR2, or DATE. You define collection of User-Defined Data Types (UTDs) when you create a collection of a SQL UDT or PL/SQL-only RECORD data type. The former uses one C/C++ library and the latter another.

Now, Oracle even make the differences between Java and PL/SQL more complex because it treats collections known as tables, really lists in most programming languages, differently than varrays, or arrays. You create a TABLE collection, or list, when you create a table of a scalar or UDT data type. There are two options when you create these object types, and they are:

• You create an empty collection with a no element constructor, which means you’ll need to allocate memory before assigning element values later in your program.
• You create a populated collection with a comma-delimited list of elements.

Both approaches give you a list of elements with a densely populated index. A “densely populated index” is Oracle’s jargon for how they characterize a 1-based sequence of integers without any gaps (e.g., 1, 2, 3, …). The initial construction works the same way whether you create a TABLE or VARRAY collection type. Unfortunately, after you’ve built the collection behaviors change. If you use Oracle’s Collection API to delete one or more items from a TABLE collection type, you create gaps in the index’s sequence of values. That means you must use special logic to navigate across a TABLE collection type to ensure it doesn’t fail when encountering a gap in the numeric sequence.

For example, here’s a FOR-LOOP without the logic to vouchsafe a uninterrupted set of sequence values incrementing by a counter of 1 element at a time:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a for loop, which
     depends on densely populated index values. */
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||lv_list(i)||']');
  END LOOP;
END;
/

The program fails when it tries to read the second element of the table collection, which was previously removed. It raises the following error message after print the first element of the table collection:

[Moe]
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

Conveniently, Oracle’s Collection API provides an EXISTS method that we can use to check for the presence of an index’s value. Modifying line 16 by wrapping it in an IF-statement fixes one problem but identifies another:

15
16
17
18
19

  FOR i IN 1..lv_list.COUNT LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

The program no longer fails on a missing index value, or index gap, but it returns fewer lines of output than you might expect.

That’s because the Oracle Collection API’s COUNT method returns the number of elements currently allocated in memory not the number of original elements. We learn that when we deleted the second element, Oracle deleted the memory allocated for it as well. This is the type of behavior you might expect for a singly linked list. It prints:

[Moe]
[Larry]

One more change is required to count past and to the highest index value. One line 15, change the COUNT method call to the LAST method call, which returns the highest index value.

15
16
17
18
19

  FOR i IN 1..lv_list.LAST LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

It now prints the three stooges we would expect to see:

[Moe]
[Larry]
[Curly]

Realistically, a FOR-LOOP is not the best control structure for a collection. You should use a WHILE-LOOP and treat the incrementing value as an iterator rather than sequence index value. An iterator doesn’t worry about gaps in the sequence, it simply moves to the next element in the singly linked list. Here’s an example that uses the iterator approach with a WHILE-LOOP:

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

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a while loop, which
     doesn't depend on densely populated index values
     by setting the starting index value and increment
     as if with an iterator. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

The iterator approach prints the elements as:

[Moe]
[Larry]
[Curly]

You can reverse the process with the following changes to lines 20-24:

20
21
22
23
24

  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;

It prints the list backwards:

[Curly]
[Larry]
[Moe]

After covering the issues with sparsely populated, those with gaps in the sequence of indexes values, table collections, let’s examine how you must work around PL/SQL’s lack of a parallel array class hierarchy. The solution lies in combining two programming concepts:

• A function to pack the sparsely populated table collection into a densely populated one, and
• A package with overloaded functions that pack different table collections.

To develop the test case, let’s use an ADT collection because it’s the simplest to work with. The following creates a table collection of a thirty character long scalar string:

1
2
3

CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(30);
/

The following pack function takes a table collection of the thirty character long scalar string, evaluates the string for missing elements, and packs the existing elements into a densely populated list:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
  /* Declare a new list. */
  lv_new  LIST  := list();
BEGIN
  /* Read, check, and pack an old list into a new one. */
  FOR i IN 1..pv_list.LAST LOOP
    IF pv_list.EXISTS(i) THEN
      lv_new.EXTEND;
      lv_new(lv_new.COUNT) := pv_list(i);
    END IF;
  END LOOP;
  RETURN lv_new;
END;
/

This anonymous block tests the pack function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DECLARE
  /* Declare a list value. */
  lv_test  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.COUNT LOOP
    dbms_output.put_line('['||lv_test(i)||']');
  END LOOP;
END;
/

It prints the expected three string values:

[Moe]
[Larry]
[Curly]

Now, let’s expand the example to build an overloaded package. The first step requires building a base_t object type and a table collection of the object type, like:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( oid  NUMBER )
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE base_list IS TABLE OF base_t;
/

Next, you create a book_t subtype of the base_t object type and a book_list table collection of the book_t subtype, like:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE
  TYPE book_t UNDER base_t
  ( title  VARCHAR2(30)
  , COST   NUMBER);
/
 
CREATE OR REPLACE
  TYPE book_list IS TABLE OF book_t;
/

We can test the base_t and book_t default constructors with the following SQL*Plus formatting and SQL query:

COL oid   FORMAT 999
COL title FORMAT A20
COL COST  FORMAT 99.99
SELECT *
FROM   TABLE(book_list(book_t(1,'Neuromancer',15.30)
                      ,book_t(2,'Count Zero',7.99)
                      ,book_t(3,'Mona Lisa Overdrive',7.99)
                      ,book_t(4,'Burning Chrome',8.89)));

It prints the following output:

 OID TITLE                  COST
---- -------------------- ------
   1 Neuromancer           15.30
   2 Count Zero             7.99
   3 Mona Lisa Overdrive    7.99
   4 Burning Chrome         8.89

The following is an overloaded package specification:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE
  PACKAGE packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list;
 
  /* A UDT list of base objects. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list;
 
  /* A UDT list of subtype objects. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list;
 
END;
/

After you create the package specification, you need to provide the implementation. This is typical in any programming language that supports Interface Description Language (IDL). A package body provides the implementation for the package specification. The package body follows:

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE OR REPLACE
  PACKAGE BODY packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
    /* Declare a new list. */
    lv_new  LIST  := list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list IS
 
    /* Declare a new list. */
    lv_new  BASE_LIST  := base_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list IS
 
    /* Declare a new list. */
    lv_new  BOOK_LIST  := book_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
END packer;
/

The test case for the base_list object type is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DECLARE
  lv_test  BASE_LIST :=
    base_list(base_t(1),base_t(2)
             ,base_t(3),base_t(4));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line('['||lv_test(i).oid||']');
  END LOOP;
END;
/

It prints the following output:

[1]
[3]
[4]

The test case for the book_list object type is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE
  lv_test  BOOK_LIST := 
    book_list(book_t(1,'Neuromancer',15.30)
             ,book_t(2,'Count Zero',7.99)
             ,book_t(3,'Mona Lisa Overdrive',7.99)
             ,book_t(4,'Burning Chrome',8.89));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line( '['||lv_test(i).oid||']'
                        ||'['||lv_test(i).title||']'
                        ||'['||lv_test(i).COST||']');
  END LOOP;
END;
/

It prints the following output:

[1][Neuromancer][15.3]
[3][Mona Lisa Overdrive][7.99]
[4][Burning Chrome][8.89]

In conclusion, you would not have to write overloaded methods for every list if PL/SQL supported class hierarchy and parallel array class hierarchy like Java. Unfortunately, it doesn’t and likely won’t in the future. You can pack table collections as a safety measure when they’re passed as parameters to other functions, procedures, or methods with the code above.

As always, I hope this helps those looking for a solution.

The purpose of this example shows you how to navigate a list with a sparsely populated index. This can occur when one element has been removed after the list was initialized. Unlike Oracle’s VARRAY (array), removing an element from a TABLE or list does not re-index the elements of the list.

This example also shows you how to coupled lists. The outer loop increments, notwithstanding the gap in index values, while the inner loop decrements. The upper range of the inner loop is set by the index value of the outer loop.

The example program uses an abbreviated version of the Twelve Days of Christmas, and I’ve tried to put teaching notes throughout the example file.

DECLARE
  /* Create a single column collection that is a list strings
     less than 8 characters in length and another of strings
     less than 20 characters in length. */
  TYPE DAY   IS TABLE OF VARCHAR2(8);
  TYPE verse IS TABLE OF VARCHAR2(20);
 
  /* Create variables that use the user-defined types:
  || =================================================
  ||  1. We give the variable a name of lv_day and lv_verse.
  ||  2. We assign a user-defined ADT (Attribute Data Type) collection.
  ||  3. We assign a list of value to the constructor of the list, which
  ||     allocates memory for each item in the comma-delimited list of
  ||     string.
  */
  lv_day   DAY   := DAY('first','second','third','fourth','fifth');
  lv_verse VERSE := verse('Partridge','Turtle Doves','French Hen'
                         ,'Calling Birds','Gold Rings');
 
BEGIN
  /*
  ||  Remove an element from each of the two lists, which makes the two
  ||  lists sparsely indexed. A sparsely indexed list has gaps in the
  ||  sequential index of the list.
  */
 
  lv_day.DELETE(3);
 
  /*
  ||   Loop through the list of days:
  ||  ===================================================j
  ||   1. A list created by a comma-delimited list is densely populated,
  ||      which means it has no gaps in the sequence of indexes.
  ||   2. A list created by any means that is subsequently accessed
  ||      and has one or more items removed is sparsely populated,
  ||      which means it may have gaps in the sequence of indexes.
  ||   3. A FOR loop anticipates densely populated indexes and fails
  ||      when trying to read a missing index, which is why you should
  ||      use an IF statement to check for the element of a list before
  ||      accessing it.
  ||   4. A COUNT method returns the number of elements allocated memory
  ||      in a list of values and the LAST method returns the highest
  ||      index value. The index value is alway an integer for user-defined
  ||      ADT (Attribute Data Type) collections, but may be a string for
  ||      an associative array or a PL/SQL list indexed by a string.
  ||   5. Removing an element from a list does not change the other
  ||      index values but does if you create an array (or varray), which
  ||      means COUNT OR LAST may cause the same type of error for a list
  ||      with a missing element.
  */
 
  FOR i IN 1..lv_day.LAST LOOP
 
    /*
    ||  Verify the index is valid.
    || ====================================================
    ||  You check whether the element is present in the
    ||  list.
    */
 
    IF lv_day.EXISTS(i) THEN
 
      /* Print the beginning of the stanza. */
      dbms_output.put_line('On the ['||lv_day(i)||'] of Christmas ...');
 
      /* Print the song. */
      FOR j IN REVERSE 1..i LOOP
        /* Check if the day exists. */
        IF lv_verse.EXISTS(j) THEN
          /* All but first and last verses. */
          IF j > 1 THEN
            dbms_output.put_line('-   ['||lv_verse(j)||']');
          /* The last verse. */
          ELSIF i = j THEN
            dbms_output.put_line('- A ['||lv_verse(j)||']'||CHR(10));
          /* Last verse. */
          ELSE
            dbms_output.put_line('and a ['||lv_verse(j)||']'||CHR(10));
          END IF;
        END IF;
      END LOOP;
    ELSE
      CONTINUE;
    END IF;
  END LOOP;
END;
/

As always, I hope it helps you solve problems in the real world.

In my classes, we use a VMware Linux install with SQL Developer. One of my students called me in a panic after an upgrade of packages when SQL Developer failed to launch. The student was astute enough to try running it from the command line where it generates an error like:

 Oracle SQL Developer
 Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
 
/opt/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 954: [: : integer expression expected
The JDK (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.222.b10-0.fc30.x86_64/) is not a valid JDK.
The JDK was specified by a SetJavaHome directive in a .conf file or by a --setjavahome option.
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/student/.sqldeveloper/19.2.0/product.conf
 
Error:  Unable to get APP_JAVA_HOME input from stdin after 10 tries

The error is simple, the SQL Developer package update wipe clean the configuration of the SetJavaHome variable in the user’s ~/.sqldeveloper/19.2.0/product.conf file. The fix is three steps because its very likely that the Java packages were also updated. Here’s how to fix it:

1. Navigate to the directory where you’ve installed the Java Virtual Machine (JVM) and find the current version of the JVM installed:

   cd /usr/lib/jvm ls java*

   cd /usr/lib/jvm ls java*

   It will return a set of files, like:

   java java-1.8.0 java-1.8.0-openjdk java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64 java-openjdk jre jre-1.8.0 jre-1.8.0-openjdk jre-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64 jre-openjdk

   java java-1.8.0 java-1.8.0-openjdk java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64 java-openjdk jre jre-1.8.0 jre-1.8.0-openjdk jre-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64 jre-openjdk

2. Navigate to your user’s product configuration file with this command:

   cd ~/.sqldeveloper/19.2.0

   cd ~/.sqldeveloper/19.2.0

3. Add the following line to the product.conf file:

   # SetJavaHome /path/jdk SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64/

   # SetJavaHome /path/jdk SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64/

Now, you should be able to run it from the command line. The shortcut icon should also work if one was installed. Also, don’t forget to update your $JAVA_HOME variable in the master Bash resource file, or your local user’s .bashrc files.

As always, I hope this helps those looking for a quick solution.

Install, configure, and use an Oracle Docker Container

Installing a Docker instance isn’t quite straightforward nor is it terribly difficult. It can be quite overwhelming if you’re unfamiliar with the technology of virtualization and containerization. This essay shows you how to create, configure, and manage an Oracle Database 18c XE Docker instance on the macOS. There are some slight differences when you install it on Windows OS.

Installation

You need to download the Oracle Database 18c XE file for Linux. You will find it on Oracle’s OTN website at https://www.oracle.com/downloads/. Click the Database link under the Developer Downloads banner. You want to download the Oracle Database Express Edition (XE), Release 18.4.0.0.0 (18c) file.

The file is a Linux Red Hat Package Manager (rpm) file. The file is approximately 2.5 GB in size, which means you may want to take a break while downloading it. Whether you take a break or not, this step may take more time than you like.

While downloading the Oracle database, you want to work on the two other tasks concurrently. You need to download and install Docker and Homebrew software, which aren’t installed from Apple’s Application Store. Many macOS systems disallow by default software from outside the comfy boundaries and inspections of the Apps Store. You may need to change your system preferences to install Docker and Homebrew.

You can download Docker for the macOS from the following website:

https://docs.docker.com/docker-for-mac/install/

The Homebrew (the missing package manager for macOS) website will tell you to install it from the macOS Command Line Interface (CLI). Please note that you must already have the Xcode Command Line Tools installed before you install Homebrew. The following Homebrew installation will update your Command Line Tools to macOS Mojave Version 10.14.

Open a Terminal session from your finder and run this command:

bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

After you install Homebrew in the Terminal, type the following to go to your user’s home folder (or directory):

cd

In your home directory (/Users/username [~]), create the docker-images directory from the available GitHub docker containers with these two commands (separated by the semicolon):

cd ~/; git clone https://github.com/oracle/docker-images.git

Move the Oracle Database XE 18c rpm file from your Downloads folder to the target docker-images subfolder with the following command:

mv ~/Downloads/oracle-database-xe-18c-1.0-1.x86_64-2.rpm \
   ~/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0/.

Change your present working directory with the following cd command:

cd docker-images/OracleDatabase/SingleInstance/dockerfiles

Build the Docker image with the following command (from the directory where the buildDockerImage.sh shell script is found):

./buildDockerImage.sh -v 18.4.0 -x

The Docker image build takes quite some time. It may take more than 10 minutes on some macOS computers. After it completes, you should see that it was successfully built and tagged in the Terminal. You can confirm the image build with this command:

docker images

It should return something like this:

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     18.4.0-xe           926f4349b277        12 minutes ago      5.89GB
oraclelinux         7-slim              153f8d73287e        8 weeks ago         131MB

Before you start your Docker container, you need to open a Terminal session. You will be in your home directory, which should give you a prompt like:

machine_name:~ username$

If you issue a pwd command, you should see the following:

/Users/username

Create an oracle directory as subdirectory:

mkdir oracle

While you might wonder about the oracle directory at this point, it’s to help keep straight Docker containers on the macOS file system. For example, when you install Docker instances for MySQL and PostgreSQL, you can see the Docker file systems as:

/Users/username/mysql
/Users/username/oracle
/Users/username/postgres

Now, you start the Docker container with the following command:

sudo \
docker run --name videodb -d -p 51521:1521 -p 55500:5500 -e ORACLE_PWD=cangetin \
           -e ORACLE_CHARACTERSET=AL32UTF8 -v ~/oracle:/home oracle/database:18.4.0-xe

After starting the Docker container, you check the container’s status the following command:

docker ps

Congratulations, you have successfully installed the Docker container.

Configure

The standard docker container prepares a base platform for you. It doesn’t create a schema or containerized user. It simply installs the Oracle Database Management System (DBMS) and Oracle Listener. You need to configure your Linux environment and your database.

You connect to the container as the root user, like:

docker exec -it videodb bash

You should update any of the older packages with the following command:

yum update

Next, you should install the openssh-server and vim packages. They’re not installed as part of the docker container’s default configuration. You’ll need them when you create non-root users and edit configuration files. This command installs them both:

yum openssh-server vim

There are a number of things for you to do at this point. They don’t all have to be done in the order that this essay takes. Like any other installation of Oracle on Linux, there is an oracle user who owns the installation. The oracle user is a non-login user. A non-login user doesn’t have a password and disallows a ssh connection. You need to first become the root user before you can use the su (substitute user) command to become the oracle user. Only superuser accounts hold the privileges to su without credentials because they’re trusted users.

The easiest thing to do while you’re the root user is test your ability to connect to the Oracle database’s system schema. You set the system schema’s password to cangetin when you ran the docker run command. At the command prompt, type the following to connect to the database:

sqlplus system/cangetin@xe

You should see the following when you connect as the system user:

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Sep 13 02:48:44 2020
Version 18.4.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Sat Sep 12 2020 21:13:33 +00:00
 
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
 
SQL>

Please note that only the oracle user can connect without referencing the @xe service identifier, which is defined in the $ORACLE_HOME/network/admin/tnsnames.ora file. You can read more about the SQL*Net configuration in the documentation. The quit command exits the SQL*Plus Command Line Interface. At this point, as root, lets you create a missing vi symbolic link to the vim utility you installed earlier.

ln -s /usr/bin/vim /usr/bin/vi

With vi configured, let’s su to the oracle user and create an .bashrc file for it. You should note that a non-login user doesn’t have a .bashrc file by default. You become the oracle user with the following command:

su oracle

You may notice that you’re not in the oracle user’s home directory. Let’s change that by moving to the correct home directory.

The home directory for any user is configured in the /etc/passwd file and available by using the echo command to see the $HOME environment variable’s value. This is true for Red Hat, Oracle Unbreakable Linux, CentOS, and Fedora distributions. They create users’ home directories as subdirectories in the /home directory.

The .bashrc file is a hidden file. Hidden files have no file name before the period and are not visible with an ls (list) command. You can find them by using a -al flag value with the ls command

ls -al

You can use the vi editor to create a .bashrc file like this:

vi .bashrc

A minimal oracle .bashrc (bash resource) file should look like this:

# 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
 
# Set Prompt
export PS1="[\u@localhost \W]\$ "
 
# Change to home directory.
cd $HOME
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions

If you know about the Linux CLI prompt, the localhost string may seem odd. It’s there to suppress the random string otherwise provided by the docker container.

A number of other Oracle environment parameters have already been set. You can see them with this command:

env | grep -i oracle

You can connect as the privileged sysdba role, once known as the internal user, to start and stop the database instance without stopping the docker container. That command syntax is:

sqlplus / as sysdba

Only the oracle user has privileges to connect with the sysdba role by default. That’s because the oracle user is the owner of the Oracle database installation.

While connected as the oracle user, you should make three changes. One change to oracle executable file permissions and two changes to the glogin.sql default configuration file.

The initial permissions on the $ORACLE_HOME/bin/oracle executable file in the docker container are shown below.

-rwxr-x--x 1 oracle oinstall 437755981 Oct 18  2018 oracle

The setuid bit is disabled when the user’s permissions are rwx. The oracle executable should always run with the permissions and ownership of the oracle user. That only happens when the setuid bit is enabled. You set the setuid. bit with the following syntax as the oracle user or privileged root superuser (from the $ORACLE_HOME/bin directory):

chmod u+s oracle

Relisting the file in a long list view (ls -al) after the change, you should see the following:

-rwsr-x--x 1 oracle oinstall 437755981 Oct 18  2018 oracle

The setuid bit is enabled when the user permissions are rws. Connections to the database by non-privileged Oracle users may raise ORA-01017 and ORA-12547 errors when the setuid bit is unset.

The glogin.sql file is read and processed with every connection to the database. Therefore, you should put little in there, and some would argue nothing in there. You’re going to enter the command that lets you interactively launch vi from a SQL> command prompt and set a SQL*Plus environment variable. The SQL*Plus environment variable lets you see debug messages raised by your PL/SQL programs.

To edit the glogin.sql file, change your terminal directory as follows:

cd $ORACLE_HOME/sqlplus/admin

Add the following two lines at the bottom of the glogin.sql file:

define _editor=vi
SET SERVEROUTPUT ON SIZE UNLIMITED

That’s it for configuring the oracle user’s account. Type exit to return to the root user shell. Type exit again, this time to leave the root user’s account and return to your hosting macOS.

The next configuration step sets up a non-privileged student account in Linux. You setup the student user with the following Docker command (for reference, it can’t be done from within the docker container):

sudo \
docker exec -it videodb bash -c "useradd -u 501 -g dba -G users \
            -d /home/student -s /bin/bash -c "Student" -n student"

You will be prompted for a password when this command runs. Try to keep the password simple. Using a password like cangetin is recommended when it’s a development instance. You can connect with the following docker command:

docker exec -it --user student videodb bash

After logging in to the docker container as the student user, you need to configure the .bashrc file. You should use the following minimal .bashrc file in the /home/student directory, which you can create with the vi editor.

# 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
 
# Set Prompt
export PS1="[\u@localhost \W]\$ "
 
# Change to home directory.
cd $HOME
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions
 
# Set Oracle environment variables.
export ORACLE_SID=XE
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE

As the c##student user, you need to connect to the system schema and provision a c##student container database. You can connect to the system schema with the following syntax:

sqlplus system/cangetin@xe

There are four steps required to provision a container database. These steps are different than the steps for previous non-container databases. In non-container databases, you could grant privileges directly to database users. Oracle now requires that you create database roles, which bundle privileges together. Then, you grant roles to users. The four provisioning steps are:

1. Create a user, which must adhere to the following naming convention from Oracle Database 12c forward. The database user’s name must start with the letter c and two # (pound) symbols followed by a character and then a string of letters and numbers.
2. Create a role, which must use the same naming convention as containerized users. Failure to use the correct naming convention raises an ORA-65096 error.
3. Grant database privileges to a role.
4. Grant a database role to a user.

You create a c##student container database user with the following syntax:

CREATE USER c##student
IDENTIFIED BY student
DEFAULT TABLESPACE users
QUOTA 100M ON users
TEMPORARY TABLESPACE temp;

Next, you create a c##studentrole container role with the following syntax:

CREATE ROLE c##studentrole CONTAINER = ALL;

Then, you grant the following privileges to your newly created c##studentrole role:

GRANT CREATE CLUSTER, CREATE INDEXTYPE,
CREATE PROCEDURE, CREATE SEQUENCE,
CREATE SESSION, CREATE TABLE,
CREATE TRIGGER, CREATE TYPE,
CREATE VIEW TO c##studentrole;

Finally, you grant a c##studentrole role (bundle of privileges) to a c##videodb user:

GRANT c##studentrole TO c##student;

After completing these tasks, you should use the quit command to exit the SQL*Plus CLI. Please note that unlike some database CLIs, you do not need to add a semicolon to quit the connection. Oracle divides its CLI commands into SQL*Plus and SQL commands; and the quit command is a SQL*Plus command. SQL*Plus commands do not require the terminating semicolon. SQL commands do require the semicolon or a line return and forward slash, which dispatches the SQL command to the SQL engine.

You should confirm that the provisioning worked by reconnecting to the Oracle database as the c##student user with this syntax:

sqlplus c##student/student@xe

You have now provisioned and verified the connection to a database container user. Use the quit command to disconnect from the Oracle database, and the exit command to return to your host operating system.

At this point you have a couple options for using the docker container. You can install a proprietary Integrated Development Environment (IDE), like Oracle’s free SQL Developer. There are several others and some support more than one database engine. Unfortunately, all the others have annual licensing costs.

Post Install: Access Tools

Oracle’s SQL Developer is a Java-based solution that runs on numerous platforms. You can download SQL Developer from Oracle’s OTN web site:

https://www.oracle.com/downloads/

Click on the Developer Tools link to find the current version of the SQL Developer. Before you install SQL Developer on your macOS, you will need to download and install the Java 8 Software Development Kit (SDK) from this web site:

http://www.oracle.com/technetwork/java/javase/downloads/

You configure a SQL Developer connection with the following values: use localhost as the host, c##student as the user, xe as the network service identifier, and 51521 as the port address. Click the plus button to add a connection where you enter these values, like shown below:

While the Java code in SQL Developer supports a port connection, Docker maps the port to the Oracle docker container. You don’t need to resolve the connection between SQL Developer and the Oracle Database listener through the network layer because this solution uses an Internal Process Control (IPC) model, based on socket to socket communication.

With virtualization you would need to install the Oracle Instant Client software on the hosting operating system. Then, you would configure your /etc/hosts file on both the hosting (macOS) and hosted (Oracle Linux) operating systems. Alternatively, you could add both IP addresses to a DNS server. The IP addresses let you map the connection between your physical macOS system and the Docker container running Oracle Linux. You can find further qualification of the connection mechanisms and repackaging in the Oracle Docker User Guide.

Containers map a local directory to operating system inside the container. Based on the earlier instructions the ~/oracle directory maps to the /home directory in the docker container. You have the ability to edit and move files within this portion of the file system’s hierarchy, which means you have complete control of the portion of the file system owned by the student user.

The next few steps help you verify the dual access to this portion of the docker container. Open a Terminal session and check your present working directory (with the pwd utility).

macName:~ username$ pwd

It should return:

/Users/username

During the installation, you created two subdirectories in the /Users/username directory. They were the oracle and docker-images subdirectories. In your host macOS, you should list (with the ls utility) the contents of your oracle subdirectory:

ls ~/oracle

It should return the following:

oracle	student

As mentioned, your macOS /Users/username/oracle directory holds the contents of your docker container’s /home directory. That means that your /Users/username/oracle/student directory mirrors the /home/student directory in your docker container.

Assume your GitHub code repository for Oracle development is in a directory on your macOS side. The local mapping to the ~/oracle/student directly lets you put symbolic links in the hosted student user’s subdirectories. These symbolic links would point to the editable code on the macOS file system, which can be concurrently linked to your GitHub code repository.

One of the key problems with Oracle’s deployment is that you can not use the up-arrow key to navigate the sqlplus command-line history. Here’s little Bash shell function that you can put in your .bashrc file. It requires you to have your system administrator install the rlwrap package, which wraps the sqlplus command-line history.

You should also set the $ORACLE_HOME environment variable before you put this function in your .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
}

If you port this shell script to an environment where rlwrap is not installed, it simply prints the error message and advises you to install the rlwrap package.

As always, I hope this helps those looking for a solution.

It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.

Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

DECLARE
 /* Control variable. */
 container  BOOLEAN := FALSE;
 
 /* Weakly structured system reference cursor. */
 container_sql  SYS_REFCURSOR;
 
 /* Constant required for pre-container databases to avoid a
    a compile time error. */
 sql_statement  CONSTANT VARCHAR2(50) :=
                  'SELECT cdb FROM v$database WHERE cdb = ''YES''';
BEGIN
 /* Check if the current user is the superuser. */
 FOR i IN (SELECT USER FROM dual) LOOP
 
   /* Perform tasks as superuser. */
   IF i.USER = 'SYSTEM' THEN
     /* Check for a container-enabled column, which enables this to
        work in both pre-container Oracle databases, like 10g and 11g. */
     FOR j IN (SELECT DISTINCT column_name
               FROM   dba_tab_columns
               WHERE  column_name = 'CDB') LOOP
 
       /* Check for a container database, set control variable
          and exit when found. */
       OPEN container_sql FOR sql_statement;
       LOOP
         container := TRUE;
         EXIT WHEN container_sql%FOUND;
       END LOOP;
     END LOOP;
 
      /* Conditionally drop existing user and role. */   
      IF container THEN
        /* Conditionally drop a container user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'C##STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER c##student CASCADE';
        END LOOP;
 
        /* Conditionally rop the container c##studentrole role. */
        FOR j IN (SELECT ROLE
                  FROM   dba_roles
                  WHERE  ROLE = 'C##STUDENTROLE') LOOP
          EXECUTE IMMEDIATE 'DROP ROLE c##studentrole';
        END LOOP;
 
        /* Create a container user with 200 MB of space. */
        EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Create a container role. */
        EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL';
 
        /* Grant privileges to a container user. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO c##studentrole';
 
        /* Grant role to user. */
        EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student';
      ELSE
        /* Conditonally drop the non-container database user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER student CASCADE';
        END LOOP;
 
        /* Create the student database. */
        EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Grant necessary privileges to the student database. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO student';
       END IF;
    ELSE
      /* Print an message that the user lacks privilegs. */
      dbms_output.put_line('You must be the SYSTEM user to drop and create a user.');
    END IF;
  END LOOP;
END;
/

As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.

My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.

The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:

ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30

It would return something like:

192.168.147.198

After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy Secure Copy) utility. I recommend you click on the MSI (Microsoft Installer Package) file, and install it on your Windows machine. As a rule, you should accept the default location, which is C:\Program Files\PuTTy.

While you could alter your system-level %PATH% environment variable after you install the software, I recommend you only include it in the %PATH% within the scope of a Command (cmd) shell. Navigate to your Windows Start and enter cmd in the search field. It should launch the Command Prompt terminal, which is a terminal emulator.

In the terminal editor, type the following case sensitive command to add the PuTTy directory to your path (yes, it’s case sensitive):

SET PATH=%PATH%;%ProgramFiles%\PuTTy

Now, you can securely copy the files and directory structure from Linux to Windows with the following command (where you replace the traditional server name with the dynamically assigned IP address). You should also be in the target directory where you want the files and directories copied:

C:\Data\cit225>pscp -r student@192.168.147.198:/home/student/Data/cit225/mysql .

After entering the password for the student on the Linux VM, you should see the following copy over:

Raiders2.png              | 99 kB |  99.5 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings3.png       | 119 kB | 119.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter4.png          | 103 kB | 103.9 kB/s | ETA: 00:00:00 | 100%
Raiders1.png              | 92 kB |  92.4 kB/s | ETA: 00:00:00 | 100%
Raiders3.png              | 123 kB | 123.9 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings2.png       | 111 kB | 111.7 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings1.png       | 103 kB | 104.0 kB/s | ETA: 00:00:00 | 100%
HarryPotter2.png          | 118 kB | 118.7 kB/s | ETA: 00:00:00 | 100%
HarryPotter7.png          | 150 kB | 150.2 kB/s | ETA: 00:00:00 | 100%
HarryPotter3.png          | 106 kB | 106.1 kB/s | ETA: 00:00:00 | 100%
HarryPotter5.png          | 82 kB |  82.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter6.png          | 129 kB | 129.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter1.png          | 118 kB | 118.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter8.png          | 150 kB | 150.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter8.txt          | 8 kB |   8.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter3.txt          | 5 kB |   5.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter5.txt          | 7 kB |   7.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter1.txt          | 6 kB |   6.6 kB/s | ETA: 00:00:00 | 100%
HarryPotter2.txt          | 7 kB |   7.8 kB/s | ETA: 00:00:00 | 100%
Raiders3.txt              | 5 kB |   5.6 kB/s | ETA: 00:00:00 | 100%
HarryPotter4.txt          | 7 kB |   7.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter7.txt          | 5 kB |   5.4 kB/s | ETA: 00:00:00 | 100%
HarryPotter6.txt          | 7 kB |   7.4 kB/s | ETA: 00:00:00 | 100%
LOTRFellowship.txt        | 4 kB |   5.0 kB/s | ETA: 00:00:00 | 100%
apply_store_base.sql      | 1 kB |   1.6 kB/s | ETA: 00:00:00 | 100%
query_membership.sql      | 0 kB |   0.3 kB/s | ETA: 00:00:00 | 100%
apply_mysql_lab1.sql      | 1 kB |   1.9 kB/s | ETA: 00:00:00 | 100%
configure_mysql_web.sql   | 37 kB |  37.1 kB/s | ETA: 00:00:00 | 100%
seed_mysql_store_ri2.sql  | 58 kB |  58.5 kB/s | ETA: 00:00:00 | 100%
cleanup_mysql_store.sql   | 5 kB |   5.4 kB/s | ETA: 00:00:00 | 100%
create_mysql_store_ri2.sq | 21 kB |  21.1 kB/s | ETA: 00:00:00 | 100%

My students will need to repeat this step to transfer all of the sample PHP files that demonstrate web application patterns. They also need to inspect individual files to ensure any path referencing commands are manually converted to their new Windows equivalent.

They can move the physical files as the root superuser with the following pscp command provide you haven’t stored the files somewhere other than the default location:

C:\Data\temp>pscp -r root@192.168.147.198:/var/lib/mysql .

As always, I hope this helps those trying to sort things out.

It’s been a couple releases trying to fix the following error thrown by SQL Developer on Fedora 30 and shown as the following dialog:

When you click the Detail button it shows the following error stack:

java.lang.NoClassDefFoundError: javafx/embed/swing/JFXPanel
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanelImpl(JFXPanelFactory.java:58)
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanel(JFXPanelFactory.java:34)
	at oracle.dbtools.raptor.startpage.StartPageViewer.createGUIComponent(StartPageViewer.java:179)
	at oracle.dbtools.raptor.startpage.StartPageViewer.getEditorContent(StartPageViewer.java:136)
	at oracle.ide.editor.AsynchronousEditor$2.run(AsynchronousEditor.java:345)
	at oracle.ide.editor.AsynchronousEditor$5.run(AsynchronousEditor.java:555)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058)
Caused by: java.lang.ClassNotFoundException: javafx.embed.swing.JFXPanel cannot be found by oracle.sqldeveloper_19.2.0
	at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412)
	at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107)
	at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
	... 10 more

I thought applying the Open Java FX package might fix the problem. I installed the package like the following:

yum install -y openjfx

The installation log:

Last metadata expiration check: 4:03:29 ago on Tue 21 Apr 2020 06:42:26 PM MDT.
Dependencies resolved.
=============================================================================================
 Package            Architecture      Version                       Repository          Size
=============================================================================================
Installing:
 openjfx            x86_64            8.0.202-8.b07.fc30            updates            8.8 M
 
Transaction Summary
=============================================================================================
Install  1 Package
 
Total download size: 8.8 M
Installed size: 11 M
 
Downloading Packages:
openjfx-8.0.202-8.b07.fc30.x86_64.rpm                        2.5 MB/s | 8.8 MB     00:03    
---------------------------------------------------------------------------------------------
Total                                                        2.1 MB/s | 8.8 MB     00:04     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                     1/1 
  Installing       : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Running scriptlet: openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Verifying        : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
 
Installed:
  openjfx-8.0.202-8.b07.fc30.x86_64                                                          
 
Complete!

After installing the software, I determined the new JAR files. Then, I added them to my $CLASSPATH environment variable, like:

export CLASSPATH=/usr/share/java/mysql-connector-java.jar:/usr/lib/jvm/openjfx/rt/lib/ext/fxrt.jar:/usr/lib/jvm/openjfx/rt/lib/jfxswt.jar:.

While it appears to load faster with these JAR files, it still raises the same Dialog error. I simply have to continue to look for a complete fix.

I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

In Oracle, a CASE statement ignores the null of a missing ELSE clause between lines 4 and 5. Oracle assumes a null value is an empty string when concatenated to a string with the double-piped concatenation operator. Oracle’s implementation differs from the ANSI standard and is non-compliant.

It would display the following thanks to the SQL reporting features that don’t exist in other Command-Line Interface (CLI) implementations, like mysql, psql, sqlcmd, or cql:

Account    Name
Number     (Last, First MI) City         State      Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71445 Winn, Randi      San Jose     CA         001-(408) 111-1111
B293-71445 Winn, Brian      San Jose     CA         001-(408) 111-1111

However, it fails in Postgres without a notice, warning, or error. Postgres simply returns a null string for the missing ELSE clause and follows the rule that any string concatenated against a null is a null. That means it retunes a null value for the full_name column above. The Postgres behavior is the ANSI standard behavior. After years of working with Oracle it was interesting to have this pointed out while porting a query.

You can fix the statement in Postgres by adding an explicit ELSE clause on a new line 5 that appends an empty string, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
           ELSE ''
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

It would display:

 account_number |  full_name  |   city   | state_province |     telephone      
----------------+-------------+----------+----------------+--------------------
 B293-71445     | Winn, Randi | San Jose | CA             | 001-(408) 111-1111
 B293-71445     | Winn, Brian | San Jose | CA             | 001-(408) 111-1111
(2 rows)

As always, I hope this helps those looking to solve a problem.