MacLochlainns Weblog

The first time you launch SQL Developer, you may see a very small or tiny display on the screen. With some high resolution screens the text is unreadable. Unless you manually configure the sqldeveloper shortcut, you generally can’t use it.

On my virtualization on a 27″ screen it looks like:

As an Administrator user, you right click the SQLDeveloper icon and click the Compatibility tab, which should look like the following dialog. You need to check the Compatibility Mode, which by default is unchecked with Windows 8 displayed in the select list.

Check the Compatibility Mode box and the select list will no longer be gray scaled. Click on the select list box and choose Windows 7. After the change you should see the following:

After that change, you need to click on the Change high DPI settings gray scaled button, which will display the following dialog box.

Click the Override high DPI scaling behavior check box. It will change the gray highlighted Scaling Performed by select box to white. Then, you click the Scaling Performed by select box and choose the System option.

Click the OK button on the nested SQLDeveloper Properties dialog box. Then, click the Apply button on the SQLDeveloper Properties button and the OK button. You will see a workable SQL Developer interface when you launch the program through your modified shortcut.

One of the errors that defeats a lot of new users who install the Oracle Database on the Windows operating system is a two-step event. The first step occurs when you try to connect to the database and it raises the following error:

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 7 21:00:42 2022
Version 18.4.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
ERROR:
ORA-12541: TNS:no listener

The second step may occur after you get the “no listener” error when you try to start the Oracle listener and it fails to start. The Oracle listener control command is:

lsnrctl start

When it returns the following error:

LSNRCTL FOR 64-bit Windows: Version 18.0.0.0.0 - Production ON 07-JAN-2022 21:02:20
 
Copyright (c) 1991, 2018, Oracle.  ALL rights reserved.
 
Starting tnslsnr: please wait...
 
Unable TO OpenSCManager: err=5
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

The problem is generally in two configuration files. They are the listener.ora and tnsnames.ora files. This typically occurs when the developer fails to set the localhost in the Windows operating system hosts configuration file. The chain of events that causes these errors can be avoided when the user puts the following two lines:

127.0.0.1      localhost
::1            localhost

in the following hosts file:

C:\Windows\system32\drivers\etc\hosts

You can typically avoid these errors when you configure the hosts configuration file correctly before installing the Oracle Database. That’s because the Oracle database installation will use localhost keyword instead of the current, and typically DHCP assigned, IP address.

The loss of connectivity errors typically occur when the IP address changes after the installation. DHCP IP addresses often change as machines disconnect and reconnect to a network.

You can fix a DHCP IP installation of an Oracle database by editing the listener.ora and tnsnames.ora files. You replace the IP addresses with the localhost keyword.

The listener.ora and tnsnames.ora files look like the following for an Oracle Database 21c Express Edition (provided you installed them in a C:\app\username directory:

listener.ora

# listener.ora Network Configuration File: C:\app\username\product\21.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
 
DEFAULT_SERVICE_LISTENER = XE
 
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = CLRExtProc)
     (ORACLE_HOME = C:\app\username\product\21.0.0\dbhomeXE)
     (PROGRAM = extproc)
     (ENVS = "EXTPROC_DLLS=ONLY:C:\app\username\product\21.0.0\dbhomeXE\bin\oraclr21.dll")
   )
 )
 
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\mclaughlinm\product\21.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
 
XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
 
LISTENER_XE =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 
 
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
   (CONNECT_DATA =
     (SID = CLRExtProc)
     (PRESENTATION = RO)
   )
 )

As always, I hope this helps those looking for a solution to something that can take more time than it should to fix.

The first tutorial supplementing the MySQL Connector/NET Developer Guide showed you how to connect and run static INSERT statement. It was a barebones PowerShell script with the MySQL Connector. This post shows you how to run a PowerShell script that uses a dynamic form to gather the MySQL credentials and then run a static query. Below is the MySQL Credentials form.

You enter the correct user name, password, hostname (or IP address), port, and database, like this:

Here’s the complete code for this staticQuery.ps1 PowerShell script:

# Add libraries for form components.
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
 
# Define a user credential form.
$form = New-Object System.Windows.Forms.Form
$form.Text = 'User Credential Form'
$form.Size = New-Object System.Drawing.Size(300,240)
$form.StartPosition = 'CenterScreen'
 
