MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle 18c’ Category

Comparative Techniques

without comments

This post is designed to illustrate PL/SQL code with techniques employed before Oracle Database 12c with manual sequence references rather than auto generated IDs. The trick is small but should be made with older code. It impacts creation statement, conditional drop statements, and modification to stored procedures that manage transactions across two or more tables.

The key to the difference is in the table creation. Prior to Oracle Database 12c, there were no automatic sequences that you could assign to table definitions. Tables were created without sequences, and sequences were created independently. Likewise, there was no “IF EXISTS” clause for a DROP statement, which meant conditional drops were required as anonymous blocks.

Old tables and sequences from the Oracle Database 11g would use the following definitions:

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

/* Conditionally drop grandma table and grandma_s sequence. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ','GRANDMA','GRANDMA_SEQ')
            ORDER BY 1 DESC, 2) LOOP
    IF i.object_type = 'TABLE' THEN
      /* Use the cascade constraints to drop the dependent constraint. */
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id)
);
 
/* Create the sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);
 
/* Create sequence. */
CREATE SEQUENCE tweetie_bird_seq;

The key to the differences in Oracle Database 12c are in the table creation, and then in the INSERT statement because you can exclude the surrogate key column. A surrogate key column is also known as an ID column or sequence identified column.

New tables and sequences from the Oracle Database 12c forward use the following definitions to manage sequences:

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

/* Conditionally drop grandma table and grandma_s sequence. */
DROP TABLE IF EXISTS tweetie_bird;
DROP TABLE IF EXISTS grandma;
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER        DEFAULT grandma_seq.NEXTVAL
, grandma_house  VARCHAR2(30)  CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk        PRIMARY KEY (grandma_id)
);
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        DEFAULT grandma_seq.CURRVAL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

This is the pattern that I setup to explain the new default surrogate primary and foreign keys when I wrote the Oracle Database 12c PL/SQL Programming book (pp. 20-22). Unfortunately, it was just a simple example and fits this use case:

  • You write one create_something procedure or function, also known interchangeably as a method, to insert initial records; and
  • You write one add_something method add a subsequent dependent record; and

This is a common practice by many but it creates unnecessary code that you need to maintain, which translates to accumulated technical debt throughout a design. The technical debt grows by a factor of two when you want to overload behaviors for security features or batch processing.

A better solution is to write one method that accommodates both an creates a new record and adds dependent records. The warner_brother procedure will do that later in this post. To prepare for the warner_brother procedure the dependent tweetie_bird table needs to change.

The default of the .currval of a “parent” table’s sequence value is no longer guaranteed in an independent action. The create and add methods now share the same method, and inspection of an existing independent record is necessary for both the create and add methods.

The new tweetie_bird table looks like:

/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn2 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

The next part of the post discusses how to write the create and add procedure in pre-12c and 12c forward implementations. Clearly, the automatic generated sequence values are the best approach when you use user-defined names for them as shown earlier.

The warner_brother procedure models the idea that any grandma may have one or more Tweetie birds, which means you must check if there’s a row in the grandma table. If there’s a row in the grandma table, you shouldn’t insert a new row in the grandma table. The internal get_grandma_id function returns:

  • A zero if there is no existing row in the grandma table. After which, the nested if-block will insert a new row into the grandma table; and then assign the grandma_seq.CURRVAL value to a local variable.
  • An existing grandma_id value, which can be used as a foreign key value in the subsequent insert statement to the tweety_bird table.

