This article demonstrates how you create a full test case for your SQL and PL/SQL code.  While many developers rely on excellent tools, like Toad for Oracle, there comes a time when you have to pull it all together. You hope that the time comes when everything is ready to deploy. Unfortunately, that’s generally not the case.

It can be a lot of rework when you encounter a problem while still developing a solution. The late stage errors, typically during integration testing, cost you the most time and effort. Designing and writing a test case program lets you minimize the impact of mistakes. A test case program lets you sequence and run solutions as a whole. They let you make a course correction where necessary. They also let you run your solution by issuing a single command.

A bash shell program is the best way to automate your process when you organize deployment into self-managing modules. That means you decompose your problem into small re-runnable scripts, like

  • Data Definition Language (DDL) scripts let you drop and recreate tables, views, and sequences
  • Data Manipulation Language (DML) scripts let you seed the tables with testing data
  • DDL scripts that let you drop and recreate stored functions, procedures, packages and object types
  • SQL programs that seed your testing tables with sample data that mimics your production system
  • Test case programs that verify and validate your solution

The article examines the different types of re-runnable scripts. It also shows you how to implement and manage them in a small test application. More or less, we’re following Cary Millsap’s application of the carpenter’s adage “Measure Twice, Cut Once.”

There are four parts to the balance of this article. You will setup the file structure, design and deploy DDL scripts, design and deploy DML seeding scripts, and write comprehensive test case scripts.

Setting up the file structure

Oracle is famous, or infamous, from some developer’s perspective for creating the Oracle Flexible Architecture (OFA). OFA is nothing more than a File system Hierarchy Standard (FHS) for the Unix operating system. OFA only makes recommendations on where files should exist. OFA doesn’t constrain where you actually deploy them.

Like OFA, the following structure is only a recommendation. The main directory is what’s known as the product top for an Oracle XE default installation. You put the subdirectories inside the product top directory.

/u01/app/oracle/product

/custom

/bin

/log

/pls

/out

/sql

You put your executable programs, like bash shell programs in the /bin directory. You direct log values to the /log directory and any output from queries to the /out directory.  Log files are written by using the following SQL*Plus commands at the beginning and ending of your SQL scripts:

SPOOL file_name

... script contents ...

SPOOL OFF

-- Quit the SQL*Plus session.

QUIT;

Output files are written by queries that you redirect to a bash shell program. The /sql directory holds scripts that use SQL and the /pls directory holds scripts that drop, create, and recreated PL/SQL programs.

If you were also writing C/C++ and Java libraries, you’d add three additional directories. The /c and /lib directories for C/C++ and the /java directory for your Java projects.

Design and deploy DDL scripts

You design and deploy DDL scripts so that they are re-runnable without error. This requires writing PL/SQL blocks that let you conditionally drop tables, sequences, views, stored functions, procedures, packages, and types.

This paper uses three SQL scripts that conditionally drop and create the ACTOR, FILM, and MOVIE tables, and their respective ACTOR_S, FILM_S, and MOVIE_S sequences. The actor.sql script conditionally drops and creates the ACTOR table and ACTOR_S sequence.

The first part of the actor.sql script conditionally drops the ACTOR table and ACTOR_S sequence:

SQL> BEGIN

  2    FOR i IN (SELECT   object_name

  3             ,        object_type

  4             FROM     user_objects

  5              WHERE    object_name IN ('ACTOR','ACTOR_S')) LOOP

  6   IF    i.object_type = 'TABLE' THEN

  7     EXECUTE IMMEDIATE

  8       'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';

  9   ELSIF i.object_type = 'SEQUENCE' THEN

 10     EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;

 11   END IF;

 12    END LOOP;

 13  END;

 14  /

A static cursor inside the FOR-loop looks for the ACTOR table and ACTOR_S sequence on line 5. It drops a table on line 8 when the loop returns a table object type. Alternatively, it drops a sequence on line 10 when the loop returns a sequence object type.

You create the ACTOR table with the following:

SQL> CREATE TABLE actor

  2  ( actor_id    NUMBER CONSTRAINT actor_pk PRIMARY KEY

  3  , actor_name  VARCHAR(30)      NOT NULL );

and, ACTOR_S sequence with this:

SQL> CREATE SEQUENCE actor_s;

The film.sql script conditionally drops and creates the FILM table and FILM_S sequence. The first part of the film.sql script conditionally drops the FILM table and FILM_S sequence:

SQL> BEGIN

  2    FOR i IN (SELECT   object_name

  3              ,        object_type

  4             FROM     user_objects

  5              WHERE    object_name IN ('FILM','FILM_S')) LOOP

  6     IF i.object_type = 'TABLE' THEN

  7       EXECUTE IMMEDIATE

  8        'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';

  9      ELSIF i.object_type = 'SEQUENCE' THEN

 10       EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;

 11     END IF;

 12    END LOOP;

 13  END;

 14  /

Like the prior actor.sql script, the static cursor inside the FOR-loop looks for the FILM table and FILM_S sequence on line 5. It drops a table on line 8 when the loop returns a table object type. Alternatively, it drops a sequence on line 10 when the loop returns a sequence object type.

You create the FILM table with the following:

SQL> CREATE TABLE film

  2  ( film_id    NUMBER CONSTRAINT film_pk PRIMARY KEY

  3  , film_name  VARCHAR(30)  NOT NULL );

and, FILM_S sequence with this:

SQL> CREATE SEQUENCE film_s;

The movie.sql script conditionally drops and creates the MOVIE table and MOVIE_S sequence. The first part of the movie.sql script conditionally drops the MOVIE table and MOVIE_S sequence:

SQL> BEGIN

  2    FOR i IN (SELECT   object_name

  3          ,      object_type

  4          FROM   user_objects

  5          WHERE        object_name IN ('MOVIE','MOVIE_S')) LOOP

  6     IF i.object_type = 'TABLE' THEN

  7       EXECUTE IMMEDIATE

  8        'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';

  9     ELSIF i.object_type = 'SEQUENCE' THEN

 10       EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;

 11     END IF;

 12    END LOOP;

 13  END;

 14  /

Like the prior movie.sql script, the static cursor inside the FOR-loop looks for the MOVIE table and MOVIE_S sequence on line 5. It drops a table on line 8 when the loop returns a table object type. Alternatively, it drops a sequence on line 10 when the loop returns a sequence object type.

You create the MOVIE table with the following:

SQL> CREATE TABLE movie

  2  ( movie_id   NUMBER  CONSTRAINT movie_pk   PRIMARY KEY

  3  , actor_id   NUMBER  CONSTRAINT movie_nn1  NOT NULL

  4  , film_id    NUMBER  CONSTRAINT movie_nn2  NOT NULL

  5  , CONSTRAINT actor_fk FOREIGN KEY (actor_id)

  6    REFERENCES actor (actor_id)

  7  , CONSTRAINT film_fk  FOREIGN KEY (film_id)

  8    REFERENCES film(film_id));

and, MOVIE_S sequence with this:

SQL> CREATE SEQUENCE movie_s;

After conditionally dropping the table and sequence, and creating the table and sequence, each of these script files quits the SQL*Plus session. This technique lets you call the SQL script file by using the –s (or silent) option.

You can call any of these script files like this:

sqlplus -s username/password @directory/filename

This syntax lets you call the actor.sql, film.sql, and movie.sql scripts from a bash script. That’s what the bash shell script will do later in this article.

Design and deploy DML seeding scripts

You design and deploy DML seeding scripts so that they are re-runnable without error. There are two approaches to writing DML seeding scripts. One verifies the data isn’t there before adding it and the other adds it because it assumes the data isn’t there. Dropping and recreating the tables is the safest way to ensure you have a clean environment before running DML seeding scripts.

The actor_dml.sql, film_dml.sql, and movie_dml.sql scripts simply insert rows of data. The actor_dml.sql and film_dml.sql scripts must run successfully before you run the movie_dml.sql script because the MOVIE table is an association table that links the ACTOR and FILM tables.

The actor_dml.sql script inserts as follows:

SQL> INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Hemsworth');

SQL> INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pine');

SQL> INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pratt');

The film_dml.sql script inserts as follows:

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Thor');

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Thor: The Dark World');

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek');

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek into Darkness');

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Guardians of the Galaxy');

SQL> INSERT INTO film VALUES (film_s.NEXTVAL,'Jurassic World');

The movie_dml.sql script inserts rows to match the actors with the films. Only one example is shown to conserve space.

SQL> INSERT INTO movie

  2  VALUES

  3  ( movie_s.NEXTVAL

  4  ,(SELECT     actor_id

  5    FROM actor

  6    WHERE      actor_name = 'Chris Hemsworth')

  7  ,(SELECT     film_id

  8    FROM film

  9    WHERE      film_name = 'Thor'));

