Archive for the ‘pl/sql’ Category
Design Database Triggers
Designing and implementing database triggers is always interesting and sometimes not easy. I believe most of the difficulty comes from not implementing the triggers in a way that lets you perform single use case testing. For example, a trigger typically fires as a result of an INSERT, UPDATE, or DELETE statement. That means you can’t test the trigger’s logic independently from the SQL statement.
This post shows you how to implement an Oracle Database trigger that ensures a last_name field always has a hyphen when it is composed of two surnames. It also shows you how to build debugging directly into the trigger with Oracle’s conditional compilation logic (covered in my Oracle Database 12c PL/SQL Programming book on pages 170-171) while writing the debug comments to a debug logging table.
The example works through the design in stages. To begin the process, you need to define a zeta table and zeta_s sequence (no magic in the table or sequence names).
-- Create the zeta demo table. CREATE TABLE zeta ( zeta_id NUMBER , last_name VARCHAR2(30)); -- Create the zeta_s demo sequence. CREATE SEQUENCE zeta_s; |
Next, you write a basic on insert row-level (or, row-by-row) trigger. The following white_space trigger only fires when the last_name column value contains a whitespace between two components of a last name.
The code follows below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /* || Create an on insert trigger to implement the desired || logic, which replaces a whitespace between two portions || of a last_name column. */ CREATE OR REPLACE TRIGGER white_space BEFORE INSERT ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END white_space; / |
You can now test the white_space trigger with these two INSERT statements:
-- Two test insert statements. INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Baron-Schwartz' ); INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Zeta Jones' ); |
After running the two INSERT statements, you can query the last_name from the zeta table and verify that there’s always a hyphen between the two components of the last name, like:
SELECT * FROM zeta; |
It should display:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones |
However, the business logic is violated when you run an UPDATE statement, like:
-- Update data and break the business rule. UPDATE zeta SET last_name = 'Zeta Jones' WHERE last_name = 'Zeta-Jones'; |
A fresh query like
SELECT * FROM zeta; |
Should display the following, which allowed an UPDATE statement to put in a non-conforming last name value:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta Jones |
You need to expand the role of your white_space trigger to prevent this undesired outcome by enabling it to fire on an insert or update event. You do that by adding ON UPDATE to line 8 below. The modified white_space trigger for both SQL events is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /* || Create an on insert or update trigger to implement the || desired logic, which replaces a whitespace between two || portions of a last_name column. */ CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END white_space; / |
Having made the change on line 8 above, you can now retest the white_space trigger with the following UPDATE statement. You should note that the WHERE clause uses a whitespace because the last UPDATE statement with the INSERT-only white_space trigger allowed its change.
UPDATE zeta SET last_name = 'Zeta-Jones' WHERE last_name = 'Zeta Jones'; |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta Jones |
The modified white_space trigger doesn’t let us capture debug information and it doesn’t let us see whether the SQL event is an INSERT or UPDATE statement. It also fails to differentiate between outcomes from an INSERT and UPDATE event.
You can fix this by:
- Creating a debug_log table that captures debugging information.
- Creating a debug_procedure to format diagnostic strings.
- Using the Data Manipulation Language (DML) Event Functions (covered in my Oracle Database 12c PL/SQL Programming book’s Table 12-3 on page 533) to track whether the event is an
INSERTorUPDATEstatement.
The three steps to make the trigger capable of different outcomes and debugging are:
- The following creates a debug_log table:
-- Create the debug_log table. CREATE TABLE debug_log ( message VARCHAR2(78));
- The following creates an a debug procedure:
-- Create a debug logging procedure. CREATE OR REPLACE PROCEDURE debug ( event VARCHAR2 := 'Unknown' , location VARCHAR2 , COLUMN VARCHAR2 ) IS /* Local message variable. */ lv_message VARCHAR2(78); /* Set procedure as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Build, insert, and commit message in log. */ lv_message := event || ' event at ' || location || ' on column [' || COLUMN || ']'; INSERT INTO debug_log ( message ) VALUES ( lv_message ); COMMIT; END; /
- The following creates an a replacement white_space trigger equipped with event tracking and conditional compilation debug calls to the debug_log table:
You actually need to change the session before compiling this trigger with the following command so that the conditional compilation instructions work:
ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';
Then, create the white_space trigger from the following code:
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
-- Create a debug logging procedure. CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.last_name' ); $END IF INSERTING THEN lv_event := 'Inserting'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after IF statement' , column_value => ':new.last_name' ); $END :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); ELSIF UPDATING THEN lv_event := 'Updating'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after ELSIF statement' , column_value => ':new.last_name' ); $END RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.last_name' ); $END END white_space; /
A new test case for the modified white_space trigger uses an INSERT and UPDATE statement, like:
INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Pinkett Smith' ); UPDATE zeta SET last_name = 'Pinkett Smith' WHERE last_name = 'Pinkett-Smith'; |
The UPDATE statement violates the business rule and the new white_space trigger throws an error when an attempt is made to update the last_name with two names separated by a whitespace. The UPDATE statement raises the following error stack:
UPDATE zeta * ERROR AT line 1: ORA-20001: Whitespace replaced WITH hyphen. ORA-06512: AT "STUDENT.WHITE_SPACE", line 31 ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE' |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones
3 Pinkett-Smith |
Unfortunately, there’s a lot of debugging clutter in the white_space trigger. The other downside is it requires testing from INSERT and UPDATE statements rather than simple anonymous block. You can fix that by doing two things:
- Remove the body of the trigger to an autonomous zeta_function.
- Put a logic router in the trigger with a call to the autonomous zeta_function.
Here’s the script to create the zeta_function:
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 | CREATE OR REPLACE FUNCTION zeta_function ( column_value VARCHAR2 , event VARCHAR2 ) RETURN VARCHAR2 IS /* Return value. */ lv_retval VARCHAR2(30) := column_value; /* Set function as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.column_value' ); $END /* Check if event is INSERT statement. */ IF event = 'INSERTING' THEN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => INITCAP(event) , location => 'after IF statement' , column_value => ':new.column_value' ); $END /* Replace a whitespace with a hyphen. */ lv_retval := REGEXP_REPLACE(column_value,' ','-',1,1); /* Check if event is UPDATE statement. */ ELSIF event = 'UPDATING' THEN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => INITCAP(event) , location => 'after ELSIF statement' , column_value => ':new.column_value' ); $END /* Raise error to state policy allows no changes. */ RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.column_value' ); $END /* Return modified column for insert or original column for update. */ RETURN lv_retval; END zeta_function; / |
The refactored white_space trigger follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Set evaluation event. */ IF INSERTING THEN lv_event := 'INSERTING'; ELSIF UPDATING THEN lv_event := 'UPDATING'; END IF; /* || Assign the result of the formatted string to the || new last_name value. */ :NEW.last_name := zeta_function( event => lv_event , column_value => :NEW.last_name); END white_space; / |
A new test case for the modified white_space trigger uses an INSERT and UPDATE statement with some new values.
INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Day Lewis' ); UPDATE zeta SET last_name = 'Day Lewis' WHERE last_name = 'Day-Lewis'; |
The UPDATE statement continues to violate the business rule and the modified white_space trigger throws a different error stack. The new error stack includes the zeta_function because that’s where you throw the error. It is caught and re-thrown by the white_space trigger.
UPDATE zeta * ERROR AT line 1: ORA-20001: Whitespace replaced WITH hyphen. ORA-06512: AT "STUDENT.ZETA_FUNCTION", line 47 ORA-06512: AT "STUDENT.WHITE_SPACE", line 13 ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE' |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones
3 Pinkett-Smith
4 Day-Lewis |
Now, you can query the debug_log table and see the debug messages that you captured from testing the INSERT and UPDATE statements. You get three messages from the INSERT statement test and only two from the UPDATE statement test.
MESSAGE ------------------------------------------------------------------ Unknown event at before IF statement on column [:new.last_name] Inserting event at after IF statement on column [:new.last_name] Unknown event at after END IF statement on column [:new.last_name] Unknown event at before IF statement on column [:new.last_name] Updating event at after ELSIF statement on column [:new.last_name] |
As always, I hope this helps people see new ways to solve problems.
PL/SQL Mimic Iterator
There’s no formal iterator in PL/SQL but you do have the ability of navigating a list or array with Oracle’s Collection API. For example, the following navigates a sparsely indexed collection from the lowest to the highest index value while skipping a missing index value:
DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Declare the collection. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Create a gap in the densely populated index. */ lv_list.DELETE(2); /* Mimic an iterator in the loop. */ CURRENT := lv_list.FIRST; WHILE NOT (CURRENT > lv_list.LAST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
The next one, navigates a sparsely indexed collection from the highest to the lowest index value while skipping a missing index value:
DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Declare the collection. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Create a gap in the densely populated index. */ lv_list.DELETE(2); /* Mimic an iterator in the loop. */ CURRENT := lv_list.LAST; WHILE NOT (CURRENT < lv_list.FIRST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.PRIOR(CURRENT); END LOOP; END; / |
However, the next example is the most valuable because it applies to a PL/SQL associative array indexed by string values. You should note that the string indexes are organized in ascending order and assigned in the execution section of the program. This differs from the earlier examples where the values are assigned by constructors in the declaration section.
There’s no need to delete an element from the associative array because the string-based indexes are already sparsely constructed. A densely populated character index sequence is possible but not very useful, which is probably why there aren’t any examples of it.
Moreover, the following example is how you navigate a dictionary, which is known as an associative array in Oracle parlance (special words to describe PL/SQL structures). Unfortunately, associative arrays lack any utilities like Python’s key() method for dictionaries.
DECLARE /* Create a local associative array type. */ TYPE list IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10); /* Define a variable of the associative array type. */ lv_list LIST; -- := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT VARCHAR2(5); BEGIN /* Assign values to an associative array (PL/SQL structure). */ lv_list('One') := 'Moe'; lv_list('Two') := 'Shemp'; lv_list('Three') := 'Larry'; lv_list('Four') := 'Curly'; /* Mimic iterator. */ CURRENT := lv_list.FIRST; dbms_output.put_line('Debug '||CURRENT); WHILE NOT (CURRENT < lv_list.LAST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
As always, I hope this example helps somebody solve a real world problem.
What Identifier?
It’s always interesting to see students find the little nuances that SQL*Plus can generate. One of the first things we cover is the concept of calling PL/SQL interactively versus through an embedded call. The easiest and first exercise simply uses an insecure call like:
sqlplus -s student/student @call.sql |
to the call.sql program:
SQL> DECLARE 2 lv_input VARCHAR2(20); 3 BEGIN 4 lv_input := '&1'; 5 dbms_output.put_line('['||lv_input||']'); 6 END; 7 / |
It prints the following to console:
Enter value for 1: machine old 4: lv_input := '&1'; new 4: lv_input := 'machine'; [machine] PL/SQL procedure successfully completed. |
Then, we change the '&1' parameter variable to '&mystery' and retest the program, which prints the following to the console:
Enter value for mystery: machine old 4: lv_input := '&mystery'; new 4: lv_input := 'machine'; [machine] PL/SQL procedure successfully completed. |
After showing a numeric and string input parameter, we remove the quotation from the lv_input input parameter and raise the following error:
Enter value for mystery: machine
old 4: lv_input := &mystery;
new 4: lv_input := machine;
lv_input := machine;
*
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored |
The point of the exercise is to spell out that the default input value is numeric and that if you pass a string it becomes an identifier in the scope of the program. So, we rewrite the call.sql program file by adding a machine variable, like:
SQL> DECLARE 2 lv_input VARCHAR2(20); 3 machine VARCHAR2(20) := 'Mystery Machine'; 4 BEGIN 5 lv_input := &mystery; 6 dbms_output.put_line('['||lv_input||']'); 7 END; 8 / |
It prints the following:
Enter value for mystery: machine old 5: lv_input := &mystery; new 5: lv_input := machine; [Mystery Machine] PL/SQL procedure successfully completed. |
The parameter name becomes an identifier and maps to the variable machine. That mapping means it prints the value of the machine variable.
While this is what we’d call a terminal use case, it is a fun way to illustrate an odd PL/SQL behavior. As always, I hope its interesting for those who read it.
PL/SQL Inheritance Failure
PL/SQL is a great programming language as far as it goes but it lacks true type inheritance for its collections. While you can create an object type and subtype, you can’t work with collections of those types the same way. PL/SQL object type inheritance, unlike the Java class hierarchy and parallel array class hierarchy, only supports a class hierarchy. Effectively, that means:
- You can pass a subtype as a call parameter, or argument, to a parent data type in a function, procedure, or method signature, but
- You can’t pass a collection of a subtype as a call parameter, or argument, to a collection of parent type in a function, procedure, or method signature.
The limitation occurs because collections have their own data type, which is fixed when you create them. Worse yet, because Oracle has never seen fit to fix their two underlying code trees (23 years and counting since Oracle 8i), you have two types of collections using two distinct C/C++ libraries. You define collections of Attribute Data Types (ATDs) when you create a collection of a standard scalar data type, like NUMBER, VARCHAR2, or DATE. You define collection of User-Defined Data Types (UTDs) when you create a collection of a SQL UDT or PL/SQL-only RECORD data type. The former uses one C/C++ library and the latter another.
Now, Oracle even make the differences between Java and PL/SQL more complex because it treats collections known as tables, really lists in most programming languages, differently than varrays, or arrays. You create a TABLE collection, or list, when you create a table of a scalar or UDT data type. There are two options when you create these object types, and they are:
- You create an empty collection with a no element constructor, which means you’ll need to allocate memory before assigning element values later in your program.
- You create a populated collection with a comma-delimited list of elements.
Both approaches give you a list of elements with a densely populated index. A “densely populated index” is Oracle’s jargon for how they characterize a 1-based sequence of integers without any gaps (e.g., 1, 2, 3, …). The initial construction works the same way whether you create a TABLE or VARRAY collection type. Unfortunately, after you’ve built the collection behaviors change. If you use Oracle’s Collection API to delete one or more items from a TABLE collection type, you create gaps in the index’s sequence of values. That means you must use special logic to navigate across a TABLE collection type to ensure it doesn’t fail when encountering a gap in the numeric sequence.
For example, here’s a FOR-LOOP without the logic to vouchsafe a uninterrupted set of sequence values incrementing by a counter of 1 element at a time:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Statically allocate memory and assign values to for elements. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); BEGIN /* Remove the second element, Shemp, from the collection of variable length strings. */ lv_list.DELETE(2); /* Loop through the target with a for loop, which depends on densely populated index values. */ FOR i IN 1..lv_list.COUNT LOOP dbms_output.put_line('['||lv_list(i)||']'); END LOOP; END; / |
The program fails when it tries to read the second element of the table collection, which was previously removed. It raises the following error message after print the first element of the table collection:
[Moe] DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 16 |
Conveniently, Oracle’s Collection API provides an EXISTS method that we can use to check for the presence of an index’s value. Modifying line 16 by wrapping it in an IF-statement fixes one problem but identifies another:
15 16 17 18 19 | FOR i IN 1..lv_list.COUNT LOOP IF lv_list.EXISTS(i) THEN dbms_output.put_line('['||lv_list(i)||']'); END IF; END LOOP; |
The program no longer fails on a missing index value, or index gap, but it returns fewer lines of output than you might expect.
That’s because the Oracle Collection API’s COUNT method returns the number of elements currently allocated in memory not the number of original elements. We learn that when we deleted the second element, Oracle deleted the memory allocated for it as well. This is the type of behavior you might expect for a singly linked list. It prints:
[Moe] [Larry] |
One more change is required to count past and to the highest index value. One line 15, change the COUNT method call to the LAST method call, which returns the highest index value.
15 16 17 18 19 | FOR i IN 1..lv_list.LAST LOOP IF lv_list.EXISTS(i) THEN dbms_output.put_line('['||lv_list(i)||']'); END IF; END LOOP; |
It now prints the three stooges we would expect to see:
[Moe] [Larry] [Curly] |
Realistically, a FOR-LOOP is not the best control structure for a collection. You should use a WHILE-LOOP and treat the incrementing value as an iterator rather than sequence index value. An iterator doesn’t worry about gaps in the sequence, it simply moves to the next element in the singly linked list. Here’s an example that uses the iterator approach with a WHILE-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 | DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Statically allocate memory and assign values to for elements. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Remove the second element, Shemp, from the collection of variable length strings. */ lv_list.DELETE(2); /* Loop through the target with a while loop, which doesn't depend on densely populated index values by setting the starting index value and increment as if with an iterator. */ CURRENT := lv_list.FIRST; WHILE NOT (CURRENT > lv_list.LAST) LOOP dbms_output.put_line('['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
The iterator approach prints the elements as:
[Moe] [Larry] [Curly] |
You can reverse the process with the following changes to lines 20-24:
20 21 22 23 24 | CURRENT := lv_list.LAST; WHILE NOT (CURRENT < lv_list.FIRST) LOOP dbms_output.put_line('['||lv_list(CURRENT)||']'); CURRENT := lv_list.PRIOR(CURRENT); END LOOP; |
It prints the list backwards:
[Curly] [Larry] [Moe] |
After covering the issues with sparsely populated, those with gaps in the sequence of indexes values, table collections, let’s examine how you must work around PL/SQL’s lack of a parallel array class hierarchy. The solution lies in combining two programming concepts:
- A function to pack the sparsely populated table collection into a densely populated one, and
- A package with overloaded functions that pack different table collections.
To develop the test case, let’s use an ADT collection because it’s the simplest to work with. The following creates a table collection of a thirty character long scalar string:
1 2 3 | CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(30); / |
The following pack function takes a table collection of the thirty character long scalar string, evaluates the string for missing elements, and packs the existing elements into a densely populated list:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE FUNCTION pack ( pv_list LIST ) RETURN list IS /* Declare a new list. */ lv_new LIST := list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END; / |
This anonymous block tests the pack function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE /* Declare a list value. */ lv_test LIST := list('Moe','Shemp','Larry','Curly'); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.COUNT LOOP dbms_output.put_line('['||lv_test(i)||']'); END LOOP; END; / |
It prints the expected three string values:
[Moe] [Larry] [Curly] |
Now, let’s expand the example to build an overloaded package. The first step requires building a base_t object type and a table collection of the object type, like:
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TYPE base_t IS OBJECT ( oid NUMBER ) INSTANTIABLE NOT FINAL; / CREATE OR REPLACE TYPE base_list IS TABLE OF base_t; / |
Next, you create a book_t subtype of the base_t object type and a book_list table collection of the book_t subtype, like:
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TYPE book_t UNDER base_t ( title VARCHAR2(30) , COST NUMBER); / CREATE OR REPLACE TYPE book_list IS TABLE OF book_t; / |
We can test the base_t and book_t default constructors with the following SQL*Plus formatting and SQL query:
COL oid FORMAT 999 COL title FORMAT A20 COL COST FORMAT 99.99 SELECT * FROM TABLE(book_list(book_t(1,'Neuromancer',15.30) ,book_t(2,'Count Zero',7.99) ,book_t(3,'Mona Lisa Overdrive',7.99) ,book_t(4,'Burning Chrome',8.89))); |
It prints the following output:
OID TITLE COST ---- -------------------- ------ 1 Neuromancer 15.30 2 Count Zero 7.99 3 Mona Lisa Overdrive 7.99 4 Burning Chrome 8.89 |
The following is an overloaded package specification:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE PACKAGE packer IS /* A simple ADT list of strings. */ FUNCTION pack ( pv_list LIST ) RETURN list; /* A UDT list of base objects. */ FUNCTION pack ( pv_list BASE_LIST ) RETURN base_list; /* A UDT list of subtype objects. */ FUNCTION pack ( pv_list BOOK_LIST ) RETURN book_list; END; / |
After you create the package specification, you need to provide the implementation. This is typical in any programming language that supports Interface Description Language (IDL). A package body provides the implementation for the package specification. The package body 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 49 50 51 52 53 54 55 56 | CREATE OR REPLACE PACKAGE BODY packer IS /* A simple ADT list of strings. */ FUNCTION pack ( pv_list LIST ) RETURN list IS /* Declare a new list. */ lv_new LIST := list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; /* A simple ADT list of strings. */ FUNCTION pack ( pv_list BASE_LIST ) RETURN base_list IS /* Declare a new list. */ lv_new BASE_LIST := base_list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; /* A simple ADT list of strings. */ FUNCTION pack ( pv_list BOOK_LIST ) RETURN book_list IS /* Declare a new list. */ lv_new BOOK_LIST := book_list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; END packer; / |
The test case for the base_list object type is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE lv_test BASE_LIST := base_list(base_t(1),base_t(2) ,base_t(3),base_t(4)); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := packer.pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.LAST LOOP dbms_output.put_line('['||lv_test(i).oid||']'); END LOOP; END; / |
It prints the following output:
[1] [3] [4] |
The test case for the book_list object type is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DECLARE lv_test BOOK_LIST := book_list(book_t(1,'Neuromancer',15.30) ,book_t(2,'Count Zero',7.99) ,book_t(3,'Mona Lisa Overdrive',7.99) ,book_t(4,'Burning Chrome',8.89)); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := packer.pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.LAST LOOP dbms_output.put_line( '['||lv_test(i).oid||']' ||'['||lv_test(i).title||']' ||'['||lv_test(i).COST||']'); END LOOP; END; / |
It prints the following output:
[1][Neuromancer][15.3] [3][Mona Lisa Overdrive][7.99] [4][Burning Chrome][8.89] |
In conclusion, you would not have to write overloaded methods for every list if PL/SQL supported class hierarchy and parallel array class hierarchy like Java. Unfortunately, it doesn’t and likely won’t in the future. You can pack table collections as a safety measure when they’re passed as parameters to other functions, procedures, or methods with the code above.
As always, I hope this helps those looking for a solution.
PL/SQL Coupled Loops
The purpose of this example shows you how to navigate a list with a sparsely populated index. This can occur when one element has been removed after the list was initialized. Unlike Oracle’s VARRAY (array), removing an element from a TABLE or list does not re-index the elements of the list.
This example also shows you how to coupled lists. The outer loop increments, notwithstanding the gap in index values, while the inner loop decrements. The upper range of the inner loop is set by the index value of the outer loop.
The example program uses an abbreviated version of the Twelve Days of Christmas, and I’ve tried to put teaching notes throughout the example file.
DECLARE /* Create a single column collection that is a list strings less than 8 characters in length and another of strings less than 20 characters in length. */ TYPE DAY IS TABLE OF VARCHAR2(8); TYPE verse IS TABLE OF VARCHAR2(20); /* Create variables that use the user-defined types: || ================================================= || 1. We give the variable a name of lv_day and lv_verse. || 2. We assign a user-defined ADT (Attribute Data Type) collection. || 3. We assign a list of value to the constructor of the list, which || allocates memory for each item in the comma-delimited list of || string. */ lv_day DAY := DAY('first','second','third','fourth','fifth'); lv_verse VERSE := verse('Partridge','Turtle Doves','French Hen' ,'Calling Birds','Gold Rings'); BEGIN /* || Remove an element from each of the two lists, which makes the two || lists sparsely indexed. A sparsely indexed list has gaps in the || sequential index of the list. */ lv_day.DELETE(3); /* || Loop through the list of days: || ===================================================j || 1. A list created by a comma-delimited list is densely populated, || which means it has no gaps in the sequence of indexes. || 2. A list created by any means that is subsequently accessed || and has one or more items removed is sparsely populated, || which means it may have gaps in the sequence of indexes. || 3. A FOR loop anticipates densely populated indexes and fails || when trying to read a missing index, which is why you should || use an IF statement to check for the element of a list before || accessing it. || 4. A COUNT method returns the number of elements allocated memory || in a list of values and the LAST method returns the highest || index value. The index value is alway an integer for user-defined || ADT (Attribute Data Type) collections, but may be a string for || an associative array or a PL/SQL list indexed by a string. || 5. Removing an element from a list does not change the other || index values but does if you create an array (or varray), which || means COUNT OR LAST may cause the same type of error for a list || with a missing element. */ FOR i IN 1..lv_day.LAST LOOP /* || Verify the index is valid. || ==================================================== || You check whether the element is present in the || list. */ IF lv_day.EXISTS(i) THEN /* Print the beginning of the stanza. */ dbms_output.put_line('On the ['||lv_day(i)||'] of Christmas ...'); /* Print the song. */ FOR j IN REVERSE 1..i LOOP /* Check if the day exists. */ IF lv_verse.EXISTS(j) THEN /* All but first and last verses. */ IF j > 1 THEN dbms_output.put_line('- ['||lv_verse(j)||']'); /* The last verse. */ ELSIF i = j THEN dbms_output.put_line('- A ['||lv_verse(j)||']'||CHR(10)); /* Last verse. */ ELSE dbms_output.put_line('and a ['||lv_verse(j)||']'||CHR(10)); END IF; END IF; END LOOP; ELSE CONTINUE; END IF; END LOOP; END; / |
As always, I hope it helps you solve problems in the real world.
Create Student User
It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.
Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.
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 | DECLARE /* Control variable. */ container BOOLEAN := FALSE; /* Weakly structured system reference cursor. */ container_sql SYS_REFCURSOR; /* Constant required for pre-container databases to avoid a a compile time error. */ sql_statement CONSTANT VARCHAR2(50) := 'SELECT cdb FROM v$database WHERE cdb = ''YES'''; BEGIN /* Check if the current user is the superuser. */ FOR i IN (SELECT USER FROM dual) LOOP /* Perform tasks as superuser. */ IF i.USER = 'SYSTEM' THEN /* Check for a container-enabled column, which enables this to work in both pre-container Oracle databases, like 10g and 11g. */ FOR j IN (SELECT DISTINCT column_name FROM dba_tab_columns WHERE column_name = 'CDB') LOOP /* Check for a container database, set control variable and exit when found. */ OPEN container_sql FOR sql_statement; LOOP container := TRUE; EXIT WHEN container_sql%FOUND; END LOOP; END LOOP; /* Conditionally drop existing user and role. */ IF container THEN /* Conditionally drop a container user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'C##STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER c##student CASCADE'; END LOOP; /* Conditionally rop the container c##studentrole role. */ FOR j IN (SELECT ROLE FROM dba_roles WHERE ROLE = 'C##STUDENTROLE') LOOP EXECUTE IMMEDIATE 'DROP ROLE c##studentrole'; END LOOP; /* Create a container user with 200 MB of space. */ EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Create a container role. */ EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL'; /* Grant privileges to a container user. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO c##studentrole'; /* Grant role to user. */ EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student'; ELSE /* Conditonally drop the non-container database user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER student CASCADE'; END LOOP; /* Create the student database. */ EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Grant necessary privileges to the student database. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO student'; END IF; ELSE /* Print an message that the user lacks privilegs. */ dbms_output.put_line('You must be the SYSTEM user to drop and create a user.'); END IF; END LOOP; END; / |
As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.
Postgres Print Debug Notes
A student asked how you print output from PL/pgSQL blocks. The student wanted to know if there was something like the following in Oracle’s PL/SQL programming language:
dbms_output.put_line('some string'); |
or, in Java programming the:
System.out.println("some string"); |
The RAISE NOTICE is the equivalent to these in Postgres PL/pgSQL, as shown in the following anonymous block:
do $$ BEGIN raise notice 'Hello World!'; END; $$; |
It prints:
NOTICE: Hello World! |
You can write a hello_world function as a named PL/pgSQL block:
CREATE FUNCTION hello_world() RETURNS text AS $$ DECLARE output VARCHAR(20); BEGIN /* Query the string into a local variable. */ SELECT 'Hello World!' INTO output; /* Return the output text variable. */ RETURN output; END $$ LANGUAGE plpgsql; |
You can call it with the following:
SELECT hello_world(); |
It prints:
hello_world -------------- Hello World! (1 row) |
Here’s a full test case with stored procedure in PL/pgSQL:
-- Drop the msg table. DROP TABLE msg; -- Create the msg table. CREATE TABLE msg ( comment VARCHAR(400) ); -- Transaction Management Example. DROP PROCEDURE IF EXISTS testing ( IN pv_one VARCHAR(30) , IN pv_two VARCHAR(10)); -- Transaction Management Example. CREATE OR REPLACE PROCEDURE testing ( IN pv_one VARCHAR(30) , IN pv_two VARCHAR(10)) AS $$ DECLARE /* Declare error handling variables. */ err_num TEXT; err_msg INTEGER; BEGIN /* Log actdual parameter values. */ INSERT INTO msg VALUES (pv_one||'.'||pv_two); EXCEPTION WHEN OTHERS THEN err_num := SQLSTATE; err_msg := SUBSTR(SQLERRM,1,100); RAISE NOTICE 'Trapped Error: %', err_msg; END $$ LANGUAGE plpgsql; do $$ DECLARE lv_one VARCHAR(30) := 'INDIVIDUAL'; lv_two VARCHAR(19) := 'R11-514-34'; BEGIN RAISE NOTICE '[%]', lv_one; RAISE NOTICE '[%]', lv_two; CALL testing( pv_one := lv_one, pv_two := lv_two ); END $$; -- Query any logged results. SELECT * FROM msg; |
It prints:
DROP TABLE
CREATE TABLE
DROP PROCEDURE
CREATE PROCEDURE
psql:fixed.sql:61: NOTICE: [INDIVIDUAL]
psql:fixed.sql:61: NOTICE: [R11-514-34]
DO
comment
-----------------------
INDIVIDUAL.R11-514-34
(1 row) |
I hope this helps those looking for a solution.
Misleading ORA- Message
Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:
BEGIN * ERROR AT line 1: ORA-22288: FILE OR LOB operation failed ORA-06512: AT "STUDENT.LOAD_CLOB_FROM_FILE", line 71 ORA-06512: AT line 11 |
Oddly enough, it was simple to identify generally. It failed on a call to the DBMS_LOB.LOADCLOBFROMFILE procedure. However, the better question is why did it fail because the virtual directory resolved and the permissions worked.
The first test was to try another file, which worked perfectly with the same code. That meant it had to be something with the physical file. I took a look and sure enough I found a character set problem, like the following:
… he reveals that the Nazgûl, or Ringwraiths, have left Mordor to capture the Ring and kill whoever carries it.
and,
The group flees to the elvish realm of Lothlórien …
The “û” and “ó” characters were incompatible with the default NLS_LANG setting of the database and a CLOB limits the use of non-standard character sets. It’s ashamed that Oracle didn’t through a character set error, which would have expedited resolution of the problem.
As always, I hope this helps those looking for solutions.
Logging Triggers
Oracle Logging Trigger Results
This article demonstrates how you can write log files from triggers on different tables to the same logging table. This approach leverages Oracle’s object types and column substitutability features. It also eliminates the requirement to create a unique logging table for each logging trigger. The trick to accomplishing this requires mastering two skills.
The first skill requires you to learn how to create user-defined types (UDTs) and subtypes. The UDT stores the elements common to all logging data, and the UDT subtype stores the unique column values of individual tables. The second skill requires you to learn how to create a logging table that uses a base UDT as a column type, and to learn how to insert new data into and query subtype data from a UDT subtype.
You will learn both skills in this article. If you’re new to database triggers and Oracle’s object types, I’d recommend you check out my earlier “Critical and Non-critical Triggers” and “Object Types and Column Substitutability” articles.
The article works through the steps in four parts. You create:
- Five tables and sequences, and one UDT base type and two subtypes that map to the specific tables
- A reusable autonomous stored procedure
- Two data manipulation language (DML) triggers
- A test case with standalone PL/SQL blocks that query the data
This article uses small headers to organize the parts. After creating and testing the parts, there are some observations and suggestions at the end of the article.
Creating Tables, Sequences, and Types
You create five tables because of foreign key dependencies. The application_user table supports the use of who-audit columns. Who-audit columns document the user who creates and last updates every row of data. Who-audit columns must link to an access control list (ACL), which are typically a list of user names and their encrypted password keys.
The following creates the ACL table and sequence:
SQL> CREATE TABLE application_user 2 ( application_user_id NUMBER CONSTRAINT app_user_pk PRIMARY KEY 3 , application_user_name VARCHAR2(30) CONSTRAINT app_user_nn1 NOT NULL 4 , created_by NUMBER CONSTRAINT app_user_nn2 NOT NULL 5 , creation_date DATE CONSTRAINT app_user_nn3 NOT NULL 6 , last_updated_by NUMBER CONSTRAINT app_user_nn4 NOT NULL 7 , last_update_date DATE CONSTRAINT app_user_nn5 NOT NULL 8 , CONSTRAINT app_user_fk1 FOREIGN KEY(created_by) 9 REFERENCES application_user(app_user_id) 10 , CONSTRAINT app_user_fk2 FOREIGN KEY(last_updated_by) 11 REFERENCES application_user(app_user_id)); SQL> CREATE SEQUENCE application_user_seq; |
After you create the application_user table and application_user_seq sequence, you need to insert one row. The row let’s you validate the created_by and last_updated_by who-audit columns.
The following creates the mpaa table and mpaa_seq sequence:
SQL> CREATE TABLE mpaa 2 ( mpaa_id NUMBER CONSTRAINT mpaa_pk PRIMARY KEY 3 , rating_code VARCHAR2(5) CONSTRAINT mpaa_nn1 NOT NULL 4 , rating_name VARCHAR2(30) CONSTRAINT mpaa_nn2 NOT NULL 5 , rating_desc VARCHAR2(180) CONSTRAINT mpaa_nn3 NOT NULL 6 , created_by NUMBER CONSTRAINT mpaa_nn4 NOT NULL 7 , creation_date DATE CONSTRAINT mpaa_nn5 NOT NULL 8 , last_updated_by NUMBER CONSTRAINT mpaa_nn6 NOT NULL 9 , last_update_date DATE CONSTRAINT mpaa_nn7 NOT NULL 10 , CONSTRAINT mpaa_fk1 FOREIGN KEY(created_by) 11 REFERENCES application_user(application_user_id) 12 , CONSTRAINT mpaa_fk2 FOREIGN KEY(last_updated_by) 13 REFERENCES application_user(application_user_id)); SQL> CREATE SEQUENCE mpaa_seq; |
The mpaa table supports film ratings for the film table. The film table’s mpaa_id column holds foreign key values that reference the mpaa table. The film and employee tables are the principle testing tables for the stored procedure, triggers, and trigger event logging.
The following creates the film table and film_seq sequence:
SQL> CREATE TABLE film 2 ( film_id NUMBER CONSTRAINT film_pk PRIMARY KEY 3 , film_name VARCHAR2(40) CONSTRAINT film_nn1 NOT NULL 4 , release_date DATE CONSTRAINT film_nn2 NOT NULL 5 , mpaa_id NUMBER CONSTRAINT film_nn3 NOT NULL 6 , created_by NUMBER CONSTRAINT film_nn4 NOT NULL 7 , creation_date DATE CONSTRAINT film_nn5 NOT NULL 8 , last_updated_by NUMBER CONSTRAINT film_nn6 NOT NULL 9 , last_update_date DATE CONSTRAINT film_nn7 NOT NULL 10 , CONSTRAINT film_fk1 FOREIGN KEY(created_by) 11 REFERENCES application_user(application_user_id) 12 , CONSTRAINT film_fk2 FOREIGN KEY(last_updated_by) 13 REFERENCES application_user(application_user_id) 14 , CONSTRAINT film_fk3 FOREIGN KEY (mpaa_id) 15 REFERENCES mpaa (mpaa_id)); SQL> CREATE SEQUENCE film_seq; |
The following creates the employee table and employee_seq sequence:
SQL> CREATE TABLE employee 2 ( employee_id NUMBER 3 , employee_number VARCHAR2(10) 4 , first_name VARCHAR2(20) CONSTRAINT employee_nn1 NOT NULL 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) CONSTRAINT employee_nn2 NOT NULL 7 , created_by NUMBER CONSTRAINT employee_nn3 NOT NULL 8 , creation_date DATE CONSTRAINT employee_nn5 NOT NULL 9 , last_updated_by NUMBER CONSTRAINT employee_nn6 NOT NULL 10 , last_update_date DATE CONSTRAINT employee_nn7 NOT NULL 11 , CONSTRAINT employee_pk PRIMARY KEY (employee_id) 12 , CONSTRAINT employee_fk1 FOREIGN KEY (created_by) 13 REFERENCES application_user (application_user_id) 14 , CONSTRAINT employee_fk2 FOREIGN KEY (last_updated_by) 15 REFERENCES application_user (application_user_id)); SQL> DROP SEQUENCE employee_seq; |
You should populate some data in the application_user, mpaa, film, and employee tables. This testing ensures the interdependencies work.
Before you create the trigger_log table, you need to create three UDTs. The base_t object type requires you create a base_t object type and implement a base_t object body.
The following creates the base_t object type:
SQL> CREATE OR REPLACE 2 TYPE base_t IS OBJECT 3 ( oname VARCHAR2(30) 4 , CONSTRUCTOR FUNCTION base_t 5 RETURN SELF AS RESULT 6 , MEMBER FUNCTION get_oname RETURN VARCHAR2 7 , MEMBER PROCEDURE set_oname (oname VARCHAR2) 8 , MEMBER FUNCTION to_string RETURN VARCHAR2) 9 INSTANTIABLE NOT FINAL; 10 / |
The following creates the base_t object body:
SQL> CREATE OR REPLACE 2 TYPE BODY base_t IS 3 /* A default constructor w/o formal parameters. */ 4 CONSTRUCTOR FUNCTION base_t 5 RETURN SELF AS RESULT IS 6 BEGIN 7 self.oname := 'BASE_T'; 8 RETURN; 9 END; 10 /* An accessor, or getter, method. */ 11 MEMBER FUNCTION get_oname RETURN VARCHAR2 IS 12 BEGIN 13 RETURN self.oname; 14 END get_oname; 15 /* A mutator, or setter, method. */ 16 MEMBER PROCEDURE set_oname 17 ( oname VARCHAR2 ) IS 18 BEGIN 19 self.oname := oname; 20 END set_oname; 21 /* A to_string conversion method. */ 22 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 23 BEGIN 24 RETURN self.oname; 25 END to_string; 26 END; 27 / |
Lines 4 through 9 implements a no-argument constructor that automatically assigns a literal value to the oname field on line 7. This type of constructor lets you create an instance of the base_t object type without providing an oname. Lines 11 through 14 implements a getter for the oname field, and lines 16 through 20 implements a setter for the oname field.
Lines 22 through 26 implements a to_string function that prints the oname field value. The to_string function also provides a convenient way to test the object type of object instance stored in tables, as you will see later in this article.
You can now create the UDT subtypes for the employee and film tables. The base_t name represents the base type or a super type. The subtypes for the employee and film tables use the more conventional _obj suffix.
The following creates the employee_obj UDT subtype:
SQL> CREATE OR REPLACE 2 TYPE employee_obj UNDER base_t 3 ( employee_id NUMBER 4 , employee_number VARCHAR2(10) 5 , first_name VARCHAR2(20) 6 , middle_name VARCHAR2(20) 7 , last_name VARCHAR2(20) 8 , created_by NUMBER 9 , creation_date DATE 10 , last_updated_by NUMBER 11 , last_update_date DATE); 12 / |
The following creates the film_obj UDT subtype:
SQL> CREATE OR REPLACE 2 TYPE film_obj UNDER base_t 3 ( film_id NUMBER 4 , film_name VARCHAR2(40) 5 , release_date DATE 6 , mpaa_id NUMBER 7 , created_by NUMBER 8 , creation_date DATE 9 , last_updated_by NUMBER 10 , last_update_date DATE); 11 / |
After creating the base_t UDT and the employee_obj and film_obj subtypes, you can create the trigger_log table. The following creates the trigger_log table and trigger_log_s sequence:
SQL> CREATE TABLE trigger_log 2 ( trigger_log_id NUMBER 3 , table_name VARCHAR2(30) 4 , trigger_event VARCHAR2(6) 5 , transaction_status VARCHAR2(9) 6 , old_instance BASE_T 7 , new_instance BASE_T ); |
The surrogate key for the table is the trigger_log_id column. The composite key of the table_name, trigger_event, and transaction_status columns define the natural key for table. The old_instance and new_instance columns hold respectively the values for any table before and after the DML event.
Autonomous Procedure
You have a 32,000-byte limit on the size of database triggers. Also, you have a limit on the scope of database triggers. A database trigger must run in the same context as the DML event, which means a trigger can’t write a log file when it raises an exception. You can write a log file when the trigger raises an exception by calling a procedure that runs as an anonymous transaction.
The following implements anonymous-transaction procedure:
SQL> CREATE OR REPLACE 2 PROCEDURE log_trigger_result 3 ( pv_table_name VARCHAR2 4 , pv_trigger_event VARCHAR2 5 , pv_transaction_status VARCHAR2 6 , pv_old_instance BASE_T 7 , pv_new_instance BASE_T ) IS 8 9 /* Set precompiler directive to run in a separate context. */ 10 PRAGMA AUTONOMOUS_TRANSACTION; 11 BEGIN 12 /* Write to the log table. */ 13 INSERT INTO trigger_log 14 ( trigger_log_id 15 , table_name 16 , trigger_event 17 , transaction_status 18 , old_instance 19 , new_instance ) 20 VALUES 21 ( trigger_log_s.NEXTVAL 22 , pv_table_name 23 , pv_trigger_event 24 , pv_transaction_status 25 , pv_old_instance 26 , pv_new_instance ); 27 28 /* Commit the autonmous transaction. */ 29 COMMIT; 30 END log_trigger_result; 31 / |
Lines 6 and 7 uses a base_t UDT as a parameter type, which means it accepts a base_t type or any subtype. Line 10 set a pre-compiler directive that enables the log_trigger_result procedure to run in an independent thread of execution.
Autonomous Procedure
The INSERT statement designates two base_t columns on lines 18 and 19, and then it passes the two base_t parameters in the VALUES clause. Line 29 commits the record into the trigger_log table.
The following implements an INSERT or UPDATE event trigger on the employee table:
SQL> CREATE OR REPLACE TRIGGER employee_t1 2 BEFORE INSERT OR UPDATE OF last_name ON employee 3 FOR EACH ROW 4 WHEN (REGEXP_LIKE(NEW.last_name,' ')) 5 DECLARE 6 /* DML event label. */ 7 lv_employee_event VARCHAR2(6); 8 lv_transaction_status VARCHAR2(9) := 'REJECTED'; 9 10 /* Declare exception. */ 11 e EXCEPTION; 12 PRAGMA EXCEPTION_INIT(e,-20001); 13 BEGIN 14 /* Check for an event and assign event value. */ 15 IF INSERTING THEN 16 /* Check for a empty image_id primary key column value, 17 and assign the next sequence value when it is missing. */ 18 IF :NEW.employee_id IS NULL THEN 19 SELECT employee_seq.NEXTVAL 20 INTO :NEW.employee_id 21 FROM dual; 22 END IF; 23 :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); 24 lv_transaction_status := 'PROCESSED'; 25 lv_employee_event := 'INSERT'; 26 ELSE 27 lv_employee_event := 'UPDATE'; 28 END IF; 29 30 /* Log the details captured by an insert or update. */ 31 log_trigger_result 32 ( pv_table_name => 'EMPLOYEE' 33 , pv_trigger_event => lv_employee_event 34 , pv_transaction_status => lv_transaction_status 35 , pv_new_instance => 36 employee_obj( 37 oname => 'EMPLOYEE_OBJ' 38 , employee_id => :old.employee_id 39 , employee_number => :old.employee_number 40 , first_name => :old.first_name 41 , middle_name => :old.middle_name 42 , last_name => :old.last_name 43 , created_by => :old.created_by 44 , creation_date => :old.creation_date 45 , last_updated_by => :old.last_updated_by 46 , last_update_date => :old.last_update_date ) 47 , pv_old_instance => 48 employee_obj( 49 oname => 'EMPLOYEE_OBJ' 50 , employee_id => :NEW.employee_id 51 , employee_number => :NEW.employee_number 52 , first_name => :NEW.first_name 53 , middle_name => :NEW.middle_name 54 , last_name => :NEW.last_name 55 , created_by => :NEW.created_by 56 , creation_date => :NEW.creation_date 57 , last_updated_by => :NEW.last_updated_by 58 , last_update_date => :NEW.last_update_date )); 59 60 /* Throw exception. */ 61 IF UPDATING THEN 62 RAISE_APPLICATION_ERROR(-20001 63 ,'No two-part last names without a hyphen.'); 64 END IF; 65 66 EXCEPTION 67 /* Capture an exception. */ 68 WHEN e THEN 69 ROLLBACK; 70 dbms_output.put_line('[Trigger Event: '||lv_employee_event||']'); 71 dbms_output.put_line(SQLERRM); 72 WHEN OTHERS THEN 73 dbms_output.put_line(SQLERRM); 74 END; 75 / |
Line 8 sets the lv_transaction_status to REJECTED by default. Line 15 checks for an INSERT statement as the triggering event. It sets the lv_transaction_status to PROCESSED and sets the lv_employee_event to INSERT on lines 24 and 25. An UPDATE statement sets the lv_employee_event variable to UPDATE on line 27.
Lines 35 through 46 create an instance of the employee_obj as the old part of the INSERT statement. It should always be a null value for an INSERT statement. Lines 47 through 58 create an instance of the employee_obj as the new part of the INSERT statement.
The following implements an INSERT or UPDATE event trigger on the film table:
SQL> CREATE OR REPLACE TRIGGER film_t1 2 BEFORE INSERT OR UPDATE OF film_name ON film 3 FOR EACH ROW 4 DECLARE 5 /* DML event label. */ 6 lv_trigger_event VARCHAR2(6); 7 lv_transaction_status VARCHAR2(9) := 'REJECTED'; 8 9 /* Declare exception. */ 10 e EXCEPTION; 11 PRAGMA EXCEPTION_INIT(e,-20001); 12 BEGIN 13 /* Check for an event and assign event value. */ 14 IF INSERTING THEN 15 /* Check for a empty image_id primary key column value, 16 and assign the next sequence value when it is missing. */ 17 IF :NEW.film_id IS NULL THEN 18 SELECT film_seq.NEXTVAL 19 INTO :NEW.film_id 20 FROM dual; 21 END IF; 22 lv_trigger_event := 'INSERT'; 23 lv_transaction_status := 'PROCESSED'; 24 ELSIF UPDATING THEN 25 lv_trigger_event := 'UPDATE'; 26 END IF; 27 28 /* Log the details captured by an insert or update. */ 29 log_trigger_result 30 ( pv_table_name => 'FILM' 31 , pv_trigger_event => lv_trigger_event 32 , pv_transaction_status => lv_transaction_status 33 , pv_new_instance => 34 film_obj( 35 oname => 'FILM_OBJ' 36 , film_id => :old.film_id 37 , film_name => :old.film_name 38 , release_date => :old.release_date 39 , mpaa_id => :old.mpaa_id 40 , created_by => :old.created_by 41 , creation_date => :old.creation_date 42 , last_updated_by => :old.last_updated_by 43 , last_update_date => :old.last_update_date ) 44 , pv_old_instance => 45 film_obj( 46 oname => 'FILM_OBJ' 47 , film_id => :NEW.film_id 48 , film_name => :NEW.film_name 49 , release_date => :NEW.release_date 50 , mpaa_id => :NEW.mpaa_id 51 , created_by => :NEW.created_by 52 , creation_date => :NEW.creation_date 53 , last_updated_by => :NEW.last_updated_by 54 , last_update_date => :NEW.last_update_date )); 55 56 /* Throw exception. */ 57 IF UPDATING THEN 58 RAISE_APPLICATION_ERROR(-20001,'Film names not updateable.'); 59 END IF; 60 61 EXCEPTION 62 /* Capture an exception. */ 63 WHEN e THEN 64 ROLLBACK; 65 dbms_output.put_line('[Trigger Event: '||lv_trigger_event||']'); 66 dbms_output.put_line(SQLERRM); 67 WHEN OTHERS THEN 68 dbms_output.put_line(SQLERRM); 69 END; 70 / |
The film_t1 trigger does much the same thing as the employee_t1 trigger. The difference occurs in the INSERT statement. The film_t1 trigger constructs an old and new film_obj instances to the autonomous procedure.
You use INSERT and UPDATE statements as test cases for the complete model. The INSERT statement would look like the following:
SQL> INSERT INTO employee 2 ( employee_id 3 , employee_number 4 , first_name 5 , last_name 6 , created_by 7 , creation_date 8 , last_updated_by 9 , last_update_date ) 10 VALUES 11 ( employee_seq.NEXTVAL 12 ,'B98765-678' 13 ,'Catherine' 14 ,'Zeta Jones' 15 , 1 16 , TRUNC(SYSDATE) 17 , 1 18 , TRUNC(SYSDATE)); |
The INSERT statements should complete without error, but the UPDATE statement should raise an error. You can use the following UPDATE statement:
SQL> UPDATE employee 2 SET employee_number = 'B98765-678' 3 , first_name = 'Catherine' 4 , last_name = 'Zeta Jones' 5 , created_by = 1 6 , creation_date = TRUNC(SYSDATE) 7 , last_updated_by = 1 8 , last_update_date = TRUNC(SYSDATE) 9 WHERE first_name = 'Catherine' 10 AND middle_name IS NULL 11 AND last_name = 'Zeta-Jones'; |
It throws the following exception:
UPDATE employee
*
ERROR at line 1:
ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "STUDENT.EMPLOYEE_T1", line 64
ORA-20001: No two-part last names without a hyphen.
ORA-04088: error during execution of trigger 'STUDENT.EMPLOYEE_T1' |
An INSERT statement lets you test the film table, and UPDATE statement lets you test the film table. The film_t allows the INSERT statement but raises an exception with an UPDATE statement.
Query Data with Standalone PL/SQL
You have the ability to query the results with PL/SQL. The following anonymous block lets you print the results of the trigger architecture for employee_obj records.
SQL> DECLARE 2 /* Declare a cursor with subcursors. */ 3 CURSOR c IS 4 SELECT trigger_log_id 5 , table_name 6 , trigger_event 7 , transaction_status 8 , TREAT(new_instance AS employee_obj) 9 , TREAT(old_instance AS employee_obj) 10 FROM trigger_log 11 WHERE table_name = 'EMPLOYEE'; 12 13 /* Declare scalar variables. */ 14 lv_trigger_log_id NUMBER; 15 lv_table_name VARCHAR2(30); 16 lv_trigger_event VARCHAR2(6); 17 lv_transaction_status VARCHAR2(9); 18 19 /* Declare UDT variables. */ 20 lv_old_record EMPLOYEE_OBJ; 21 lv_new_record EMPLOYEE_OBJ; 22 23 BEGIN 24 /* Open base cursor and fetch records until none are found. */ 25 OPEN c; 26 LOOP 27 FETCH c 28 INTO lv_trigger_log_id 29 , lv_table_name 30 , lv_trigger_event 31 , lv_transaction_status 32 , lv_old_record 33 , lv_new_record; 34 EXIT WHEN c%NOTFOUND; 35 36 dbms_output.put_line('========================================'); 37 dbms_output.put_line('Trigger_Log_ID [Row] : ' 38 || lv_trigger_log_id); 39 dbms_output.put_line('Table_Name [Row] : ' 40 || lv_table_name); 41 dbms_output.put_line('Table_Name [Row] : ' 42 || lv_trigger_event); 43 dbms_output.put_line('Transaction [Row] : ' 44 || lv_transaction_status); 45 dbms_output.put_line( 46 '----------------------------------------'); 47 dbms_output.put_line('OName [Old] : ' 48 || lv_old_record.oname); 49 dbms_output.put_line('Employee_ID [Old] : ' 50 || lv_old_record.employee_id); 51 dbms_output.put_line('Employee_Number [Old] : ' 52 || lv_old_record.employee_number); 53 dbms_output.put_line('First_Name [Old] : ' 54 || lv_old_record.first_name); 55 dbms_output.put_line('Middle_Name [Old] : ' 56 || lv_old_record.middle_name); 57 dbms_output.put_line('Last_Name [Old] : ' 58 || lv_old_record.last_name); 59 dbms_output.put_line( 60 '----------------------------------------'); 61 dbms_output.put_line('OName [New] : ' 62 || lv_new_record.oname); 63 dbms_output.put_line('Employee_ID [New] : ' 64 || lv_new_record.employee_id); 65 dbms_output.put_line('Employee_Number [New] : ' 66 || lv_new_record.employee_number); 67 dbms_output.put_line('First_Name [New] : ' 68 || lv_new_record.first_name); 69 dbms_output.put_line('Middle_Name [New] : ' 70 || lv_new_record.middle_name); 71 dbms_output.put_line('Last_Name [New] : ' 72 || lv_new_record.last_name); 73 END LOOP; 74 CLOSE c; 75 76 /* Print the close the set. */ 77 dbms_output.put_line( 78 '========================================'); 79 END; 80 / |
The cursor on lines 4 through 11 includes a key trick for reading the object types on lines 8 and 9. The TREAT function instructs the query to instantiate the base_t column as an employee_obj subtype.
You access the object instance on lines 47 through 58 by referring to the lv_new_record variable. You access the individual field element with a dot notation. The same approach lets you access the lv_old_record variable’s contents.
It generates the following output from the employee table:
======================================== Trigger_Log_ID [ROW] : 1 Table_Name [ROW] : EMPLOYEE Table_Name [ROW] : INSERT TRANSACTION [ROW] : PROCESSED ---------------------------------------- OName [Old] : EMPLOYEE_OBJ Employee_ID [Old] : Employee_Number [Old] : First_Name [Old] : Middle_Name [Old] : Last_Name [Old] : ---------------------------------------- OName [NEW] : EMPLOYEE_OBJ Employee_ID [NEW] : 1 Employee_Number [NEW] : B98765-678 First_Name [NEW] : Catherine Middle_Name [NEW] : Last_Name [NEW] : Zeta-Jones ======================================== Trigger_Log_ID [ROW] : 2 Table_Name [ROW] : EMPLOYEE Table_Name [ROW] : UPDATE TRANSACTION [ROW] : REJECTED ---------------------------------------- OName [Old] : EMPLOYEE_OBJ Employee_ID [Old] : 1 Employee_Number [Old] : B98765-678 First_Name [Old] : Catherine Middle_Name [Old] : Last_Name [Old] : Zeta-Jones ---------------------------------------- OName [NEW] : EMPLOYEE_OBJ Employee_ID [NEW] : 1 Employee_Number [NEW] : B98765-678 First_Name [NEW] : Catherine Middle_Name [NEW] : Last_Name [NEW] : Zeta Jones ======================================== |
This article has shown you how to create a framework for the writing trigger results from multiple tables into a single logging table. It’s also shown you how to leverage column substitutability with the base_t type column.
While this example has shown you to query with an anonymous block, you should really use an object table function. You would develop one object table function for each different type of output.
Critical Triggers
Oracle Critical and Non-critical Triggers
This article demonstrates how you can write critical and non-critical row-level triggers. You may ask yourself, what are critical and non-critical triggers? That’s a great question. A critical trigger stops processing and raises an exception within the scope of an Application Programming Interface (API). An API is typically a series of end-user forms that help you solve business problems. A non-critical trigger either allows users to perform undesired behavior or it automatically fixes undesired behavior by preventing it. Non-critical triggers may log events but they don’t typically raise exceptions to the API.
Next, you’re probably asking yourself if critical and non-critical triggers are important. That’s also a great question. The answer is they’re very important and a key part of any database-centric application software solution.
If you’re new to database triggers, you can read the DML Trigger Basic article on this site to get an introduction. By way of review, you can write database triggers against DDL or DML statements. DML triggers can be either statement-level or row-level triggers.
The difference between a statement-level and row-level trigger is simple. A statement-level trigger runs once for any INSERT, UPDATE, or DELETE statement, which means you can’t inspect the specific rows that a DML statement affects. A row-level trigger runs once for each row affected by an INSERT, UPDATE, or DELETE statement.
Row-level database triggers give us the most granular (fancy word for detailed) view of transactions in your application. They’re also the best suited to logging changes happening with your data. The examples in this article will use DML row-level database triggers.
Business Logic
The article creates some tables for the examples, and the tables use traditional Oracle sequences and triggers. That’s because using sequences and triggers is the closest to how Oracle APEX creates tables. Many readers are familiar with how APEX works. After we create the tables, sequences, and basic automatic numbering database triggers, you will learn how to create non-critical triggers. The last section shows you how to create critical triggers.
It’s helpful to have a basic business problem when you work with so many moving parts. I chose a business problem that should be familiar to most people. The example uses a human resource professional. A human resource professional creates new employees when they join a company. Company policy sometimes dictates the convention for personal names. For example, they may restrict multipart last names. That means when you want to enter a multipart last name; they replace the whitespace with a hyphen.
The example business case requires that all last names must have hyphens. This means that the company disallows multipart last names. While this may seem old fashioned, it’s a simple business process to model, and it lets you see how to work with non-critical and critical database triggers.
So, here are our two use cases:
Non-critical Use Case
A human resource professional may try to enter a multipart last name with whitespace between parts. The entry may be intentional or simply a mistake. Assuming a positive mental attitude, you should assume the human resource profession doesn’t understand the policy. That means our triggers shouldn’t raise an exception when initially entering a value. The insert trigger should only log the attempt to enter non-conforming data. Initial entries, like this, are made through INSERT statements.
Critical Use Case
What the same human resource professional does when they notice that they weren’t able to enter a multipart last name becomes important. A critical trigger becomes necessary when the human resource professional tries to change a hyphenated name into a multipart name. The API uses an UPDATE statement to change an existing value with a new value. There is no use case when the human resource professional accepts the change to a hyphenated name.
The following steps you through how you create a framework for the non-critical and critical triggers. The framework uses three tables.
Framework
The non-critical trigger only uses two of those tables. The non-critical trigger is an INSERT trigger and the critical trigger is an UPDATE trigger. The application_user table will contain information about our authorized users; and the employee table will be the target for our non-critical and critical triggers.
The following creates the application_user table with this statement:
SQL> CREATE TABLE application_user 2 ( application_user_id NUMBER 3 , application_user_name VARCHAR2(30) CONSTRAINT application_user_nn1 NOT NULL 4 , created_by NUMBER CONSTRAINT application_user_nn2 NOT NULL 5 , creation_date DATE CONSTRAINT application_user_nn3 NOT NULL 6 , last_updated_by NUMBER CONSTRAINT application_user_nn4 NOT NULL 7 , last_update_date DATE CONSTRAINT application_user_nn5 NOT NULL 8 , CONSTRAINT application_user_pk PRIMARY KEY (application_user_id) 9 , CONSTRAINT application_user_fk1 FOREIGN KEY (created_by) 10 REFERENCES application_user (application_user_id) 11 , CONSTRAINT application_user_fk2 FOREIGN KEY (last_updated_by) 12 REFERENCES application_user (application_user_id)); |
The application_user_seq supports a surrogate key for the application_user table. You create it with the following statement:
SQL> CREATE OR REPLACE TRIGGER application_user_t1 2 BEFORE INSERT ON application_user 3 FOR EACH ROW 4 BEGIN 5 /* Check for a empty image_id primary key column value, 6 and assign the next sequence value when it is missing. */ 7 IF :NEW.application_user_id IS NULL THEN 8 SELECT application_user_seq.NEXTVAL 9 INTO :NEW.application_user_id 10 FROM dual; 11 END IF; 12 END; 13 / |
You will need at least one row in the application_user table to test the non-critical and critical triggers. The following insert a single row into the application_user table:
SQL> INSERT INTO application_user 2 ( application_user_name 3 , created_by 4 , creation_date 5 , last_updated_by 6 , last_update_date) 7 VALUES 8 ('Database Administrator' 9 , 1 10 , TRUNC(SYSDATE) 11 , 1 12 , TRUNC(SYSDATE)); |
The next statement creates the employee table:
SQL> CREATE TABLE employee 2 ( employee_id NUMBER 3 , employee_number VARCHAR2(10) 4 , first_name VARCHAR2(20) CONSTRAINT employee_nn1 NOT NULL 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) CONSTRAINT employee_nn2 NOT NULL 7 , created_by NUMBER CONSTRAINT employee_nn3 NOT NULL 8 , creation_date DATE CONSTRAINT employee_nn5 NOT NULL 9 , last_updated_by NUMBER CONSTRAINT employee_nn6 NOT NULL 10 , last_update_date DATE CONSTRAINT employee_nn7 NOT NULL 11 , CONSTRAINT employee_pk PRIMARY KEY (employee_id) 12 , CONSTRAINT employee_fk1 FOREIGN KEY (created_by) 13 REFERENCES application_user (application_user_id) 14 , CONSTRAINT employee_fk2 FOREIGN KEY (last_updated_by) 15 REFERENCES application_user (application_user_id)); |
You create the employee_seq sequence with this statement:
SQL> CREATE SEQUENCE employee_seq; |
Next, you create a trigger to generate sequence values like you did for the application_user table:
SQL> CREATE OR REPLACE TRIGGER employee_t1 2 BEFORE INSERT ON employee 3 FOR EACH ROW 4 BEGIN 5 /* Check for a empty image_id primary key column value, 6 and assign the next sequence value when it is missing. */ 7 IF :NEW.employee_id IS NULL THEN 8 SELECT employee_seq.NEXTVAL 9 INTO :NEW.employee_id 10 FROM dual; 11 END IF; 12 END; 13 / |
You have created the two tables for our non-critical trigger. The next section relies on the framework and integrates with it.
Non-critical Trigger
Before you create the logging trigger, you should test the concept of replacing a whitespace in a multipart last name with a hyphenated name. The following INSERT trigger fixes user input by replacing the whitespace with a hyphen. It doesn’t log the entry and some times you won’t log results for this type of trigger.
You create the employee_t2 trigger with the following:
SQL> CREATE OR REPLACE TRIGGER employee_t2 2 BEFORE INSERT ON employee 3 FOR EACH ROW 4 FOLLOWS employee_t1 5 WHEN (REGEXP_LIKE(NEW.last_name,' ')) 6 BEGIN 7 /* Substitute a dash for the white space. */ 8 :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); 9 END; 10 / |
Line 4 designates that employee_t2 executes after employee_t1, which is the purpose of the FOLLOWS command. Line 8 uses the REGEXP_REPLACE function to find and replace the first instance of a whitespace with a hyphen.
After creating the employee_t2 trigger, you can test it by using an INSERT statement like this:
SQL> INSERT INTO employee 2 ( employee_number 3 , first_name 4 , last_name 5 , created_by 6 , creation_date 7 , last_updated_by 8 , last_update_date ) 9 VALUES 10 ('B12345-678' 11 ,'Sandy' 12 ,'Johnston Smith' 13 , 1 14 , TRUNC(SYSDATE) 15 , 1 16 , TRUNC(SYSDATE)); |
You can verify that the employee_t1 trigger prevented the entry of a multipart last name with the following query:
SQL> COLUMN employee_id FORMAT 9999 HEADING "Employee|ID #" SQL> COLUMN employee_number FORMAT A10 HEADING "Employee|Number" SQL> COLUMN first_name FORMAT A20 HEADING "First Name" SQL> COLUMN last_name FORMAT A20 HEADING "Last Name" SQL> SELECT employee_id 2 , employee_number 3 , first_name 4 , last_name 5 FROM employee; |
It returns:
Employee Employee
ID # Number First Name Last Name
-------- ---------- -------------------- --------------------
1 B12345-678 Sandy Johnston-Smith |
As you see from the results, the last name is hyphenated. If we accept another use case for the UPDATE statement, we may treat updates like you treat inserts.
An INSERT trigger doesn’t guarantee the user can’t change the hyphenated last name into a multipart last name. The application user can always change the value by using an UPDATE statement. That’s why there must be an UPDATE trigger.
The first element of a our
SQL> CREATE OR REPLACE TRIGGER employee_t3 2 BEFORE UPDATE OF last_name ON employee 3 FOR EACH ROW 4 WHEN (REGEXP_LIKE(NEW.last_name,' ')) 5 BEGIN 6 /* Substitute a dash for the white space. */ 7 :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); 8 END; 9 / |
Line 2 guarantees that the UPDATE trigger only runs when an UPDATE statement changes the last_name column of the employee table. An UPDATE statement like the following causes the trigger to run (technically, the jargon is “fire”):
SQL> UPDATE employee 2 SET last_name = 'Johnston Smith' 3 WHERE employee_number = 'B12345-678'; |
Having shown you how to create the non-critical INSERT and UPDATE triggers, I’ll now show you how to create the following employee_log table. This is where you can store the results from INSERT, UPDATE, and DELETE triggers. All columns are nullable (or optional) columns except the sequence generated employee_log_id column. The columns are optional because an INSERT statement never has an old set of values, and a DELETE statement never has a new set of values. Only the UPDATE statement provides old and new values inside a trigger.
The following creates the employee_log table:
SQL> CREATE TABLE employee_log 2 ( employee_log_id NUMBER 3 , employee_event VARCHAR2(6) 4 , old_employee_id NUMBER 5 , old_employee_number VARCHAR2(10) 6 , old_first_name VARCHAR2(20) 7 , old_middle_name VARCHAR2(20) 8 , old_last_name VARCHAR2(20) 9 , old_created_by NUMBER 10 , old_creation_date DATE 11 , old_last_updated_by NUMBER 12 , old_last_update_date DATE 13 , new_employee_id NUMBER 14 , new_employee_number VARCHAR2(10) 15 , new_first_name VARCHAR2(20) 16 , new_middle_name VARCHAR2(20) 17 , new_last_name VARCHAR2(20) 18 , new_created_by NUMBER 19 , new_creation_date DATE 20 , new_last_updated_by NUMBER 21 , new_last_update_date DATE 22 , CONSTRAINT employee_log_pk PRIMARY KEY (employee_log_id)); |
You should create the employee_log_seq sequence, like
SQL> CREATE SEQUENCE employee_log_seq; |
Then, you should add an employee_log_t1 trigger to generate the sequence value automatically. The trigger follows the pattern of the prior two triggers for the application_user and employee tables.
You create the employee_log_seq trigger with the following syntax:
SQL> CREATE OR REPLACE TRIGGER employee_log_t1 2 BEFORE INSERT ON employee_log 3 FOR EACH ROW 4 BEGIN 5 /* Check for a empty image_id primary key column value, 6 and assign the next sequence value when it is missing. */ 7 IF :NEW.employee_log_id IS NULL THEN 8 SELECT employee_log_seq.NEXTVAL 9 INTO :NEW.employee_log_id 10 FROM dual; 11 END IF; 12 END; 13 / |
The logging table is the first step. After creating the logging table, you need to create a standalone log_invalid_employee procedure. The following code creates the procedure. This procedure only runs in the current transaction context, and later another version shows you how to implement it in an autonomous transaction context.
SQL> CREATE OR REPLACE 2 PROCEDURE log_invalid_employee 3 ( pv_employee_event VARCHAR2 4 , pv_old_employee_id NUMBER 5 , pv_old_employee_number VARCHAR2 6 , pv_old_first_name VARCHAR2 7 , pv_old_last_name VARCHAR2 8 , pv_old_created_by NUMBER 9 , pv_old_creation_date DATE 10 , pv_old_last_updated_by NUMBER 11 , pv_old_last_update_date DATE 12 , pv_new_employee_id NUMBER 13 , pv_new_employee_number VARCHAR2 14 , pv_new_first_name VARCHAR2 15 , pv_new_last_name VARCHAR2 16 , pv_new_created_by NUMBER 17 , pv_new_creation_date DATE 18 , pv_new_last_updated_by NUMBER 19 , pv_new_last_update_date DATE) IS 20 BEGIN 21 /* Write to the log table. */ 22 INSERT INTO employee_log 23 ( employee_event 24 , old_employee_id 25 , old_employee_number 26 , old_first_name 27 , old_last_name 28 , old_created_by 29 , old_creation_date 30 , old_last_updated_by 31 , old_last_update_date 32 , new_employee_id 33 , new_employee_number 34 , new_first_name 35 , new_last_name 36 , new_created_by 37 , new_creation_date 38 , new_last_updated_by 39 , new_last_update_date ) 40 VALUES 41 ( pv_employee_event 42 , pv_old_employee_id 43 , pv_old_employee_number 44 , pv_old_first_name 45 , pv_old_last_name 46 , pv_old_created_by 47 , pv_old_creation_date 48 , pv_old_last_updated_by 49 , pv_old_last_update_date 50 , pv_new_employee_id 51 , pv_new_employee_number 52 , pv_new_first_name 53 , pv_new_last_name 54 , pv_new_created_by 55 , pv_new_creation_date 56 , pv_new_last_updated_by 57 , pv_new_last_update_date ); 58 END log_invalid_employee; 59 / |
With the logging table and procedure, you can now rework the INSERT and UPDATE triggers into a single trigger. The new trigger fires when an INSERT or an UPDATE statement affects the employee table. That means you can log the data from both events.
If you created employee_t1, employee_t2 and employee_t3 triggers, you need to drop employee_t2 and employee_t3 triggers before creating the new trigger. The previous employee_t3 trigger will cause incorrect behaviors because it is incompatible with the new employee_t1 trigger.
The new employee_t1 trigger is:
SQL> CREATE OR REPLACE TRIGGER employee_t1 2 BEFORE INSERT OR UPDATE OF last_name ON employee 3 FOR EACH ROW 4 WHEN (REGEXP_LIKE(NEW.last_name,' ')) 5 DECLARE 6 /* DML event label. */ 7 lv_employee_event VARCHAR2(6); 8 BEGIN 9 /* Check for an event and assign event value. */ 10 IF INSERTING THEN 11 /* Check for a empty image_id primary key column value, 12 and assign the next sequence value when it is missing. */ 13 IF :NEW.employee_id IS NULL THEN 14 SELECT employee_seq.NEXTVAL 15 INTO :NEW.employee_id 16 FROM dual; 17 END IF; 18 lv_employee_event := 'INSERT'; 19 ELSE 20 lv_employee_event := 'UPDATE'; 21 END IF; 22 23 /* Log the details captured by an insert or update. */ 24 log_invalid_employee 25 ( pv_employee_event => lv_employee_event 26 , pv_old_employee_id => :old.employee_id 27 , pv_old_employee_number => :old.employee_number 28 , pv_old_first_name => :old.first_name 29 , pv_old_last_name => :old.last_name 30 , pv_old_created_by => :old.created_by 31 , pv_old_creation_date => :old.creation_date 32 , pv_old_last_updated_by => :old.last_updated_by 33 , pv_old_last_update_date => :old.last_update_date 34 , pv_new_employee_id => :NEW.employee_id 35 , pv_new_employee_number => :NEW.employee_number 36 , pv_new_first_name => :NEW.first_name 37 , pv_new_last_name => :NEW.last_name 38 , pv_new_created_by => :NEW.created_by 39 , pv_new_creation_date => :NEW.creation_date 40 , pv_new_last_updated_by => :NEW.last_updated_by 41 , pv_new_last_update_date => :NEW.last_update_date ); 42 43 /* Substitute a dash for the white space. */ 44 :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); 45 END; 46 / |
This non-critical trigger checks whether the event is an INSERT statement on line 10. The trigger generates a sequence value when an INSERT statement fires the trigger. Then, the trigger sets a local variable with the INSERT string. It assigns an UPDATE string when an UPDATE statement fires the trigger.
After the event detection logic, the trigger calls the log_invalid_employee procedure on line 24. Line 44 changes the multipart last name into a hyphenated last name.
This part of the article has shown you how to create and manage non-critical triggers.
Critical Trigger
This part of the article shows you how to create and manage critical triggers. The key difference is that critical triggers stop the transaction that fires the trigger. This has significant impact on how you design and implement the log_invalid_employee procedure.
You need to modify the log_invalid_employee procedure so that it supports autonomous transactions. That requires adding a PRAGMA precompiler directive in the declaration block and a COMMIT statement after the INSERT statement.
The following shows you the changes required in the log_invalid_employee procedure:
SQL> CREATE OR REPLACE 2 PROCEDURE log_invalid_employee 3 ( pv_employee_event VARCHAR2 4 , pv_old_employee_id NUMBER ... 21 /* Set precompiler directive to run in a separate context. */ 22 PRAGMA AUTONOMOUS_TRANSACTION; 23 BEGIN ... 62 /* Commit the autonmous transaction. */ 63 COMMIT; 64 END log_invalid_employee; 65 / |
Line 22 holds the autonomous transaction PRAGMA, and line 63 holds the COMMIT statement. Both of these are required when you want to enable a trigger to both log data and raise an exception that terminates the transaction.
Next, you need to rework the employee_t1 trigger by adding content to the declaration and execution blocks, and by adding an exception block. The declaration block requires you to declare an exception variable and create a PRAGMA precompiler directive. The exception block requires you to add a conditional block at the end of the execution block. You also need to add an exception block to manage a raised exception.
The following shows you the changes required for the employee_t1 trigger:
SQL> CREATE OR REPLACE TRIGGER employee_t1 2 BEFORE INSERT OR UPDATE OF last_name ON employee 3 FOR EACH ROW 4 WHEN (REGEXP_LIKE(NEW.last_name,' ')) 5 DECLARE ... 9 /* Declare exception. */ 10 e EXCEPTION; 11 PRAGMA EXCEPTION_INIT(e,-20001); 12 BEGIN ... 47 /* Re-check for an event and assign event value. */ 48 IF INSERTING THEN 49 /* Substitute a dash for the white space. */ 50 :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); 51 ELSE 52 /* Throw exception. */ 53 RAISE_APPLICATION_ERROR(-20001,'No two-part last names without a hyphen.'); 54 END IF; 55 EXCEPTION 56 /* Capture an exception. */ 57 WHEN e THEN 58 ROLLBACK; 59 dbms_output.put_line('[Trigger Event: '||lv_employee_event||']'); 60 dbms_output.put_line(SQLERRM); 61 WHEN OTHERS THEN 62 dbms_output.put_line(SQLERRM); 63 END; 64 / |
Line 10 declares a local exception variable. Line 11 declares the PRAGMA precompiler directive. Lines 47 through 54 implements the conditional logic for writing a hyphenated last name for an INSERT statement, and the logic for raising an exception for an UPDATE statement.
An INSERT statement causes the database trigger to write to the employee_log logging table. An UPDATE statement causes the database trigger to write to the logging table and throw an exception.
The test case for a critical event trigger uses an UPDATE statement, as shown:
SQL> UPDATE employee 2 SET last_name = 'Zeta Jones' 3 WHERE employee_number = 'B98765-678'; |
The first thing you see is a thrown exception, like
[Trigger Event: UPDATE] ORA-20001: No two-part last names without a hyphen. |
After you see the thrown exception, you can run the following query to see what has been written to the exception_log table:
SQL> COLUMN employee_log_id FORMAT 9999 HEADING "Empl|Log|ID #" SQL> COLUMN old_employee_id FORMAT 9999 HEADING "Empl|ID #" SQL> COLUMN old_name FORMAT A25 HEADING "Old Name" SQL> COLUMN new_employee_id FORMAT 9999 HEADING "Empl|ID #" SQL> COLUMN new_name FORMAT A25 HEADING "New Name" SQL> SELECT employee_log_id 2 , old_employee_id 3 , DECODE( old_last_name || ', '|| old_first_name,', ',NULL 4 , old_last_name || ', '|| old_first_name) AS "old_name" 5 , new_employee_id 6 , DECODE( new_last_name || ', '|| new_first_name,', ',NULL 7 , new_last_name || ', '|| new_first_name) AS "new_name" 8 FROM employee_log; |
It displays:
Empl
Log Empl Empl
ID # ID # Old Name ID # New Name
----- ----- ------------------------- ----- -------------------------
1 2 Evert Lloyd, Chris
2 2 Evert-Lloyd, Chris 2 Evert Lloyd, Chris
3 3 Zeta Jones, Catherine
4 3 Zeta-Jones, Catherine 3 Zeta Jones, Catherine |
The ultimate test of these is that while there were many attempts at entering a multipart last name, none of them succeeds. You can query the last_name column from the employee table to verify that, like
SQL> SELECT last_name 2 FROM employee; |
It should show you the three rows that you’ve inserted and updated through this article. You should see:
Last Name ------------------------- Johnston-Smith Evert-Lloyd Zeta-Jones |
Through this article you should have learned how to create non-critical and critical triggers. These techniques are important when you manage transactions against business rules that can’t be supported by ordinary database constraints.
Subscribe via RSS