MacLochlainns Weblog

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.

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.

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.

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:

• They handle lists of standard types in a difference C++ class than they do UDT types.
• They rigidly adhere to Interface Definition Language (IDL) principles.

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; /

  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]

  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'))));

  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

  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'))));

  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

  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.

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.

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.

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.

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;

As I move forward with trying to build an easy to use framework for data analysts who use multiple database backends and work on Windows OS, here’s a complete script that lets you run any query stored in a file to return a CSV file. It makes the assumption that you opted to put the user ID and password in the Windows ODBC DSN, and only provides the ODBC DSN name to make the connection to the ODBC library and database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

# A local function for verbose reporting.
function Get-Message ($param, $value = $null) {
  if (!($value)) {
    Write-Host "Evaluate swtich    [" $param "]" } 	  
  else {
    Write-Host "Evaluate parameter [" $param "] and [" $value "]" } 
}
 
# Read SQLStatement file and minimally parse it.
function Get-SQLStatement ($sqlStatement) {
  # Set localvariable for return string value.
  $statement = ""
 
  # Read a file line-by-line.
  foreach ($line in Get-Content $sqlStatement) {
    # Use regular expression to replace multiple whitespace.
    $line = $line -replace '\s+', ' '
 
    # Add a whitespace to avoid joining keywords from different lines;
    # and remove trailing semicolons which are unneeded.
    if (!($line.endswith(";"))) {
      $statement += $line + " " }
    else {
      $statement += $line.trimend(";") }
  }
  # Returned minimally parsed statement.
  return $statement
}
 
# Set default type of SQL statement value to a query.
$stmt = "select"
 
# Set a variable to hold a SQL statement from a file.
$query = ""
 
# Set default values for SQL input and output files.
$outFile = "output.csv"
$sqlFile = "query.sql"
 
# Set default path to: %USERPROFILE%\AppData\Local\Temp folder, but ir 
# the tilde (~) in lieu of the %USERPROFILE% environment variable value.
$path = "~\AppData\Local\Temp"
 
