MacLochlainns Weblog

A unit test script may contain SQL or PL/SQL statements or it may call another script file that contains SQL or PL/SQL statements. Moreover, a script file is a way to bundle several activities into a single file because most unit test programs typically run two or more instructions as unit tests.

Unconditional Script File

You can write a simple unit test like the example program provided in the Lab 1 Help Section, which includes conditional logic. However, you can write a simpler script that is unconditional and raises exceptions when preconditions do not exist.

The following script file creates a one table and one_s sequence. The DROP TABLE and DROP SEQUENCE statements have the same precondition, which is that the table or sequence must previously exist.

-- Drop table one.
DROP TABLE one;
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Drop sequence one_s.
DROP SEQUENCE one_s;
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

After writing the script file, you can save it in the lab2 subdirectory as the unconditional.sql file. After you login to the SQL*Plus environment from the lab2 subdirectory. You call the unconditional.sql script file from inside the SQL*Plus environment with the following syntax:

@unconditional.sql

It will display the following output, which raises an exception when the one table or one_s sequence does not already exist in the schema or database:

DROP TABLE one
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
Table created.
 
DROP SEQUENCE one_s
              *
ERROR at line 1:
ORA-02289: sequence does not exist
 
Sequence created.

An unconditional script raises exceptions when a precondition of the statement does not exist. The precondition is not limited to objects, like the table or sequence; and the precondition may be specific data in one or several rows of one or several tables. You can avoid raising conditional errors by writing conditional scripts.

Conditional Script File

A conditional script file contains statements that check for a precondition before running a statement, which effectively promotes their embedded statements to a lambda function. The following logic recreates the logic of the unconditional.sql script file as a conditional script file:

-- Conditionally drop a table and sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ONE','ONE_S')
            ORDER BY object_type ) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

You can save this script in the lab2 subdirectory as conditional.sql and then unit test it in SQL*Plus. You must manually drop the one table and one_s sequence before running the conditional.sql script to test the preconditions.

You will see that the conditional.sql script does not raise an exception because the one table or one_s sequence is missing. It should generate output to the console, like this:

PL/SQL procedure successfully completed.
 
Table created.
 
Sequence created.

As a rule, you should always write conditional script files. Unconditional script files throw meaningless errors, which may cause your good code to fail a deployment test that requires error free code.

Selective Aggregation

Learning Outcomes

• Learn how to combine CASE operators and aggregation functions.
• Learn how to selective aggregate values.
• Learn how to use SQL to format report output.

Selective aggregation is the combination of the CASE operator and aggregation functions. Any aggregation function adds, sums, or averages the numbers that it finds; and when you embed the results of a CASE operator inside an aggregation function you get a selective result. The selectivity is determined by the WHEN clause of a CASE operator, which is more or less like an IF statement in an imperative programming language.

The prototype for selective aggregation is illustrated with a SUM function below:

SELECT   SUM(CASE
               WHEN left_operand = right_operand THEN result
               WHEN left_operand > right_operand THEN result
               WHEN left_operand IN (SET OF comma-delimited VALUES) THEN result
               WHEN left_operand IN (query OF results) THEN result
               ELSE alt_result
             END) AS selective_aggregate
FROM     some_table;

A small example let’s you see how selective aggregation works. You create a PAYMENT table and PAYMENT_S sequence for this example, as follows:

-- Create a PAYMENT table.
CREATE TABLE payment
( payment_id     NUMBER
, payment_date   DATE	      CONSTRAINT nn_payment_1 NOT NULL
, payment_amount NUMBER(20,2) CONSTRAINT nn_payment_2 NOT NULL
, CONSTRAINT pk_payment PRIMARY KEY (payment_id));
 
-- Create a PAYMENT_S sequence.
CREATE SEQUENCE payment_s;

After you create the table and sequence, you should insert some data. You can match the values below or choose your own values. You should just insert values for a bunch of rows.

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

DECLARE
  -- Create local collection data types.
  TYPE pmtval IS TABLE OF NUMBER(20,2);
  TYPE smonth IS TABLE OF VARCHAR2(3);
 
  -- Create variable to hold the list of payments.
  payments PMTVAL := pmtval();
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
 
  -- Declare variable values.
  month      VARCHAR2(3);
  year       NUMBER := '2019';
  pmt_date   DATE; 
  tpmt_date  VARCHAR2(11);
 
  -- Declare default number of random payments.
  payment_number  NUMBER := 10000;
