Unit Test Script
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, as qualified in the Week 1: SQL*Plus CLI Tutorial:
@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.