# Set a verbose switch.
$verbose = $false
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Check for switches and parameters with arguments.
for ($i = 0; $i -lt $args.count; $i += 1) {
  if (($args[$i].startswith("-")) -and ($args[$i + 1].startswith("-"))) {
    if ($args[$i] = "-v") {
      $verbose = $true }
      # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] }}
  elseif ($args[$i].startswith("-")) {
    # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] $args[$i + 1] }
 
    # Evaluate and take action on parameters and values.
    if ($args[$i] -eq "-o") {
      $outfile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $sqlFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-p") {
      $path = $args[$i + 1] }
  }
}
 
# Set a PowerShell Virtual Drive.
New-PSDrive -Name folder -PSProvider FileSystem -Description 'Forder Location' `
            -Root $path | Out-Null
 
# Remove the file only when it exists.
if (Test-Path folder:$outFile) {
  Remove-Item -Path folder:$outFile }
 
# Read SQL file into minimally parsed string.
if (Test-Path folder:$sqlFile) {
  $query = Get-SQLStatement $sqlFile }
 
# Set a ODBC DSN connection string.
$ConnectionString = 'DSN=OracleGeneric'
 
# Set an Oracle Command Object for a query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = $query;
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
      }
      # Write the output from the database to a file.
      Add-Content -Value $output -Path folder:$outFile
    }
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You can use a command-line call like this:

powershell ./OracleContact.ps1 -v -o output.csv -q script.sql -p .

It produces the following verbose output to the console:

Evaluate swtich    [ -v ]
Evaluate parameter [ -o ] and [ output.csv ]
Evaluate parameter [ -q ] and [ script.sql ]
Evaluate parameter [ -p ] and [ . ]

You can suppress printing to the console by eliminating the -v switch from the parameter list.

As always, I hope this helps those looking for a solution to less tedious interactions with the Oracle database.

Transaction Management

Learning Outcomes

• Learn how to use Multiversion Concurrency Control (MVCC).
• Learn how to manage ACID-compliant transactions.
• Learn how to use:
  • SAVEPOINT Statement
  • COMMIT Statement
  • ROLLBACK Statement

Lesson Material

Transaction Management involves two key components. One is Multiversion Concurrency Control (MVCC) so one user doesn’t interfere with another user. The other is data transactions. Data transactions packag SQL statements in the scope of an imperative language that uses Transaction Control Language (TCL) to extend ACID-compliance from single SQL statements to groups of SQL statements.

Multiversion Concurrency Control (MVCC)

Multiversion Concurrency Control (MVCC) uses database snapshots to provide transactions with memory-persistent copies of the database. This means that users, via their SQL statements, interact with the in-memory copies of data rather than directly with physical data. MVCC systems isolate user transactions from each other and guarantee transaction integrity by preventing dirty transactions, writes to the data that shouldn’t happen and that make the data inconsistent. Oracle Database 12c prevents dirty writes by its MVCC and transaction model.

Transaction models depend on transactions, which are ACID-compliant blocks of code. Oracle Database 12c provides an MVCC architecture that guarantees that all changes to data are ACID-compliant, which ensures the integrity of concurrent operations on data—transactions.

ACID-compliant transactions meet four conditions:

Atomic

They complete or fail while undoing any partial changes.

Consistent

They change from one state to another the same way regardless of whether
the change is made through parallel actions or serial actions.

Isolated

Partial changes are never seen by other users or processes in the concurrent system.

Durable

They are written to disk and made permanent when completed.

Oracle Database 12c manages ACID-compliant transactions by writing them to disk first, as redo log files only or as both redo log files and archive log files. Then it writes them to the database. This multiple-step process with logs ensures that Oracle database’s buffer cache (part of the instance memory) isn’t lost from any completed transaction. Log writes occur before the acknowledgement-of-transactions process occurs.

The smallest transaction in a database is a single SQL statement that inserts, updates, or deletes rows. SQL statements can also change values in one or more columns of a row in a table. Each SQL statement is by itself an ACID-compliant and MVCC-enabled transaction when managed by a transaction-capable database engine. The Oracle database is always a transaction-capable system. Transactions are typically a collection of SQL statements that work in close cooperation to accomplish a business objective. They’re often grouped into stored programs, which are functions, procedures, or triggers. Triggers are specialized programs that audit or protect data. They enforce business rules that prevent unauthorized changes to the data.

SQL statements and stored programs are foundational elements for development of business applications. They contain the interaction points between customers and the data and are collectively called the application programming interface (API) to the database. User forms (typically web forms today) access the API to interact with the data. In well-architected business application software, the API is the only interface that the form developer interacts with.

Database developers, such as you and I, create these code components to enforce business rules while providing options to form developers. In doing so, database developers must guard a few things at all cost. For example, some critical business logic and controls must prevent changes to the data in specific tables, even changes in API programs. That type of critical control is often written in database triggers. SQL statements are events that add, modify, or delete data. Triggers guarantee that API code cannot make certain additions, modifications, or deletions to critical resources, such as tables. Triggers can run before or after SQL statements. Their actions, like the SQL statements themselves, are temporary until the calling scope sends an instruction to commit the work performed.

A database trigger can intercept values before they’re placed in a column, and it can ensure that only certain values can be inserted into or updated in a column. A trigger overrides an INSERT or UPDATE statement value that violates a business rule and then it either raises an error and aborts the transaction or changes the value before it can be inserted or updated into the table. Chapter 12 offers examples of both types of triggers in Oracle Database 12c.
MVCC determines how to manage transactions. MVCC guarantees how multiple users’ SQL statements interact in an ACID compliant manner. The next two sections qualify how data transactions work and how MVCC locks and isolates partial results from data transactions.

Data Transaction

Data Manipulation Language (DML) commands are the SQL statements that transact against the data. They are principally the INSERT, UPDATE, and DELETE statements. The INSERT statement adds new rows in a table, the UPDATE statement modifies columns in existing rows, and the DELETE statement removes a row from a table.

The Oracle MERGE statement transacts against data by providing a conditional insert or update feature. The MERGE statement lets you add new rows when they don’t exist or change column values in rows that do exist.

Inserting data seldom encounters a conflict with other SQL statements because the values become a new row or rows in a table. Updates and deletes, on the other hand, can and do encounter conflicts with other UPDATE and DELETE statements. INSERT statements that encounter conflicts occur when columns in a new row match a preexisting row’s uniquely constrained columns. The insertion is disallowed because only one row can contain the unique column set.

These individual transactions have two phases in transactional databases such as Oracle. The first phase involves making a change that is visible only to the user in the current session. The user then has the option of committing the change, which makes it permanent, or rolling back the change, which undoes the transaction. Developers use Transaction Control Language (TCL) commands to confirm or cancel transactions. The COMMIT statement confirms or makes permanent any change, and the ROLLBACK statement cancels or undoes any change.

A generic transaction lifecycle for a two-table insert process implements a business rule that specifies that neither INSERT statement works unless they both work. Moreover, if the first INSERT statement fails, the second INSERT statement never runs; and if the second INSERT statement fails, the first INSERT statement is undone by a ROLLBACK statement to a SAVEPOINT.

After a failed transaction is unwritten, good development practice requires that you write the failed event(s) to an error log table. The write succeeds because it occurs after the ROLLBACK statement but before the COMMIT statement.

A SQL statement followed by a COMMIT statement is called a transaction process, or a two-phase commit (2PC) protocol. ACID-compliant transactions use a 2PC protocol to manage one SQL statement or collections of SQL statements. In a 2PC protocol model, the INSERT, UPDATE, MERGE, or DELETE DML statement starts the process and submits changes. These DML statements can also act as events that fire database triggers assigned to the table being changed.

Transactions become more complex when they include database triggers because triggers can inject an entire layer of logic within the transaction scope of a DML statement. For example, database triggers can do the following:

• Run code that verifies, changes, or repudiates submitted changes
• Record additional information after validation in other tables (they can’t write to the table being changed—or, in database lexicon, “mutated”
• Throw exceptions to terminate a transaction when the values don’t meet business rules

As a general rule, triggers can’t contain a COMMIT or ROLLBACK statement because they run inside the transaction scope of a DML statement. Oracle databases give developers an alternative to this general rule because they support autonomous transactions. Autonomous transactions run outside the transaction scope of the triggering DML statement. They can contain a COMMIT statement and act independently of the calling scope statement. This means an autonomous trigger can commit a transaction when the calling transaction fails.

As independent statements or collections of statements add, modify, and remove rows, one statement transacts against data only by locking rows: the SELECT statement. A SELECT statement typically doesn’t lock rows when it acts as a cursor in the scope of a stored program. A cursor is a data structure that contains rows of one-to-many columns in a stored program. This is also known as a list of record structures.

Cursors act like ordinary SQL queries, except they’re managed by procedure programs row by row. There are many examples of procedural programming languages. PL/SQL and SQL/PSM programming languages are procedural languages designed to run inside the database. C, C++, C#, Java, Perl, and PHP are procedural languages that interface with the database through well-defined interfaces, such as Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC).

Cursors can query data two ways. One way locks the rows so that they can’t be changed until the cursor is closed; closing the cursor releases the lock. The other way doesn’t lock the rows, which allows them to be changed while the program is working with the data set from the cursor. The safest practice is to lock the rows when you open the cursor, and that should always be the case when you’re inserting, updating, or deleting rows that depend on the values in the cursor not changing until the transaction lifecycle of the program unit completes.

Loops use cursors to process data sets. That means the cursors are generally opened at or near the beginning of program units. Inside the loop the values from the cursor support one to many SQL statements for one to many tables.

Stored and external programs create their operational scope inside a database connection when they’re called by another program. External programs connect to a database and enjoy their own operational scope, known as a session scope. The session defines the programs’ operational scope. The operational scope of a stored program or external program defines the transaction scope. Inside the transaction scope, the programs interact with data in tables by inserting, updating, or deleting data until the operations complete successfully or encounter a critical failure. These stored program units commit changes when everything completes successfully, or they roll back changes when any critical instruction fails. Sometimes, the programs are written to roll back changes when any instruction fails.

In the Oracle Database, the most common clause to lock rows is the FOR UPDATE clause, which is appended to a SELECT statement. An Oracle database also supports a WAIT n seconds or NOWAIT option. The WAIT option is a blessing when you want to reply to an end user form’s request and can’t make the change quickly. Without this option, a change could hang around for a long time, which means virtually indefinitely to a user trying to run your application. The default value in an Oracle database is NOWAIT, WAIT without a timeout, or wait indefinitely.

You should avoid this default behavior when developing program units that interact with customers. The Oracle Database also supports a full table lock with the SQL LOCK TABLE command, but you would need to embed the command inside a stored or external program’s instruction set.

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.