BEGIN
  -- Populate payment list. 
  FOR i IN 1..payment_number LOOP
    payments.EXTEND;
    SELECT   ROUND(dbms_random.value() * 1000,0)
    ||       '.'
    ||       ROUND(dbms_random.value() * 100,0)
    INTO     payments(payments.COUNT)
    FROM     dual;
  END LOOP;
 
  -- Create and populate payment date and amount.
  FOR i IN 1..payment_number LOOP
    -- Assign random month value.
    month := short_month(dbms_random.value(1,short_month.COUNT));
 
    -- Assign random day of the month value and assemble random date.
    IF month IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') THEN
      pmt_date := ROUND(dbms_random.value(1,31),0) || '-' || month || '-' || year;
    ELSIF month IN ('APR','JUN','SEP','NOV') THEN
      pmt_date := ROUND(dbms_random.value(1,30),0) || '-' || month || '-' || year;
    ELSE
      pmt_date := ROUND(dbms_random.value(1,28),0) || '-' || month || '-' || year;
    END IF;
 
    -- Insert values into the PAYMENT table.
    INSERT INTO payment
    ( payment_id, payment_date, payment_amount )
    VALUES
    ( payment_s.NEXTVAL, pmt_date, payments(i));
  END LOOP;
 
  -- Commit the writes.
  COMMIT;
END;
/

After inserting 10,000 rows, you can get an unformatted total with the following query:

-- Query total amount.
SELECT   SUM(payment_amount) AS payment_total
FROM     payment;

It outputs the following:

PAYMENT_TOTAL
-------------
   5011091.75

You can nest the result inside the TO_CHAR function to format the output, like

-- Query total formatted amount.
SELECT   TO_CHAR(SUM(payment_amount),'999,999,999.00') AS payment_total
FROM     payment;

It outputs the following:

PAYMENT_TOTAL
---------------
   5,011,091.75

Somebody may suggest that you use a PIVOT function to rotate the data into a summary by month but the PIVOT function has limits. The pivoting key must be numeric and the column values will use only those numeric values.

-- Pivoted summaries by numeric monthly value.
SELECT   *
FROM    (SELECT EXTRACT(MONTH FROM payment_date) payment_month
         ,      payment_amount
         FROM   payment)
         PIVOT (SUM(payment_amount) FOR payment_month IN
                 (1,2,3,4,5,6,7,8,9,10,11,12));

It outputs the following:

	 1	    2	       3	  4	     5		6	   7	      8 	 9	   10	      11	 12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 245896.55  430552.36  443742.63  457860.27  470467.18	466370.71  415158.28  439898.72  458998.09  461378.56  474499.22  246269.18

You can use selective aggregation to get the results by a character label, like

SELECT   SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 1
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "JAN"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 2
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "FEB"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 3
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "MAR"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) IN (1,2,3)
             AND  EXTRACT(YEAR FROM payment_date) = 2019 THEN payment_amount
           END) AS "1FQ"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 4
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "APR"
FROM     payment;

It outputs the following:

       JAN	  FEB	     MAR	1FQ	   APR
---------- ---------- ---------- ---------- ----------
 245896.55  430552.36  443742.63 1120191.54  457860.27

You can format the output with a combination of the TO_CHAR and LPAD functions. The TO_CHAR allows you to add a formatting mask, complete with commas and two mandatory digits to the right of the decimal point. The reformatted query looks like

COL JAN FORMAT A13 HEADING "Jan"
COL FEB FORMAT A13 HEADING "Feb"
COL MAR FORMAT A13 HEADING "Mar"
COL 1FQ FORMAT A13 HEADING "1FQ"
COL APR FORMAT A13 HEADING "Apr"
SELECT   LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 1
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "JAN"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 2
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "FEB"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 3
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "MAR"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) IN (1,2,3)
             AND  EXTRACT(YEAR FROM payment_date) = 2019 THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "1FQ"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 4
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "APR"
FROM     payment;

It displays the formatted output:

Jan	      Feb	    Mar 	  1FQ		Apr
------------- ------------- ------------- ------------- -------------
   245,896.55	 430,552.36    443,742.63  1,120,191.54    457,860.27

INSERT Statement

Learning Outcomes

• Learn how to use positional- and named-notation in INSERT statements.
• Learn how to use the VALUES clause in INSERT statements.
• Learn how to use subqueries in INSERT statements.

The INSERT statement lets you enter data into tables and views in two ways: via an INSERT statement with a VALUES clause and via an INSERT statement with a query. The VALUES clause takes a list of literal values (strings, numbers, and dates represented as strings), expression values (return values from functions), or variable values.