The warner_brother procedure for Oracle Database 11g is shown below:

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

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_id
    , grandma_house )
    VALUES
    ( grandma_seq.NEXTVAL
    , pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_id
  , tweetie_bird_house 
  , grandma_id )
  VALUES
  ( tweetie_bird_seq.NEXTVAL
  , pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

The warner_brother procedure for Oracle Database 12c forward simplifies somethings and complicates others. The DEFAULT for the grandma_id foreign key column in the tweetie_bird table must be overwritten with a copy of the correct primary key value. More or less, it requires the correct grandma_id column value by finding the existing row in the grandma table. The internal get_grandma_id function grabs that value while returning a value that precludes writing to the grandma table.

The warner_brother procedure for Oracle Database 12c forward shown below shows that the grandma_id column default value is overridden by a local lv_grandma_id value returned by the internal get_grandma_id function on lines 60 thru 65 below.

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

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_house )
    VALUES
    ( pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_house 
  , grandma_id )
  VALUES
  ( pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

You can test either version with the following anonymous PL/SQL block:

/* Test the warner_brother procedure. */
BEGIN
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Cage');
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Tree House');
END;
/

Ultimately, we’ll use a natural join to test the integrity of primary and foreign key relationship:

/* Query results from warner_brother procedure. */
COL grandma_id          FORMAT 9999999  HEADING "Grandma|ID #"
COL grandma_house       FORMAT A20      HEADING "Grandma House"
COL tweetie_bird_id     FORMAT 9999999  HEADING "Tweetie|Bird ID"
COL tweetie_bird_house  FORMAT A20      HEADING "Tweetie Bird House"
SELECT *
FROM   grandma NATURAL JOIN tweetie_bird;

As always, I hope this helps those trying to leverage existing PL/SQL code into more modern code.

Written by maclochlainn

March 30th, 2025 at 6:58 pm

Posted in Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,pl/sql

Tagged with ,

PL/SQL Coupled Loops

without comments

A standard approach using fall through in languages that support it, like C, C++, and Java leverages a case structure. Unfortunately, in language that follow the Pascal pattern case statements act like if-else-if-else statements. In the Pascal model, you need to use coupled loops.

PL/SQL follows that Pascal, via Ada, structure. So, you must couple the performance of the inner descending loop to the maximum value of the outer ascending loop. Here’s an example of how you would code that in PL/SQL with the 12 Days of Christmas song, which has a chorus that grows with each verse.

It requires the definition of two ADTs (Attribute Data Types), a UDT (User-Defined Data Type) like a struct(ure), and a UDT table of the UDT struct. They’re all done in this code snippet.

/* Create a list of strings. */
CREATE OR REPLACE
  TYPE days IS TABLE OF VARCHAR2(8);
/
 
/* Create a list of strings. */
CREATE OR REPLACE
  TYPE song IS TABLE OF VARCHAR2(36);
/
 
/* Create a record structure. */
CREATE OR REPLACE
  TYPE lyric IS OBJECT
  ( DAY  VARCHAR2(8)
  , gift VARCHAR2(24));
/
 
/* Create a list of the record structure. */
CREATE OR REPLACE
  TYPE lyrics IS TABLE OF LYRIC;
/

The twelve_days function that will display the lyrics of the 12-Days of Christmas. It reads forward through the 12 days of Christmas and backwards through the chorus in the inner loop beginning with the current day of the outer 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40

/* Create a function. */
CREATE OR REPLACE
  FUNCTION twelve_days
  ( pv_days DAYS
  , pv_gifts LYRICS ) RETURN song IS
 
    /* Initialize the collection of lyrics. */
    lv_retval SONG := song();
 
    /* Local procedure to add to the song. */
    PROCEDURE ADD
    ( pv_input VARCHAR2 ) IS
    BEGIN
      lv_retval.EXTEND;
      lv_retval(lv_retval.COUNT) := pv_input;
    END ADD;
 
  BEGIN
    /* Read forward through the days. */
    FOR i IN 1..pv_days.COUNT LOOP
      ADD('On the ' || pv_days(i) || ' day of Christmas');
      ADD('my true love sent to me:');
 
      /* Read backward through the lyrics based on ascending value of the day. */
      FOR j IN REVERSE 1..i LOOP
        IF i = 1 THEN
          ADD('- '||'A'||' '||pv_gifts(j).gift);
        ELSE
          ADD('- '||pv_gifts(j).DAY||' '||pv_gifts(j).gift);
        END IF;
      END LOOP;
 
      /* A line break by verse. */
      ADD(CHR(13));
    END LOOP;
 
    /* Return the song's lyrics. */
    RETURN lv_retval;
  END;
/

The typical test case for the function in PL/SQL is:

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

/* Test the function in PL/SQL. */
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  /*
  * Declare an lv_days array of an 8 character variable length string
  * and initialize it with values.
  */
  lv_days days := days('first','second','third','fourth'
                      ,'fifth','sixth','seventh','eighth'
                      ,'nineth','tenth','eleventh','twelfth');
 
  /*
  * Declare an lv_gifts array of the user-defined LYRIC data type and
  * initialize it with values.
  */
  lv_gifts lyrics := lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree')
                           ,lyric(DAY => 'Two', gift => 'Turtle doves')
                           ,lyric(DAY => 'Three', gift => 'French hens')
                           ,lyric(DAY => 'Four', gift => 'Calling birds')
                           ,lyric(DAY => 'Five', gift => 'Golden rings' )
                           ,lyric(DAY => 'Six', gift => 'Geese a laying')
                           ,lyric(DAY => 'Seven', gift => 'Swans a swimming')
                           ,lyric(DAY => 'Eight', gift => 'Maids a milking')
                           ,lyric(DAY => 'Nine', gift => 'Ladies dancing')
                           ,lyric(DAY => 'Ten', gift => 'Lords a leaping')
                           ,lyric(DAY => 'Eleven',gift => 'Pipers piping')
                           ,lyric(DAY => 'Twelve',gift => 'Drummers drumming'));
 
  /*
  * Declare an lv_days array of an 36 character variable length string
  * and initialize it with values.
  */
  lv_song song := song();
 
BEGIN
  /* Call the twelve_days function and assign the results to the local
  * lv_song variable.
  */
  lv_song := twelve_days(lv_days,lv_gifts);
 
  /*
  * Read the lines from the local lv_song variable and print them.
  */
  FOR i IN 1..lv_song.LAST LOOP
    dbms_output.put_line(lv_song(i));
  END LOOP;
END;
/

It displays:

On the first day of Christmas
my true love sent to me:
- A Partridge in a pear tree
 
On the second day of Christmas
my true love sent to me:
- Two Turtle doves
- and a Partridge in a pear tree
 
On the third day of Christmas
my true love sent to me:
- Three French hens
- Two Turtle doves
- and a Partridge in a pear tree
 
...
 
On the twelfth day of Christmas
my true love sent to me:
- Twelve Drummers drumming
- Eleven Pipers piping
- Ten Lords a leaping
- Nine Ladies dancing
- Eight Maids a milking
- Seven Swans a swimming
- Six Geese a laying
- Five Golden rings
- Four Calling birds
- Three French hens
- Two Turtle doves
- and a Partridge in a pear tree