The subquery on lines 4 through 6 returns the primary key for a row in the ACTOR table where the actor_name is Chris Hemsworth. The subquery on lines 7 through 9 returns the primary key for a row in the FILM table where the film_name is Thor.

Write comprehensive test case scripts

You can write test cases in many programming languages. Ruby without Rails and Python are excellent choices but a bash shell script is simpler. The following demonstrates a bash shell script that calls the DDL and DML scripts before querying data from the tables.

The first line of any bash script starts the bash shell. The rest declare variables, assign values and process input parameters. This setup.sh bash script takes three input parameters and assigns them to three local variables. Then, the setup.sh bash script creates two arrays and assigns file names to each array. Subsequent for-loops run the files in sequential order. After running the SQL DDL and DML files, the script calls two diagnostic queries and redirects their output to the console.

The following is the setup.sh bash script, and it coordinates the complete test case:

  1 #!/usr/bin/bash

  2

  3 # Assign user and password

  4 username="${1}"

  5 password="${2}"

  6 dir="${3}"

  7

  8 echo "User name:" ${username}

  9 echo "Password: " ${password}

 10 echo "Directory:" ${dir}

 11

 12 # Define an array.

 13 declare -a cmd_ddl

 14 declare -a cmd_dml

 15

 16 # Assign elements to a DDL array.

 17 cmd_ddl[0]="actor_ddl.sql"

 18 cmd_ddl[1]="film_ddl.sql"

 19 cmd_ddl[2]="movie_ddl.sql"

 20

 21 # Assign elements to a DDL array.

 22 cmd_dml[0]="actor_dml.sql"

 23 cmd_dml[1]="film_dml.sql"

 24 cmd_dml[2]="movie_dml.sql"

 25

 26 # Call the array elements.

 27 for i in ${cmd_ddlStar}; do

 28   sqlplus -s ${username}/${password} @${dir}/${i} > /dev/null

 29 done

 30

 31 # Call the array elements.

 32 for i in ${cmd_dmlStar}; do

 33   sqlplus -s ${username}/${password} @${dir}/${i} > /dev/null

 34 done

 35

 36 # Connect and pipe the query result minus errors to the loop.

 37 sqlplus -s ${username}/${password} @${dir}/tables.sql 2>/dev/null |

 38

 39 # Read through the piped result until it's empty.

 40 while IFS='\n' read table_name; do

 41   echo $table_name

 42 done

 43

 44 # Connect and pipe the query result minus errors to the loop.

 45 sqlplus -s ${username}/${password} @${dir}/results.sql 2>/dev/null |

 46

 47 # Read through the piped result until it's empty.

 48 while IFS='\n' read actor_name; do

 49   echo $actor_name

 50 done

The setup.sh program assigns the three input variables on lines 4 through 6. Then, lines 8 through 9 print the contents of the three local variables.

The cmd_ddl and cmd_dml arrays are defined on lines 13 through 14. The program assigns values to the cmd_ddl array on lines 17 through 19 and to the cmd_dml array on lines 22 through 24. The for-loop on lines 27 through 29 calls and runs the DDL scripts, and the for-loop on lines 32 through 34 runs the DML seeding scripts.

Line 37 calls SQL*Plus in silent mode and runs a single query, which it redirects through a pipe in the setup.sh script to the local table_name variable. Lines 40 through 42 reads the output and uses the Internal Field Separator (IFS) to break the table_name output stream into rows of data. It effectively prints a list of table names from the database catalog, and uses a query of table name from the user_tables view.

Line 45 also calls SQL*Plus in silent mode and runs a single query. Like the previous example, it uses a pipe to redirect output on line 45. The for-loop on lines 48 through 50 processes the actor_name output into rows.

To shorten the command line entry, you can store the directory location into an environment variable, like:

export set DIR=/u01/app/oracle/product/custom/sql

You call the setup.sh script as follows:

./setup.sh sample sample $DIR

The script should echo the following to your console:

Table Name

------------------------------

ACTOR

FILM

MOVIE

 

Actors in Films

----------------------------------------

Chris Hemsworth, Thor

Chris Hemsworth, Thor: The Dark World

Chris Pine, Star Trek

Chris Pine, Star Trek into Darkness

Chris Pratt, Guardians of the Galaxy

Chris Pratt, Jurassic World

This article has shown you how to divide your work into smaller pieces. It also has shown you how you run a script to test your program code. I hope it helps you as you create business cases.