Query values are results from SELECT statements that are subqueries (covered earlier in this appendix). INSERT statements work with scalar, single-row, and multiple-row subqueries. The list of columns in the VALUES clause or SELECT clause of a query (a SELECT list) must map to the positional list of columns that defines the table. That list is found in the data dictionary or catalog. Alternatively to the list of columns from the data catalog, you can provide a named list of those columns. The named list overrides the positional (or default) order from the data catalog and must provide at least all mandatory columns in the table definition. Mandatory columns are those that are not null constrained.

Oracle databases differ from other databases in how they implement the INSERT statement. Oracle doesn’t support multiple-row inserts with a VALUES clause. Oracle does support default and override signatures as qualified in the ANSI SQL standards. Oracle also provides a multiple- table INSERT statement. This section covers how you enter data with an INSERT statement that is based on a VALUES clause or a subquery result statement. It also covers multiple-table INSERT statements.

The INSERT statement has one significant limitation: its default signature. The default signature is the list of columns that defines the table in the data catalog. The list is defined by the position and data type of columns. The CREATE statement defines the initial default signature, and the ALTER statement can change the number, data types, or ordering of columns in the default signature.

The default prototype for an INSERT statement allows for an optional column list that overrides the default list of columns. When you provide the column list you choose to implement named-notation, which is the right way to do it. Relying on the insertion order of the columns is a bad idea. An INSERT statement without a list of column names is a position-notation statement. Position-notation is bad because somebody can alter that order and previously written INSERT statements will break or put data in the wrong columns.

Like methods in OOPLs, an INSERT statement without the optional column list constructs an instance (or row) of the table using the default constructor. The override constructor for a row is defined by any INSERT statement when you provide an optional column list. That’s because it overrides the default constructor.

The generic prototype for an INSERT statement is confusing when it tries to capture both the VALUES clause and the result set from a query. Therefore, I’ve opted to provide two generic prototypes.

Insert by value

The first uses the VALUES clause:

INSERT
INTO table_name
[( column1, column2, column3, ...)] VALUES
( value1, value2, value3, ...);

Notice that the prototype for an INSERT statement with the result set from a query doesn’t use the VALUES clause at all. A parsing error occurs when the VALUES clause and query both occur in an INSERT statement.

The second prototype uses a query and excludes the VALUES clause. The subquery may return one to many rows of data. The operative rule is that all columns in the query return the same number of rows of data, because query results should be rectangles—rectangles made up of one to many rows of columns.

Insert by subquery

Here’s the prototype for an INSERT statement that uses a subquery:

INSERT
INTO table_name
[( column1, column2, column3, ...)]
( SELECT value1, value2, value3, ... FROM table_name WHERE ...);

A query, or SELECT statement, returns a SELECT list. The SELECT list is the list of columns, and it’s evaluated by position and data type. The SELECT list must match the definition of the table or the override signature provided.

Default signatures present a risk of data corruption through insertion anomalies, which occur when you enter bad data in tables. Mistakes transposing or misplacing values can occur more frequently with a default signature, because the underlying table structure can change. As a best practice, always use named notation by providing the optional list of values; this should help you avoid putting the right data in the wrong place.

The following subsections provide examples that use the default and override syntax for INSERT statements in Oracle databases. The subsections also cover multiple-table INSERT statements and a RETURNING INTO clause, which is an extension of the ANSI SQL standard. Oracle uses the RETURNING INTO clause to manage large objects, to return autogenerated identity column values, and to support some of the features of Oracle’s dynamic SQL. Note that Oracle also supports a bulk INSERT statement, which requires knowledge of PL/SQL.

DESCRIBE table_name

Name                                 Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

Name                                      Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

SQL> INSERT INTO rental
  2  VALUES
  3  ( 1086
  4  , 1009
  5  , SYSDATE
  6  , TRUNC(SYSDATE + 5) 7 ,1
  8  , SYSDATE
  9  , 1
 10  , SYSDATE);

  5  , TO_DATE('15-APR-2011 12:53:01','DD-MON-YYYY HH24:MI:SS')

  5  , CAST('15-APR-2011 12:53:02' AS DATE)

  5  ,  CAST('15-APR-2011 12:53:02' AS DATE) FROM dual
        *
ERROR AT line 1:
ORA-01830: DATE format picture ends before converting entire input string

  5  ,  CAST(CAST('15-APR-2011 12:53:02' AS TIMESTAMP) AS DATE)