As always, I hope this helps those learning how to write PL/SQL and/or code.

Written by maclochlainn

March 28th, 2025 at 7:42 pm

Posted in Oracle,Oracle 12c,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,pl/sql

Tagged with

Hidden thought SQL question

without comments

I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.

It’s a simple question that tests two levels of understanding. The question is:

  • If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?

Level 1 Understanding

Here’s the test script:

CREATE TABLE a1
( text  VARCHAR2(12) );
INSERT INTO a1 ( text ) VALUES ('Hello World!');
CREATE TABLE b1
( text  VARCHAR2(12) );
INSERT INTO b1 ( text ) VALUES ('Hello World!');
ROLLBACK;

The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.

Level 2 Understanding

Here’s the test script:

CREATE TABLE a2 AS (SELECT 'Hello World!' AS text);
CREATE TABLE b2 AS (SELECT 'Hello World!' AS text);
ROLLBACK;

The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.

Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.

Written by maclochlainn

March 21st, 2025 at 10:38 pm

Posted in Oracle,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,sql

Tagged with ,

A tkprof Korn Shell

without comments

Reviewing old files, I thought posting my tkprof.ksh would be helpful. So, here’s the script that assumes you’re using Oracle e-Business Suite (Demo database, hence the APPS/APPS connection); and if I get a chance this summer I’ll convert it to Bash shell.

#!/bin/ksh
# -------------------------------------------------------------------------
# Author:   Michael McLaughlin
# Name:     tkprof.ksh
 
# Purpose:  The program takes the following arguments:
#           1. A directory
#           2. A search string
#           3. A target directory
#           It assumes raw trace files have an extension of ".trc".
#           The output file name follows this pattern (because it is
#           possible for multiple tracefiles to be written during the
#           same minute).
# -------------------------------------------------------------------------
 
# Function to find minimum field delimiter.
function min
{
  # Find the whitespace that preceeds the file date.
  until [[ $(ls -al $i | cut -c$minv-$minv) == " " ]]; do
    let minv=minv+1
  done
}
 
# Function to find maximum field delimiter.
function max
{
  # Find the whitespace that succeeds the file date.
  until [[ $(ls -al $i | cut -c$maxv-$maxv) == " " ]]; do
    let maxv=maxv+1
  done
}
 
# Debugging enabled by unremarking the "set -x"
# set -x
 
# Print header information
print =================================================================
print Running [tkprof.ksh] script ...
 