# Define a button and assign it and its controls to a form.
$loginButton = New-Object System.Windows.Forms.Button
$loginButton.Location = New-Object System.Drawing.Point(60,160)
$loginButton.Size = New-Object System.Drawing.Size(75,23)
$loginButton.Text = 'Login'
$loginButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $loginButton
$form.Controls.Add($loginButton)
 
# Define a button and assign it and its controls to a form.
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(155,160)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = 'Cancel'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
 
# Define a label and assign it and its controls to a form.
$userLabel = New-Object System.Windows.Forms.Label
$userLabel.Location = New-Object System.Drawing.Point(30,15)
$userLabel.Size = New-Object System.Drawing.Size(100,20)
$userLabel.Text = 'Enter User Name:'
$form.Controls.Add($userLabel)
 
# Define a TextBox and assign it and its controls to a form.
$userTextBox = New-Object System.Windows.Forms.TextBox
$userTextBox.Location = New-Object System.Drawing.Point(140,15)
$userTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($userTextBox)
 
# Define a label and assign it and its controls to a form.
$pwdLabel = New-Object System.Windows.Forms.Label
$pwdLabel.Location = New-Object System.Drawing.Point(30,40)
$pwdLabel.Size = New-Object System.Drawing.Size(100,20)
$pwdLabel.Text = 'Enter Password:'
$form.Controls.Add($pwdLabel)
 
# Define a TextBox and assign it and its controls to a form.
$pwdTextBox = New-Object System.Windows.Forms.TextBox
$pwdTextBox.Location = New-Object System.Drawing.Point(140,40)
$pwdTextBox.Size = New-Object System.Drawing.Size(100,20)
$pwdTextBox.PasswordChar = "*"
$form.Controls.Add($pwdTextBox)
 
# Define a label and assign it and its controls to a form.
$hostLabel = New-Object System.Windows.Forms.Label
$hostLabel.Location = New-Object System.Drawing.Point(30,65)
$hostLabel.Size = New-Object System.Drawing.Size(100,20)
$hostLabel.Text = 'Enter Hostname:'
$form.Controls.Add($hostLabel)
 
# Define a TextBox and assign it and its controls to a form.
$hostTextBox = New-Object System.Windows.Forms.TextBox
$hostTextBox.Location = New-Object System.Drawing.Point(140,65)
$hostTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($hostTextBox)
 
# Define a label and assign it and its controls to a form.
$portLabel = New-Object System.Windows.Forms.Label
$portLabel.Location = New-Object System.Drawing.Point(30,90)
$portLabel.Size = New-Object System.Drawing.Size(100,20)
$portLabel.Text = 'Enter Port #:'
$form.Controls.Add($portLabel)
 
# Define a TextBox and assign it and its controls to a form.
$portTextBox = New-Object System.Windows.Forms.TextBox
$portTextBox.Location = New-Object System.Drawing.Point(140,90)
$portTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($portTextBox)
 
# Define a label and assign it and its controls to a form.
$dbLabel = New-Object System.Windows.Forms.Label
$dbLabel.Location = New-Object System.Drawing.Point(30,115)
$dbLabel.Size = New-Object System.Drawing.Size(100,20)
$dbLabel.Text = 'Enter DB Name:'
$form.Controls.Add($dbLabel)
 
# Define a TextBox and assign it and its controls to a form.
$dbTextBox = New-Object System.Windows.Forms.TextBox
$dbTextBox.Location = New-Object System.Drawing.Point(140,115)
$dbTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($dbTextBox)
 
$form.Topmost = $true
 
$form.Add_Shown({$userTextBox.Select()})
$result = $form.ShowDialog()
 