SQL> SELECT   rental_s1.NEXTVAL FROM dual;

  3  ( rental_s1.NEXTVAL

SQL> SELECT   contact_id
  2  FROM     contact
  3  WHERE    last_name = 'Potter'
  4  AND      first_name = 'Harry';

SQL> INSERT INTO rental
  2  ( rental_id
  3  , customer_id
  4  , check_out_date
  5  , return_date
  6  , created_by
  7  , creation_date
  8  , last_updated_by
  9  , last_update_date )
10 VALUES
 11  ( rental_s1.NEXTVAL
 12  ,(SELECT   contact_id
 13    FROM     contact
 14    WHERE    last_name = 'Potter'
 15    AND      first_name = 'Harry')
 16  , SYSDATE
 17  , TRUNC(SYSDATE + 5)
 18  , 1
 19  , SYSDATE
 20  , 3
 21  , SYSDATE);

,(SELECT   contact_id
  *
ERROR AT line 3:
ORA-01427: single-ROW subquery returns more than one ROW

Name                             Null?    Type
 ------------------------------- -------- -----------------------
ITEM_DESC                        NOT NULL CLOB
ITEM_ICON                        NOT NULL BLOB
ITEM_PHOTO                                BINARY FILE LOB

EMPTY_BLOB()
EMPTY_CLOB()

 10  , BFILENAME('VIRTUAL_DIRECTORY_NAME', 'file_name.png')

INSERT INTO some_table
[( column1, column2, column3, ...)]
VALUES
( value1, value2, value3, ...)
RETURNING column1 INTO local_variable;

Name                                 Null?    Type
------------------------------------ -------- --------------
PARENT_ID                            NOT NULL NUMBER
PARENT_NAME                                   VARCHAR2(10)

Name                                 Null?    Type
------------------------------------ -------- --------------
CHILD_ID                             NOT NULL NUMBER
PARENT_ID                                     NUMBER
PARENT_NAME                                   VARCHAR2(10)

SQL> INSERT INTO parent
  2  VALUES
  3  ( parent_s1.NEXTVAL 4 ,'One Parent');
 
SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL 4 , parent_s1.CURRVAL 5 ,'One Child');

SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL -- CHILD_ID
  4  , parent_s1.CURRVAL -- PARENT_ID
  5  ,'One Child') -- CHILD_NAME
  6  /

INSERT
INTO table_name
[( column1, column2, column3, ...)]
( SELECT value1, value2, value3, ... FROM table_name WHERE ...);

If you’re wondering about this post, it shows the basic array of a set of integers and strings before showing you how to create nested tables of data in PostgreSQL. By the way, they’re not called nested tables in PostgreSQL, like they are in Oracle but perform like their Oracle cousins.

Let’s create a table with an auto-incrementing column and two arrays, one array of integers and another of strings:

-- Conditionally drop the demo table.
DROP TABLE IF EXISTS demo;
 
-- Create the test table.
CREATE TABLE demo
( demo_id     serial
, demo_number integer[5]
, demo_string varchar(5)[7]);

You can insert test values like this:

INSERT INTO demo
(demo_number, demo_string)
VALUES
( array[1,2,3,4,5]
, array['One','Two','Three','Four','Five','Six','Seven']);

Then, you can query them with this unnest function, like:

SELECT  unnest(demo_number) AS numbers
,       unnest(demo_string) AS strings
FROM    demo;

It returns:

 numbers | strings
---------+---------
 1       | One
 2       | Two
 3       | Three
 4       | Four
 5       | Five
         | Six
         | Seven
(7 rows)

You may note that the two arrays are asymmetrical. It only becomes an issue when you navigate the result in a PL/pgSQL cursor or imperative programming language, like Python.

Now, let’s do something more interesting like work with a composite user-defined type, like the player structure. You would create the composite user-defined type with this syntax:

-- Conditionally drop the player type.
DROP TYPE IF EXISTS player;
 
-- Create the player type.
CREATE TYPE player AS
( player_no        integer
, player_name      varchar(24)
, player_position  varchar(14)
, ab               integer
, r                integer
, h                integer
, bb               integer
, rbi              integer );

You can create a world_series table that include a players column that uses an array of player type, like

-- Conditionally drop the world_series table.
DROP TABLE IF EXISTS world_series;
 
-- Create the player type.
CREATE TABLE world_series
( world_series_id  serial
, team             varchar(24)
, players          player[30]
, game_no          integer
, year             integer );

If you’re familiar with the Oracle Database, you’d have to specify a tested table in the syntax. Fortunately, PostgreSQL doesn’t require that.

Insert two rows with the following statement:

INSERT INTO world_series
( team
, players
, game_no
, year )
VALUES
('San Francisco Giants'
, array[(24,'Willie Mayes','Center Fielder',5,0,1,0,0)::player
       ,(5,'Tom Haller','Catcher',4,1,2,0,2)::player]
, 4
, 1962 );

You can append to the array with the following syntax. A former student and I have a disagreement on whether this is shown in the PostgreSQL 8.15.4 Modifying Array documentation. I believe it can be inferred from the document and he doesn’t believe so. Anyway, here’s how you add an element to an existing array in a table with the UPDATE statement:

UPDATE world_series
SET    players = (SELECT array_append(players,(7,'Henry Kuenn','Right Fielder',3,0,0,1,0)::player) FROM world_series)
WHERE  team = 'San Francisco Giants'
AND    year = 1962
AND    game_no = 4;

Like Oracle’s nested tables, PostgreSQL’s arrays of composite user-defined types requires writing a PL/pgSQL function. I’ll try to add one of those shortly in another blog entry to show you how to edit and replace entries in stored arrays of composite user-defined types.

You can query the unnested rows and get a return set like a Python tuple with the following query:

SELECT unnest(players) AS player_list
FROM   world_series
WHERE  team = 'San Francisco Giants'
AND    year = 1962
AND    game_no = 4;

It returns the three rows from the players array:

                 player_list
----------------------------------------------
 (24,"Willie Mayes","Center Field",5,0,1,0,0)
 (5,"Tom Haller",Catcher,4,1,2,0,2)
 (7,"Henry Kuenn","Right Fielde",3,0,0,1,0)
(3 rows)

It returns the data set in entry-order. If we step outside of the standard 8.15 Arrays PostgreSQL Documentation, you can do much more with arrays (or nested tables). The balance of this example demonstrates some new syntax that helps you achieve constructive outcomes in PostgreSQL.

You can use a Common Table Expression (CTE) to get the columnar display of the player composite user-defined type. This type of solution is beyond the standard , like:

WITH list AS
 (SELECT unnest(players) AS row_result
  FROM   world_series
  WHERE  team = 'San Francisco Giants'
  AND    year = 1962
  AND    game_no = 4)
SELECT  (row_result).player_name
,       (row_result).player_no
,       (row_result).player_position
FROM     list;

If you’re unfamiliar with accessing composite user-defined types, I wrote a post on that 7 years ago. You can find the older blog entry PostgreSQL Composites on my blog.

It returns only the three requested columns of the player composite user-defined type:

 player_name  | player_no | player_position
--------------+-----------+-----------------
 Willie Mayes |        24 | Center Fielder
 Tom Haller   |         5 | Catcher
 Henry Kuenn  |         7 | Right Fielder
(3 rows)

You should note that the data is presented in an entry-ordered manner when unnested alone in the SELECT-list. That behavior changes when the SELECT-list includes non-array data.

The easiest way to display data from the non-array and array columns is to list them inside the SELECT-list of the CTE, like:

WITH list AS
 (SELECT game_no AS game
  ,      year
  ,      unnest(players) AS row_result
  FROM   world_series
  WHERE  team = 'San Francisco Giants'
  AND    year = 1962
  AND    game_no = 4)
SELECT   game
,        year 
,       (row_result).player_name
,       (row_result).player_no
,       (row_result).player_position
FROM     list;

It returns an ordered set of unnested rows when you include non-array columns, like:

 game | year | player_name  | player_no | player_position
------+------+--------------+-----------+-----------------
    4 | 1962 | Henry Kuenn  |         7 | Right Fielder
    4 | 1962 | Tom Haller   |         5 | Catcher
    4 | 1962 | Willie Mayes |        24 | Center Fielder
(3 rows)

While you can join the world_series table to the unnested array rows (returned as a derived table, its a bad idea. The mechanics to do it require you to return the primary key column in the same SELECT-list of the CTE. Then, you join the CTE list to the world_series table by using the world_series_id primary key.

However, there is no advantage to an inner join approach and it imposes unnecessary processing on the database server. The odd rationale that I heard when I noticed somebody was using a CTE to base-table join was: “That’s necessary so they could use column aliases for the non-array columns.” That’s not true because you can use the aliases inside the CTE, as shown above when game is an alias to the game_no column.

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

After you create and provision the Oracle Database 21c Express Edition (XE), you can create a c##student container user with the following two step process.

1. Create a c##student Oracle user account with the following command:

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

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

2. Grant necessary privileges to the newly created c##student user:

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

   GRANT CREATE cluster, CREATE indextype, CREATE operator , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO c##student;

As always, I hope this helps those looking for how to do something that’s less than clear because everybody uses tools.

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.