# Evaluate whether an argument is provide and if no argument
# is provided, then substitute the present working directory.
if   [[ $# == 0 ]]; then
  dir=${PWD}  
  str="*"
  des=${PWD}  
elif [[ $# == 1 ]]; then
  dir=${1}
  str="*"
  des=${1}
elif [[ $# == 2 ]]; then
  dir=${1}
  str=${2}
  des=${1}
elif [[ $# == 3 ]]; then
  dir=${1}
  str=${2}
  des=${3}
fi
 
# Evaluate whether the argument is a directory file.
if [[ -d ${dir} ]] && [[ -d ${des} ]]; then
 
  # Print what directory and search string are targets.
  print =================================================================
  print Run in tkprof from [${dir}] directory ...
  print The files contain a string of [${str}] ...
  print =================================================================
 
  # Evaluate whether the argument is the present working
  # directory and if not change directory to that target
  # directory so file type evaluation will work.
  if [[ ${dir} != ${PWD} ]]; then
    cd ${dir} 
  fi
 
  # Set file counter.
  let fcnt=0
 
  # Submit compression to the background as a job.
  for i in $(grep -li "${str}" *.trc); do
 
    # Evaluate whether file is an ordinary file.
    if [[ -f ${i} ]]; then
 
      # Set default values each iteration.
      let minv=40
      let maxv=53
 
      # Increment counter.
      let fcnt=fcnt+1
 
      # Call functions to reset min and max values where necessary.
      min ${i}
      max ${i}
 
      # Parse date stamp from trace file without multiple IO calls.
      # Assumption that the file is from the current year.
      date=$(ls -al ${i} | cut -c${minv}-${maxv}) 
      mon=$(echo ${date} | cut -c1-3)
      yr=$(date          | cut -c25-28)
 
      # Validate month is 10 or greater to pad for reduced whitespace.
      if (( $(echo ${date} | cut -c5-6) < 10 )); then
        day=0$(echo ${date}| cut -c5-5)
        hr=$(echo ${date}  | cut -c7-8)
        min=$(echo ${date} | cut -c10-11)
      else
        day=$(echo ${date} | cut -c5-6)
        hr=$(echo ${date}  | cut -c8-9)
        min=$(echo ${date} | cut -c11-12)
      fi
 
      fn=file${fcnt}_${day}-${mon}-${yr}_${hr}:${min}:${day}
 
      print Old [$i] and new [$des/$fn]
      tkprof ${i} ${des}/${fn}.prf explain=APPS/APPS sort='(prsela,exeela,fchela)'
 
      # Print what directory and search string are targets.
      print =================================================================
    fi
  done
 
else
  # Print message that a directory argument was not provided.
  print You failed to provie a single valid directory argument.
fi

I hope this helps those looking for a solution.

Written by maclochlainn

May 21st, 2023 at 2:25 am

Posted in Linux,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle 21c,Unix

Tagged with ,

GROUP BY Quirk

without comments

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

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

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

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

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

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

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

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

The correct way to write the GROUP BY is:

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

Then, it would return:

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

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

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

and, another complete test case for Oracle:

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

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

Written by maclochlainn

January 12th, 2023 at 11:30 pm

Posted in MySQL,MySQL 8,Oracle,Oracle 18c,Oracle 21c,PostgreSQL,PostgreSQL 15,SQL Server,SQL Server 2014

Tagged with , , ,

PL/SQL Table Function

without comments

An Oracle example was requested as a comparison against the quick tutorial I wrote on how to do this in PostgreSQL’s PL/pgSQL. Unfortunately, there are many more moving parts to deliver this type of solution in Oracle’s PL/SQL.

The functions is same and simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German. The additional moving parts are the required User-Defined Types (UDTs); one is a record structure and the other is a list (or Oracle parlance table).

The drops are unconditional and as such will trigger errors the first time they’re run but including PL/SQL blocks to make them conditional would have made the code much larger. It’s already larger because Oracle doesn’t support comma-delimited lists in the VALUES clause.

I’ll stage this with the same conquistador table used in the last post. Then, connect to the psql shell and run the following script file:

/* Drop the conquistador table. */
DROP TABLE conquistador;
 
/* Create the conquistador table. */
CREATE TABLE conquistador
( conquistador_id   NUMBER
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30)
, lang              VARCHAR(2));
 
/* Drop the conquistador sequence. */
DROP SEQUENCE conquistador_seq;
 
/* Create the conquistador_seq with a 1001 start value. */
CREATE SEQUENCE conquistador_seq START WITH 1001;
 
/* Insert 9 rows into the table. */
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Juan de Fuca','Ioánnis Fokás','Greek','el');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES 
(conquistador_seq.NEXTVAL,'Nicolás de Federmán','Nikolaus Federmann','German','de');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Sebastián Caboto','Sebastiano Caboto','Venetian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Jorge de la Espira','Georg von Speyer','German','de');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Wenceslao Linck','Wenceslaus Linck','Bohemian','cs');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Fernando Consag','Ferdinand Konšcak','Croatian','sr');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Américo Vespucio','Amerigo Vespucci','Italian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Alejo García','Aleixo Garcia','Portuguese','pt');

While unnecessary in PL/pgSQL, you must create User-Defined Types (UDTs) to write a table function. You must also create a local procedure to avoid allocating memory before assigning values to the list. These are the UDTs required:

/* Drop the dependency before the dependent type. */
DROP TYPE conquistador_table;
DROP TYPE conquistador_struct;
 
/* Create the UDT for a record structure accessible in SQL. */
CREATE OR REPLACE
  TYPE conquistador_struct IS OBJECT
  ( conquistador      VARCHAR(30)
  , actual_name       VARCHAR(30)
  , nationality       VARCHAR(30));
/
 
/* Create a list of the UDT. */
CREATE OR REPLACE
  TYPE conquistador_table IS TABLE OF conquistador_struct;
/

Drop any existing function or procedure of the same name before you try to build it. Oracle’s OR REPLACE fails when you try to use it for a function when there is already a procedure using the same name, and vice versa.

/* Drop the function to avoid any conflict with a procedure of
   the same name. */
DROP FUNCTION getConquistador;

Now, you can build another script file to create the getConquistador function, like:

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

/* Create the function. */
CREATE OR REPLACE
  FUNCTION getConquistador
  (pv_lang IN VARCHAR) RETURN conquistador_table IS
 
  /* Declare a return variable. */
  lv_retval  CONQUISTADOR_TABLE := conquistador_table();
 
  /* Declare a dynamic cursor. */
  CURSOR get_conquistador
  ( cv_lang  VARCHAR2 ) IS
    SELECT c.conquistador
    ,      c.actual_name
    ,      c.nationality
    FROM   conquistador c
    WHERE  c.lang = cv_lang;
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  CONQUISTADOR_STRUCT ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read through the cursor and assign to the UDT table. */
  FOR i IN get_conquistador(pv_lang) LOOP
    add(conquistador_struct( i.conquistador
                           , i.actual_name
                           , i.nationality ));
  END LOOP;
 
  /* Return collection. */
  RETURN lv_retval;
END;
/

While there is some white space for readability, the Oracle version is basically twice as long as the PL/pgSQL version. It also requires you to add UDTs to the data dictionary to make it work. PL/pgSQL actually doesn’t let you add references to type definitions and requires you use enumerated descriptions with column definitions.

Then, you can test it with the following syntax. The TABLE function is required to convert the list to a SQL consumable result set:

COL conquistador  FORMAT A21
COL actual_name   FORMAT A21
COL nationality   FORMAT A12
SELECT * FROM TABLE(getConquistador('de'));

It will return the following:

CONQUISTADOR          ACTUAL_NAME           NATIONALITY
--------------------- --------------------- ------------
Nicolás de Federmán   Nikolaus Federmann    German
Jorge de la Espira    Georg von Speyer      German
 
2 rows selected.

As always, I hope this helps with a technique that’s useful.

Written by maclochlainn

May 28th, 2022 at 6:47 pm

Posted in Linux,Microsoft Windows 10,Oracle,Oracle 18c,Oracle 21c,Oracle XE,pl/sql,PostgreSQL 14,sql,Windows 10

PL/SQL List to Struct

without comments

Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.

Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.

Oracle does this for two reasons:

The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.

You need to build a UDT object type and lists of both ADT and UDT data types.

/* Create a table of strings. */
CREATE OR REPLACE
  TYPE tre AS TABLE OF VARCHAR2(20);
/
 
/* Create a structure of a date, number, and string. */
CREATE OR REPLACE
  TYPE struct IS OBJECT
  ( xdate     DATE
  , xnumber  NUMBER
  , xstring  VARCHAR2(20));
/
 
/* Create a table of tre type. */
CREATE OR REPLACE
  TYPE structs IS TABLE OF struct;
/

The cast_strings function is defined below:

CREATE OR REPLACE
  FUNCTION cast_strings
  ( pv_list  TRE ) RETURN struct IS
 
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := struct( xdate => NULL
                             , xnumber => NULL
					         , xstring => NULL); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..pv_list.LAST LOOP
      /* Ensure that a sparsely populated list can't fail. */
      IF pv_list.EXISTS(i) THEN
        /* Order if number evaluation before string evaluation. */
        CASE
          WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN
            lv_retval.xnumber := pv_list(i);
          WHEN verify_date(pv_list(i)) THEN
            IF lv_retval.xdate IS NULL THEN
              lv_retval.xdate := pv_list(i);
            ELSE
              lv_retval.xdate := NULL;
            END IF;
          WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN
            lv_retval.xstring := pv_list(i);
          ELSE
            NULL;
        END CASE;
      END IF;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
/

There are three test cases for this function:

  • The first use-case checks whether the input parameter is a sparsely or densely populated list: 

    DECLARE
  /* Declare an input variable of three or more elements. */
  lv_list    TRE := tre('Berlin','25','09-May-1945','45');
 
  /* Declare a variable to hold the compound type values. */
  lv_struct  STRUCT;
BEGIN
  /* Make the set sparsely populated. */
  lv_list.DELETE(2);
 
  /* Test the cast_strings function. */
  lv_struct := cast_strings(lv_list);
 
  /* Print the values of the compound variable. */
  dbms_output.put_line(CHR(10));
  dbms_output.put_line('xstring ['||lv_struct.xstring||']');
  dbms_output.put_line('xdate   ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']');
  dbms_output.put_line('xnumber ['||lv_struct.xnumber||']');
END;
/

    It should return:

    xstring [Berlin]
xdate   [09-MAY-1945]
xnumber [45]

    The program defines two numbers and deletes the first number, which is why it prints the second number.

  • The second use-case checks with a list of only one element: 

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
,      xnumber
,      xstring
FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));

    It should return:

    XDATE                   XNUMBER XSTRING
-------------------- ---------- --------------------
25-NOV-1945                  25 catch22

    The program returns a structure with values converted into their appropriate data type.

  • The third use-case checks with a list of two elements: 

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
,      xnumber
,      xstring
FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))
                    ,cast_strings(tre('31-APR-2017','1918','areodromes'))));

    It should return:

    XDATE                   XNUMBER XSTRING