if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
 
  # Assign inputs to connection variables.
  $uid = $userTextBox.Text
  $pwd = $pwdTextBox.Text
  $server = $hostTextBox.Text
  $port= $portTextBox.Text
  $dbName = $dbTextBox.Text
 
  # Declare connection string.
  $credentials = 'server=' + $server +
                 ';port=' + $port +
		 ';uid=' + $uid + 
		 ';pwd=' + $pwd + 
		 ';database=' + $dbName
 
  # Connect to the libaray MySQL.Data.dll
  Add-Type -Path 'C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll'
 
  # Create a MySQL Database connection variable that qualifies:
  # [Driver]@ConnectionString
  # ============================================================
  #  You can assign the connection string before using it or
  #  while using it, which is what we do below by assigning
  #  literal values for the following names:
  #   - server=<ip_address> or 127.0.0.1 for localhost
  #   - port=<port #>
  #   - uid=<user_name>
  #   - pwd=<password>
  #   - database=<database_name>
  # ============================================================
  $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$credentials}
  $Connection.Open()
 
  # Define a MySQL Command Object for a non-query.
  $sqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
  $sqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
  $sqlDataSet = New-Object System.Data.DataSet
 
  # Assign the connection and command text to the MySQL command object.
  $sqlCommand.Connection = $Connection
  $sqlCommand.CommandText = 'SELECT CONCAT(first_name," ",last_name) AS full_name ' +
                            ',      avenger ' +
                            'FROM   avenger'
 
  # Assign the connection and command text to the query method of
  # the data adapter object.
  $sqlDataAdapter.SelectCommand=$sqlCommand
 
  # Assign the tuples of data to a data set and return the number of rows fetched.
  $rowsFetched=$sqlDataAdapter.Fill($sqlDataSet, "data")
 
  # Print to console the data returned from the query.
  foreach($row in $sqlDataSet.tables[0]) {
    write-host "Avenger:" $row.avenger "is" $row.full_name }
 
  # Close the MySQL connection.
  $Connection.Close()
}

I created an avenger table and populated it with six rows of data:

-- Create the avenger table.
CREATE TABLE db_connect
( db_connect_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, version        VARCHAR(10)
, user           VARCHAR(24)
, db_name        VARCHAR(10));
 
-- Seed the avenger table with data.
INSERT INTO avenger
( first_name, last_name, avenger )
VALUES
 ('Anthony', 'Stark', 'Iron Man')
,('Thor', 'Odinson', 'God of Thunder')
,('Steven', 'Rogers', 'Captain America')
,('Bruce', 'Banner', 'Hulk')
,('Clinton', 'Barton', 'Hawkeye')
,('Natasha', 'Romanoff', 'Black Widow');

You run the staticQuery.ps1 PowerShell script from the Windows command shell with the following syntax:

powershell .\staticQuery.ps1

After running the staticQuery.ps1 PowerShell script, it writes the following to the local console but with minimal effort you can redirect it to a file:

Avenger: Iron Man is Anthony Stark
Avenger: God of Thunder is Thor Odinson
Avenger: Captain America is Steven Rogers
Avenger: Hulk is Bruce Banner
Avenger: Hawkeye is Clinton Barton
Avenger: Black Widow is Natasha Romanoff

As always, I hope this helps those looking to use this technology. My guess is the principal uses will be DevOps and Data Engineers.

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.

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3^rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the “sakila database” under the Example Database section.
2. Put the files in the local directory such as C:\temp\sakila-db (used for the next two steps, but overwrite with your directory path).
3. Type

   source c:\temp\sakila-db\sakila-schema.sql

   source c:\temp\sakila-db\sakila-schema.sql

   and press enter.

4. Type

   source c:\temp\sakila-db\sakila-data.sql

   source c:\temp\sakila-db\sakila-data.sql

   and press enter.

These instructions let you create the sakila database without rerunning the MSI to add a product. Naturally, you can avoid these steps by using the GUI approach provided in the MySQL MSI file.

As always, I hope this helps those looking for how to solve problems.

I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.

First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL ' at line 2
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1305: PROCEDURE studentdb.test does not exist

The same file generates this series of successful messages when run as a script file from MySQL Workbench:

Preparing...
Importing test.sql...
Finished executing script
Statement
CREATE PROCEDURE test
pv_input1
One
Operation completed successfully

For those who are curious enough to review the test case, here it is:

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
SELECT 'CREATE PROCEDURE test' AS "Statement";
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:

-- Set a label for the log file.
SELECT 'CREATE PROCEDURE test' AS "Statement";
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.

So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.

It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.

It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.

A warning that caught my eye in MySQL 8 (8.0.21) was this one on Unicode with the utf8 character code:

Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

Currently, a character alias for utf8mb3 is an alias for the deprecated utf8mb3 (a 3-byte character set) until it is removed. When the utf8mb3 character set is removed in a subsequent release the utf8 alias will represent the utf8mb4 (a 4-byte character set), which includes BMP and supplemental character support.