-------------------- ---------- --------------------
25-NOV-1945                  25 catch22
                           1918 areodromes

    The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.

As always, I hope this helps those looking for how to solve this type of problem.

Written by maclochlainn

May 23rd, 2022 at 12:18 am

Posted in Database,Database Design,DBA,DevOps,Linux,Oracle,Oracle 12c,Oracle 18c,Oracle 21c,Oracle DBA,Oracle Developer,Oracle XE,Unix,Windows OS

Tagged with ,

PL/SQL CASE Not Found

without comments

I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.

The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.

The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:

FROM   TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk'))))
                     *
ERROR AT line 2:
ORA-06592: CASE NOT found WHILE executing CASE statement
ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30
ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18

As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.

The fixed code 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

CREATE OR REPLACE
  FUNCTION verify_date
  ( pv_date_in  VARCHAR2) RETURN BOOLEAN IS
 
  /* Local variable to ensure case-insensitive comparison. */
  lv_date_in  VARCHAR2(11);
 
  /* Local return variable. */
  lv_date  BOOLEAN := FALSE;
BEGIN
  /* Convert string input to uppercase month. */
  lv_date_in := UPPER(pv_date_in);
 
  /* Check for a DD-MON-RR or DD-MON-YYYY string. */
  IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN
    /* Case statement checks for 28 or 29, 30, or 31 day month. */
    CASE
      /* Valid 31 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN 
        lv_date := TRUE;
      /* Valid 30 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN 
        lv_date := TRUE;
      /* Valid 28 or 29 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN
        /* Verify 2-digit or 4-digit year. */
        IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR
            LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND
            TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN
          lv_date := TRUE;
        ELSE /* Not a leap year. */
          IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN
            lv_date := TRUE;
          END IF;
        END IF;
      ELSE
        NULL;
    END CASE;
  END IF;
  /* Return date. */
  RETURN lv_date;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN lv_date;
END;
/

The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.

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

Written by maclochlainn

May 22nd, 2022 at 5:41 pm

Posted in Database,Database Design,DBA,DevOps,Linux,Oracle,Oracle 12c,Oracle 18c,Oracle 21c,pl/sql,Unix,Windows OS

Tagged with ,

PL/SQL List Function

without comments

Students wanted to see how to write PL/SQL functions that accept, process, and return lists of values. I thought it would be cool to also demonstrate coupling of loop behaviors and wrote the example using the 12-Days of Christmas lyrics.

The twelve_days function accepts two different collections. One is an Attribute Data Type (ADT) and the other a User-Defined Type (UDT). An ADT is based on a scalar data type, and a UDT is based on an object type. Object types are basically data structures, and they support both positional and named notation for variable assignments.

The twelve_days function returns a list of string, which is an ADT of the VARCHAR2 data type. Creating the ADT types is easy and a single step, like:

/* Create a days object type. */
CREATE OR REPLACE
  TYPE days IS TABLE OF VARCHAR2(8);
/
 
/* Create a string object type. */
CREATE OR REPLACE
  TYPE song IS TABLE OF VARCHAR2(36);
/

Creating the UDT is more complex and requires two steps. You need to create the UDT object type, or structure, and then the list based on the UDT object type, like:

/* Create a lyric object type. */
CREATE OR REPLACE
  TYPE lyric IS OBJECT
  ( DAY   VARCHAR2(8)
  , gift  VARCHAR2(24));
/
 
/* Create a lyrics object type. */
CREATE OR REPLACE
  TYPE lyrics IS TABLE OF LYRIC;
/

Now, you can create the twelve_days function that uses these ADT and UDT types, like:

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

CREATE OR REPLACE
  FUNCTION twelve_days
  ( pv_days   DAYS
  , pv_gifts  LYRICS ) RETURN song IS
 
  /* Initialize the collection of lyrics. */
  lv_retval  SONG := song();
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  VARCHAR2 ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read forward through the days. */
  FOR i IN 1..pv_days.COUNT LOOP
    ADD('On the ' || pv_days(i) || ' day of Christmas');
    ADD('my true love sent to me:');
 
    /* Read backward through the lyrics based on the ascending value of the day. */
    FOR j IN REVERSE 1..i LOOP
      IF i = 1 THEN
        ADD('-'||'A'||' '||pv_gifts(j).gift);
      ELSE
        ADD('-'||pv_gifts(j).DAY||' '||pv_gifts(j).gift);
      END IF;
    END LOOP;
 
    /* A line break by verse. */
    ADD(CHR(13));
  END LOOP;
 
  /* Return the song's lyrics. */
  RETURN lv_retval;
END;
/

You may notice the local add procedure on lines 10 thru 15. It lets you perform the two tasks required for populating an element in a SQL object type list in one line in the main body of the twelve_days function.

The add procedure first uses the EXTEND function to allocate space before assigning the input value to the newly allocated element in the list. Next, you can call the function inside the following SQL query:

SELECT column_value AS "12-Days of Christmas"
FROM   TABLE(twelve_days(days('first','second','third','fourth'
                             ,'fifth','sixth','seventh','eighth'
                             ,'nineth','tenth','eleventh','twelfth')
                        ,lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree')
                               ,lyric(DAY => 'Two',   gift => 'Turtle doves')
                               ,lyric(DAY => 'Three', gift => 'French hens')
                               ,lyric(DAY => 'Four',  gift => 'Calling birds')
                               ,lyric(DAY => 'Five',  gift => 'Golden rings' )
                               ,lyric(DAY => 'Six',   gift => 'Geese a laying')
                               ,lyric(DAY => 'Seven', gift => 'Swans a swimming')
                               ,lyric(DAY => 'Eight', gift => 'Maids a milking')
                               ,lyric(DAY => 'Nine',  gift => 'Ladies dancing')
                               ,lyric(DAY => 'Ten',   gift => 'Lords a leaping')
                               ,lyric(DAY => 'Eleven',gift => 'Pipers piping')
                               ,lyric(DAY => 'Twelve',gift => 'Drummers drumming'))));

It will print:

12-Days of Christmas
------------------------------------
On the first day of Christmas
my true love sent to me:
-A Partridge in a pear tree
 
On the second day of Christmas
my true love sent to me:
-Two Turtle doves
-and a Partridge in a pear tree
 
On the third day of Christmas
my true love sent to me:
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree
 
... redacted for space ...
 
On the twelfth day of Christmas
my true love sent to me:
-Twelve Drummers drumming
-Eleven Pipers piping
-Ten Lords a leaping
-Nine Ladies dancing
-Eight Maids a milking
-Seven Swans a swimming
-Six Geese a laying
-Five Golden rings
-Four Calling birds
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree

As always, I hope the example helps those looking for a solution to this type of problem.

Written by maclochlainn

May 13th, 2022 at 12:57 am

Posted in Database,Database Design,DBA,DevOps,Oracle,Oracle 11g,Oracle 18c,Oracle 21c,pl/sql,sql

Tagged with ,

Magic WITH Clause

without comments

Magic WITH Clause

Learning Outcomes

  • Learn how to use the WITH clause.
  • Learn how to join the results of two WITH clauses.

Lesson Materials

The idea of modularity is important in every programming environment. SQL is no different than other programming languages in that regard. SQL-92 introduced the ability to save queries as views. Views are effectively modular views of data.

A view is a named query that is stored inside the data dictionary. The contents of the view change as the data in the tables that are part of the view changes.

SQL:1999 added the WITH clause, which defines statement scoped views. Statement scoped views are named queries, or queries named as views, only in the scope of a query where they are defined.