It appears to me that its probably a good time to proactively provision disk space for an upgrade to utf8mb4 (a 4-byte character set). It also probably time to use the utf8mb4 character set rather than the utf8 character set alias. The MySQL Server team wrote a blog entry on the when to use which one. Naturally, all this will have a substantial impact on disk space allocated to the database.

MySQL will be used for our online sections because the VMware instance and Docker configurations where too large to effectively download this term.

MySQL 8.0.21 Installation Steps

After you download the MySQL 8 MSI file, you will perform the following 24 steps to install MySQL on Windows 10. If you want a full developer install you must install Microsoft Excel and Visual Studio first.

1. The first thing you need to do is grant privileges to allow the MySQL Installer application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

2. The next thing you need to do is grant privileges to allow the MySQL Installer Launcher application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

3. Now you start the install by choosing a setup type. As a rule, I recommend you install the Developer Default. It is the default selection and preselected for you. Click the Next button to verify that you can install what you’ve selected.

4. The next workflow step checks requirements and lists any unmet requirements. The workflow lists the requirements for MySQL for Excel 1.3.8 as unmet because Microsoft Excel is not installed. Click the Next button when there are no unmet requirements in the list. Click the Back button to remove MySQL for Excel 1.3.8 from the setup selection.

5. Returning to the setup type workflow, you should select the Custom radio button. Click the Next button to view the list of selected types.

6. Use the green arrow pointing to the left to remove MySQL for Excel 1.3.8 from the list. Click the Next button to continue the MySQL Installer to install the selected MySQL libraries.

7. This dialog will display for several minutes as each of the MySQL Installer modules is. Click the Next button to move forward in the MySQL Installer workflow.

8. This dialog qualifies that there are three products to configure. Click the Next button to begin configuration of these products.

9. This dialog lets you choose between a Standalone MySQL Server / Classic MySQL Replication and InnoDB Cluster. You should choose the Standalone MySQL Server for a developer installation on a laptop or desktop. Click the Next button to continue the MySQL Installer configuration.

10. This dialog lets you choose set the type and networking values. They should be preselected as they are in the screen image. Click the Next button to continue the MySQL Installer configuration.

11. This dialog lets you choose between SHA256-based and the older MD5 encryption. Click Use Strong Password Encryption for Authentication (RECOMMENDED) radio button. Click the Next button to continue the MySQL Installer configuration.

12. This dialog lets you enter the MySQL Root Password. Click the Next button to continue the MySQL Installer configuration.

13. This dialog lets you enter a Windows Service Name and install a Standard System Account or Custom User account. Click the Next button to continue the MySQL Installer configuration.

14. This dialog lets you apply the configuration of the MySQL Server or Custom User product. Click the Next button to continue the MySQL Server configuration.

15. This dialog lets you watch the progress of the MySQL Server or Custom User configuration. Click the Finish button to complete the MySQL Server configuration.

16. This dialog lets you choose the next MySQL Router product for configuration. Click the Next button to begin the MySQL Router configuration.

17. This dialog allows you to configure the MySQL Router Configuration product. Leave the Hostname and Password fields blank when you do not want to configure the MySQL Router Configuration product. Click the Finish button to complete the MySQL Router workflow.

18. This dialog lets you choose the next Samples and Examples product for configuration. Click the Next button to begin the Samples and Examples configuration.

19. This dialog lets you create a Windows 10 MySQL Service. You enter the root password that you entered in Step #12. After you enter root password, click the Check button to verify the root password. The Check button enables the Next button when the root account’s password is validated. Click the now enabled Next button to create the MySQL Service.

20. This dialog lets you create a Windows 10 MySQL Service. Click the now enabled Next button to create the MySQL Service.

21. This dialog applies all the configurations selected. Click the Execute button to proceed with the configuration.

23. This dialog lets you watch the progress of the configuration. Click the Finish button to complete the product configurations.

23. This dialog the product configurations. You should note that the MySQL Router was not configured or needed. Click the Next button to complete the installation.

24. This dialog completes the workflow and launches MySQL Workbench and Shell. Click the Finish button to complete the installation and configuration processes.

After you install MySQL, the following shows you how to provision a studentdb database. It also shows you how to enable the global file privilege and how to read data from an external comma-separated values (CSV) file.

While working with a programming example for my students, I ran into an interesting run-time error when I changed their approach to importing Python’s random module. Here’s the raised error message:

Traceback (most recent call last):
  File "windowBouncingBalls.py", line 84, in <module>
    speed = [choice([-2,2]), choice([-2,2])]
NameError: name 'choice' is not defined

You raise the missing choice identifier when two things occur. The first thing requires you to use a standard import statement, like the following example, and the second thing requires you to continue to reference the identifier as “choice“.

import random

You can avoid the error by making the import of random like this:

from random import *

Or, you can leave the ordinary import statement and fully qualify the choice identifier with the random module name, like this:

    speed = [random.choice([-2,2]), random.choice([-2,2])]

As always, I hope this helps those who encounter a similar problem.

I actively maintain virtual test instances for my writing and teaching on Windows 7, Red Hat Enterprise Linux, Fedora, and Solaris. Perl on Windows is always interesting. I use ActivePerl on Windows 7 rather than Strawberry Perl, and it was interesting to see this note after I upgraded to the most current Community Edition of ActivePerl (5.16.3).

I thought it might be kind to post ActiveState’s Release Note because you should really read it before you try to install ActivePerl on Windows 7/8. Just make sure you’ve removed any earlier version of ActivePerl before trying the install. That’s what I did, and surprise, there weren’t any problems.

While the installation doesn’t tell you that you need to restart the Windows 7 operating system, you do. If you don’t restart Windows 7 after the ActivePerl install, you’ll get the following error message:

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC

You can grab the DBD::MySQL bundle with this command:

perl -MCPAN -e install Bundle::DBD::mysql

It downloads the MinGW (Minimalist GNU for Windows) C/C++ compiler, but it’ll be nested and not conflict with one if you have installed it independently. Absence of the compiler means you can’t attempt to build the library.

Proceeding further, I discovered a number of incompatibilities. The basic commands require you to load CPAN (Comprehensive Perl Archive Network) shell:

perl -MCPAN -e shell

Inside the Perl CPAN shell:

cpan> force install DBD:mysql --mysql_config C:\ProgramData\MySQL\MySQL Server 5.6

You also need to create a MySQL anonymous user in the database to support the default user during compilation, and if you dropped the test database you need to recreate it. I also ended up creating an ODBC user. Here are the commands to run as the root superuser:

CREATE USER 'ODBC'@'localhost';
CREATE USER ''@'localhost' IDENTIFIED BY 's3krit';
GRANT ALL ON test.* TO ''@'localhost';

I also took the time to update the MySQL test.pl to Perl 5.16; unfortunately, at the end of the day I didn’t get it working. Maybe I’ll try back in a few weeks. You need to change line 57 from port 5050 to port 3306, and “SharedSecret” to “s3krit“, as shown:

Here’s are the edits required test.pl program on lines 189 and 194:

The only release note provided is below, which I found inadequate. If you find the trick before me, please post it.

Windows

• The fork() emulation has known limitations. See perlfork for a detailed summary. In particular, fork() emulation will not work correctly with extensions that are either not thread-safe, or maintain internal state that cannot be cloned in the pseudo-child process. This caveat currently applies to extensions such as Tk and Storable.
• It seems that some people are having problems with the ActivePerl MSI installer. The first thing to note is that you CANNOT install ActivePerl 5.16 over an older version of ActivePerl based on 5.14, 5.12, 5.10, 5.8, 5.6 or 5.005, such as build 1204 or any other 1200, 1000, 800, 600 or 500 series build. We have determined that some of these problems are due to other installations of Perl that may be pointed at by something in the environment.

  The sure-fire solution is to make absolutely certain that no other installations of Perl are on the target machine. Realizing that this is not always possible, you can follow these steps to ensure the other installations will not interfere.

  • Stop the “Windows Installer” service. This can be accomplished from the command prompt using the following command:

    C:\> net stop "Windows Installer"

    C:\> net stop "Windows Installer"

  • Temporarily remove or rename PERLLIB and PERL5LIB environment variables in the system environment.
  • Temporarily remove or rename the following registry values:

    [\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)

    [\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)

  • Proceed with the installation.

  Once the installation has completed successfully, the above actions may be undone although restoring the environment variables or the registry values may interfere with the proper operation of your new ActivePerl installation. In order to perform all of the above steps, you will need to have Administrative privileges on the target machine. If you do not have the required privileges you should contact your Administrator.