The simplest prototype for a WITH clause that contains a statement scoped view is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
  SELECT column1, column2, ...
  FROM   table_name tn INNER JOIN query_name qn
  ON     tn.column_name = qn.column_name 
  WHERE  qn.column_name = 'Some literal';

You should note that the list of columns after the query name is an optional list. The list of columns must match the SELECT-list, which is the set of comma delimited columns of the SELECT clause.

A more complete prototype for a WITH clause shows you how it can contain two or more statement scoped views. That prototype is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
, query_name2
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
SELECT column1, column2, ...
FROM   table_name tn INNER JOIN query_name1 qn1
ON     tn.column_name = qn1.column_name INNER JOIN query_name2 qn2
ON     qn1.column_name = qn2.column_name;
WHERE  qn1.column_name = 'Some literal';

The WITH clause has several advantages over embedded view in the FROM clause or subqueries in various parts of a query or SQL statement. The largest advantage is that a WITH clause is a named subquery and you can reference it from multiple locations in a query; whereas, embedded subqueries are unnamed blocks of code and often results in replicating a single subquery in multiple locations.

A small model of three tables lets you test a WITH clause in the scope of a query. It creates a war, country, and ace tables. The tables are defined as:

WAR

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
WAR_ID                                    NUMBER
WAR_NAME                                  VARCHAR2(30)

COUNTRY

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
COUNTRY_ID                                NUMBER
COUNTRY_NAME                              VARCHAR2(20)

ACE

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
ACE_ID                                    NUMBER
ACE_NAME                                  VARCHAR2(30)
COUNTRY_ID                                NUMBER
WAR_ID                                    NUMBER

The following WITH clause includes two statement scoped views. One statement scoped view queries results form a single table while the other queries results from a join between the country and ace tables.

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

CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON war_name SKIP PAGE
 
COL ace_id        FORMAT 9999 HEADING "Ace|ID #"
COL ace_name      FORMAT A24  HEADING "Ace Name"
COL war_name      FORMAT A12  HEADING "War Name"
COL country_name  FORMAT A14  HEADING "Country Name"
WITH wars (war_id, war_name) AS
 (SELECT w.war_id, war_name
  FROM   war w )
, aces (ace_id, ace_name, country_name, war_id) AS
 (SELECT   a.ace_id
  ,        a.ace_name
  ,        c.country_name
  ,        a.war_id
  FROM     ace a INNER JOIN country c
  ON       a.country_id = c.country_id)
SELECT   a.ace_id
,        a.ace_name
,        w.war_name
,        a.country_name
FROM     aces a INNER JOIN wars w
ON       a.war_id = w.war_id
ORDER BY war_name
,        CASE
           WHEN REGEXP_INSTR(ace_name,' ',1,2,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,2,1),LENGTH(ace_name) - REGEXP_INSTR(ace_name,' ',1,2,0))
           WHEN REGEXP_INSTR(ace_name,' ',1,1,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,1,1),LENGTH(ace_name))
         END;

wars is the first statement scoped view of the war table. aces is the second statement scoped view of the inner join between the ace and country tables. You should note that aces statement scoped view has access to the wars scoped view, and the master SELECT statement has scope access to both statement scoped views and any tables in its schema.

The query returns the following with the help of SQL*Plus formatting BREAK statements:

  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1009 William Terry Badham     World War I  America
 1003 Albert Ball			    United Kingdom
 1010 Charles John Biddle		    America
 1005 William Bishop			    Canada
 1007 Keith Caldwell			    New Zealand
 1006 Georges Guynemer			    France
 1008 Robert Alexander Little		    Austrailia
 1001 Manfred von Richtofen		    Germany
 1002 Eddie Rickenbacker		    America
 1004 Werner Voss			    Germany
 
  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1018 Richard Bong	       World War II America
 1015 Edward F Charles			    Canada
 1020 Heinrich Ehrler			    Germany
 1019 Ilmari Juutilainen		    Finland
 1014 Ivan Kozhedub			    Soviet Union
 1012 Thomas McGuire			    America
 1013 Pat Pattle			    United Kingdom
 1011 Erich Rudorffer			    Germany
 1016 Stanislaw Skalski 		    Poland
 1017 Teresio Vittorio			    Italy
 
20 rows selected.

The WITH clause is the most effective solution when you have a result set that needs to be consistently used in two or more places in a master query. That’s because the result set becomes a named statement scoped view.

Script Code

Click the Script Code link to open the test case seeding script inside the current webpage.

Script Code →

The following script includes tables that will not conflict with other lab assignments. It creates the war, ace, and country tables; war_s, ace_s, and country_s sequences; and the WITH enabled query from this blog page. 

DROP TABLE war;
CREATE TABLE war
( war_id    NUMBER
, war_name  VARCHAR2(30));
 
DROP SEQUENCE war_s;
CREATE SEQUENCE war_s START WITH 1001;
 
DROP TABLE ace;
CREATE TABLE ace
( ace_id      NUMBER
, ace_name    VARCHAR2(30)
, country_id  NUMBER
, war_id      NUMBER);
 
DROP SEQUENCE ace_s;
CREATE SEQUENCE ace_s START WITH 1001;
 
DROP TABLE country;
CREATE TABLE country
( country_id    NUMBER
, country_name  VARCHAR2(20));
 
DROP SEQUENCE country_s;
CREATE SEQUENCE country_s START WITH 1001;
 
/*
|| Insert two rows for wars.
*/
INSERT INTO war
( war_id
, war_name )
VALUES
( war_s.NEXTVAL
,'World War I');
 
INSERT INTO war
( war_id
, war_name )
VALUES
( war_s.NEXTVAL
,'World War II');
 
/*
|| Insert ten rows for countries.
*/
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Germany');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Italy');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Poland');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'United Kingdom');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'France');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Finland');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Soviet Union');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'America');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Austrailia');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'New Zealand');
 
INSERT INTO country
( country_id
, country_name )
VALUES
( country_s.NEXTVAL
,'Canada');
 
/*
|| Insert twenty rows for aerial combat aces.
*/
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Manfred von Richtofen'
,(SELECT country_id FROM country WHERE country_name = 'Germany')
,(SELECT war_id FROM war WHERE war_name = 'World War I'));
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Eddie Rickenbacker'
,(SELECT country_id FROM country WHERE country_name = 'America')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Albert Ball'
,(SELECT country_id FROM country WHERE country_name = 'United Kingdom')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Werner Voss'
,(SELECT country_id FROM country WHERE country_name = 'Germany')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'William Bishop'
,(SELECT country_id FROM country WHERE country_name = 'Canada')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Georges Guynemer'
,(SELECT country_id FROM country WHERE country_name = 'France')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Keith Caldwell'
,(SELECT country_id FROM country WHERE country_name = 'New Zealand')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Robert Alexander Little'
,(SELECT country_id FROM country WHERE country_name = 'Austrailia')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'William Terry Badham'
,(SELECT country_id FROM country WHERE country_name = 'America')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Charles John Biddle'
,(SELECT country_id FROM country WHERE country_name = 'America')
,(SELECT war_id FROM war WHERE war_name = 'World War I')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Erich Rudorffer'
,(SELECT country_id FROM country WHERE country_name = 'Germany')
,(SELECT war_id FROM war WHERE war_name = 'World War II')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Thomas McGuire'
,(SELECT country_id FROM country WHERE country_name = 'America')
,(SELECT war_id FROM war WHERE war_name = 'World War II'));  
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Pat Pattle'
,(SELECT country_id FROM country WHERE country_name = 'United Kingdom')
,(SELECT war_id FROM war WHERE war_name = 'World War II'));  
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Ivan Kozhedub'
,(SELECT country_id FROM country WHERE country_name = 'Soviet Union')
,(SELECT war_id FROM war WHERE war_name = 'World War II'));  
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Edward F Charles'
,(SELECT country_id FROM country WHERE country_name = 'Canada')
,(SELECT war_id FROM war WHERE war_name = 'World War II'));  
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Stanislaw Skalski'
,(SELECT country_id FROM country WHERE country_name = 'Poland')
,(SELECT war_id FROM war WHERE war_name = 'World War II'));  
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Teresio Vittorio'
,(SELECT country_id FROM country WHERE country_name = 'Italy')
,(SELECT war_id FROM war WHERE war_name = 'World War II')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Richard Bong'
,(SELECT country_id FROM country WHERE country_name = 'America')
,(SELECT war_id FROM war WHERE war_name = 'World War II')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Ilmari Juutilainen'
,(SELECT country_id FROM country WHERE country_name = 'Finland')
,(SELECT war_id FROM war WHERE war_name = 'World War II')); 
 
INSERT INTO ace
( ace_id
, ace_name
, country_id
, war_id )
VALUES
( ace_s.NEXTVAL
,'Heinrich Ehrler'
,(SELECT country_id FROM country WHERE country_name = 'Germany')
,(SELECT war_id FROM war WHERE war_name = 'World War II')); 
 
/*
|| Commit the records.
*/
COMMIT;
 
/*
|| Query from statement scoped views.
*/
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON war_name SKIP PAGE
 
COL ace_id        FORMAT 9999 HEADING "Ace|ID #"
COL ace_name      FORMAT A24  HEADING "Ace Name"
COL war_name      FORMAT A12  HEADING "War Name"
COL country_name  FORMAT A14  HEADING "Country Name"
WITH wars (war_id, war_name) AS
 (SELECT w.war_id, war_name
  FROM   war w )
, aces (ace_id, ace_name, country_name, war_id) AS
 (SELECT   a.ace_id
  ,        a.ace_name
  ,        c.country_name
  ,        a.war_id
  FROM     ace a INNER JOIN country c
  ON       a.country_id = c.country_id)
SELECT   a.ace_id
,        a.ace_name
,        w.war_name
,        a.country_name
FROM     aces a INNER JOIN wars w
ON       a.war_id = w.war_id
ORDER BY war_name
,        CASE
           WHEN REGEXP_INSTR(ace_name,' ',1,2,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,2,1),LENGTH(ace_name) - REGEXP_INSTR(ace_name,' ',1,2,0))
           WHEN REGEXP_INSTR(ace_name,' ',1,1,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,1,1),LENGTH(ace_name))
         END;

You can use the base query as a starting place to experiment with the WITH clause in a query.

Written by maclochlainn

May 12th, 2022 at 7:01 pm

Posted in Database,DBA,DevOps,Oracle,Oracle 18c,Oracle 21c,Oracle DBA,Oracle Developer

Tagged with ,

« Older Entries