Editor's note: Kevin Owens, author of the new Prentice Hall Professional Technical Reference book "Programming Oracle triggers and stored procedures" shares the following tip for those planning to work with Oracle10g's global temporary table features. To get more technical advice from Kevin, download a free copy of Chapter 3: Declarative contraints.
Oracle Database 10g extends the features of global temporary tables to permit VARRAY and nested table columns. The following tip will explain how to use these new features.
Student registration system example
[ Return to Table of Contents ]
The context of the example below is a student registration system.
The first code snippet is a TYPE definition used to capture a single class for which a student registers. It has two components: a class number (such as CS101) and a registration type such as AUDIT (A) or CREDIT (C).
CREATE OR REPLACE TYPE reg_entry AS OBJECT ( class_no VARCHAR2(15), reg_type VARCHAR2(1) ); /
The following CREATE statement is for a VARRAY TYPE that stores a limited number of registration requests. The assumption is that a student will and cannot register for more than 15 classes in a single semester.
CREATE OR REPLACE TYPE reg_entry_varray_type AS VARRAY (15) OF reg_entry; /
The TEMPORARY table provides an application with the use of a SQL table as a "private," in-memory structure that can be manipulated with SQL INSERT, UPDATE and DELETE statements. Although the temporary table object is created once in the database (using CREATE GLOBAL TEMPORARY TABLE syntax), 10 applications can concurrently be inserting data into the temporary table; each application sees only their data. The data in the table is dropped either upon a COMMIT or when the session ends.
The following TEMPORARY table is created with the attribute, ON COMMIT DELETE ROWS. This allows an application to load registration entries into the global temporary table and manipulate that data with SQL statements. The data is deleted upon each commit with the clause ON COMMIT DELETE ROWS.
CREATE GLOBAL TEMPORARY TABLE student_reg_entries ( student_name VARCHAR2(30), reg_entries reg_entry_varray_type ) ON COMMIT DELETE ROWS;
Replace ON COMMIT DELETE ROWS with ON COMMIT PRESERVE ROWS to retain temporary table data throughout the database session, regardless of any commits made during that session.
The following illustrates a PL/SQL block that populates this temporary table with two classes each for two students. It makes no difference how many other applications are currently using this temporary global table for similar purposes. For the code below, two rows are inserted, then a COMMIT, after which a SELECT shows that the table is empty.
DECLARE classes_to_take reg_entry_varray_type := reg_entry_varray_type(); BEGIN classes_to_take := reg_entry_varray_type( reg_entry('CS101', 'C' ), reg_entry('HST310', 'C' )); INSERT INTO student_reg_entries VALUES ('John', classes_to_take); classes_to_take := reg_entry_varray_type( reg_entry('ENG102', 'C' ), reg_entry('BIO201', 'C' )); INSERT INTO student_reg_entries VALUES ('Mary', classes_to_take); END; /
This next SELECT returns two rows:
SQL> SELECT * FROM student_reg_entries; Row 1: John REG_ENTRY_VARRAY_TYPE (REG_ENTRY('CS101','C'), REG_ENTRY('HST310','C')) Row 2: Mary REG_ENTRY_VARRAY_TYPE (REG_ENTRY('ENG102','C'), REG_ENTRY('BIO201','C'))
A COMMIT automatically deletes rows making the table available for the next session transaction.
SQL> COMMIT; SQL> SELECT * from student_reg_entries; no rows selected
Global tables with triggers to enforce business rules
[ Return to Table of Contents ]
Business rules are often implemented with logic that precedes an INSERT or other DML statement. For example, given a student course registration system, it's natural for an application to first verify that a class change is valid prior to an UPDATE that finalizes a student's change request. One specific check performed by the application could be to verify that the schedule change does not cause the student to be taking two separate classes at the exact same time of day. The application logic looks like:
check for schedule conflict; If conflict exists then Return error code and message text; Else UPDATE row with change; End;
An alternative is to consider the application "checks" to be business rules that are enforced on a par equal to other rules such as declarative constraints. This means that checking for schedule conflicts is performed procedurally with database triggers. The application then does not perform a schedule conflict check but is prepared to handle the error should the check fail within the trigger. The application error handling mechanism is identical to handling other declarative constraint violations, which includes the use of PGARMA EXCEPTION_INIT.
There is no saving of code. However, there is a conceptual difference because rather than thinking about detailed application logic with numerous checks containing CASE logic, the business rule is encapsulated in trigger and business rule package code. The application only needs to make the INSERT or UPDATE and handle any possible error conditions that might arise.
The following paragraphs illustrate a simple example with PL/SQL that incorporates these features:
- An ERRORS package that is dedicated to housing application error numbers and error text messages. This is called ERROR_PKG.
- Row and statement level triggers on the REGISTRATION table.
- An error log table that is used solely to capture the specific error encountered during a transaction. This table is called ERROR_LOG.
- The AUTONOMOUS TRANSACTION feature is used to INSERT error log messages in the middle of an UPDATE transaction. This code is in the body of the business rules package, REGISTRATION_RULES.
- A GLOBAL TEMPORARY TABLE used to capture row changes. This temporary table is used by the statement level trigger to perform the business rule processing. This table is named REG_CHANGES.
- A Business Rule Package (REGISTRATION_RULES) that encapsulates the business logic that is applied to each student course change.
Note: The code below was run on Oracle version 188.8.131.52.0 and 10.1.0.1.0.
First, the application example consists of just two tables: COURSES and REGISTRATION. Below is some data that shows the name and times of four classes. English 101 is taught at four different times. Each English class is 50 minutes. Biology is 75 minutes and starts at 9:00.
Critical to this example is the fact that the biology class (1003) extends into English 1002. We will see what happens when a student, who is taking biology, tries to switch an English class from 8:00 am to 10:00 am, which is English 1003. This fails, but the switch to the 2:00 class (1004) will succeed.
TABLE COURSES: COURSE CLASS# START_TIME STOP_TIME ENGL 101 1001 0800 0850 ENGL 101 1002 0900 0950 ENGL 101 1003 1000 1050 ENGL 101 1004 1400 1450 BIOL 110 1005 900 1015
The statement of the business rule needs to be short and simple. The business rule we want to enforce is the following:
When a student changes a class, the new class cannot conflict with the times of any other existing class.
Let's create the COURSES table and insert five rows.
CREATE TABLE courses ( course_name VARCHAR2(15), class_no varchar2(6) PRIMARY KEY, start_time number(4), stop_time number(4) ); rem There are four times for English: 8, 9 10 AM and 2PM rem The Biology has a lab and is longer. It starts at 9AM. INSERT INTO courses VALUES ('ENGL 101','1001', 800, 850); INSERT INTO courses VALUES ('ENGL 101','1002', 900, 950); INSERT INTO courses VALUES ('ENGL 101','1003',1000,1050); INSERT INTO courses VALUES ('ENGL 101','1004',1400,1450); INSERT INTO courses VALUES ('BIOL 110','1005', 900,1015);
The class schedule class is simply the student name and class number. Below is the REGISTRATION table followed by entries for John who will take English at 8:00 am and biology.
CREATE TABLE registration ( student_name VARCHAR2(15), class_no varchar2(6) references courses (class_no) ); rem We could add a concatenated primary key on student_name rem and class_no because we want to insure that a student rem does not register for the same class twice. rem Register for English at 8AM INSERT into registration VALUES ('John', '1001'); rem Register for Biology at 9AM INSERT into registration VALUES ('John', '1005');
Let's show what John has scheduled for himself.
SELECT a.student_name, b.course_name, b.start_time, b.stop_time FROM registration a, courses b WHERE a.class_no = b.class_no order by start_time;
STUDENT_NAME COURSE_NAME START_TIME STOP_TIME --------------- --------------- ---------- ---------- John ENGL 101 800 850 John BIOL 110 900 1015
As we will see, John does not want to wake up for a 8:00 class. 9:00 is not too bad. So, he'll change the English from 8:00 to 10:00 not realizing that the biology is 75 minutes, not 50 minutes. But he can switch to the 2:00 class without any problem.
This design starts with an ERRORS package (called ERRORS_PKG). This package is the single repository for application error numbers and error messages enforced by our business rules. These error numbers are raised using the RAISE_APPLICATION_ERROR procedure. Oracle allows error numbers within the negative number range (-20999 to -20000)
CREATE OR REPLACE PACKAGE errors_pkg IS schedule_conflict CONSTANT PLS_INTEGER := -20999; schedule_conflict_msg CONSTANT VARCHAR2(100) := 'conflicting times.'; END errors_pkg; /
Approaches to handling errors
[ Return to Table of Contents ]
There are several approaches to handling errors. The application can capture the business rule violation as an exception and then post a locally generated message to the end user. This particular example relies on an error log database table. There are many ways to design and implement error handling, but a goal should be to keep a consistent look and feel to the application. When some screens inconsistently incorporate lengthy messages while others provide short text messages, the end users notice. An error log table, as used in this example, helps to achieve consistency. This error log table is populated by the business rule package code that is invoked through the statement level triggers. When an error occurs, the application can look up the error in the error log table. Unfortunately, this design does force another trip to the database so there are pros and cons. Again, this is just another option, illustrated and implemented by the statement level trigger logging an error with an autonomous transaction. The error log table is:
CREATE TABLE ERROR_LOG (student_name VARCHAR2(15), text VARCHAR2(300));
We will be using a global temporary table, REG_CHANGES. This table is populated with the change information by the UPDATE ROW trigger. When John changes his schedule, the specifics of that change is written to this global temporary table by the UPDATE ROW trigger – that is all that takes place during row trigger execution.
Why isn't the check done in the row trigger? The actual business rule is enforced by a STATEMENT level trigger. In order to check for a schedule conflict the REGISTRATION and COURSES classes must be read for possible time conflicts. An UPDATE ROW trigger cannot get a consistent read on the same table that is the target of the UPDATE statement itself. In brief, an UPDATE ROW trigger on the REGISTRATION table generates a mutating table error if the trigger attempts to SELECT FROM the REGISTRATION table. So, we move that logic to the after statement trigger.
The GLOBAL TEMPORARY TABLE is populated only by the UPDATE ROW trigger. The temporary table is later read by the UPDATE STATEMENT trigger which builds a VARRAY that is passed to the business rule validation package. The table is created with the following.
CREATE OR REPLACE TYPE reg_change AS OBJECT ( student_name VARCHAR2(15), class_no VARCHAR2(6) ); / CREATE GLOBAL TEMPORARY TABLE reg_changes (c reg_change) ON COMMIT DELETE ROWS;
This business rule logic is encapsulated in the package REGISTRATION RULES. The purpose of this code is to return a BOOLEAN value indicating a schedule conflict. The statement level trigger uses this package to determine a conflict. If there is a conflict (TRUE is returned by the package function) the trigger calls RAISE_APPLICATION_ERROR. Could the error be raised from within the package? It certainly can. There are many strategies and one of the goals of this document is to shed light on the many options available.
The PL/SQL package specification that checks for schedule conflicts, intended only to be called by the AFTER STATEMENT trigger is shown next.
CREATE OR REPLACE TYPE reg_change_varray_type AS varray(15) of reg_change; / CREATE OR REPLACE PACKAGE registration_rules is -- Returns TRUE only if there is a conflict with the schedule change. FUNCTION schedule_conflict_exists(v reg_change_varray_type) RETURN BOOLEAN; END registration_rules; /
Because of its length, the package BODY is shown at the end of the document.
Now for the triggers, the UPDATE ROW trigger simply populates the global temporary table with row changes affected by the UPDATE statement. The UPDATE STATEMENT level trigger first builds an array from the temporary table and then passes that array to the business rules package for processing. Any schedule conflict will result in (1) an error log message to the ERROR_LOG table and, (2) a return code indicating the conflict. Because the business rule package records autonomous transactions, the statement trigger must pull all rows from the temporary table. The use of a global temporary table cannot span into and out of a procedure that records autonomous transactions. The VARRAY construction is just a few lines of code and copies data from REG_CHANGES into a VARRAY. It is then done with REG_CHANGES. Showing the ROW and STATEMENT trigger code:
CREATE OR REPLACE TRIGGER registration_AUR AFTER UPDATE ON registration FOR EACH ROW BEGIN -- insert student change into global temporary table. INSERT INTO reg_changes (c) values (reg_change(:new.student_name, :new.class_no)); END; / CREATE OR REPLACE TRIGGER registration_AUS AFTER UPDATE ON registration DECLARE B BOOLEAN; v reg_change_varray_type := reg_change_varray_type(); BEGIN -- copy global temporary table into local varray. FOR i IN (select rownum n, c from reg_changes) LOOP v.extend; v(i.n) := reg_change(i.c.student_name, i.c.class_no); END LOOP; IF registration_rules.schedule_conflict_exists(v) THEN RAISE_APPLICATION_ERROR (errors_pkg.schedule_conflict,errors_pkg.schedule_conflict_msg); END IF; END; /
Additional rules enforcement
[ Return to Table of Contents ]
The above may seem like a lot of code but here are some things to consider. There are more components: triggers, type definitions, and errors package, a business rule enforcement package; however, the total amount of code is probably no greater than a conventional programming approach. True, there are more pieces but they are modularized by their purpose. The design is very scalable and business rule enforcement can be easily added without extensively changing end user application code. Additional rule enforcement is accomplished with the following:
- Adding error numbers and messages to the package ERRORS_PKG
- Adding business rule enforcement to the package REGISTRATION_RULES
- Updating the trigger to call the additional business rule procedure
- Updating the error handling code in the application
What about the application and what does it look like? Below is a PL/SQL block that handles an UPDATE. This is run as an attempt by John to UPDATE English from the 8:00 class to the 10:00 class, not realizing that biology goes into the first 15 minutes of this English class. Secondary is a query on the ERROR LOG table.
SET SERVEROUTPUT ON DECLARE schedule_conflict EXCEPTION; PRAGMA EXCEPTION_INIT (schedule_conflict, -20999); BEGIN UPDATE registration SET class_no = 1003 WHERE class_no = 1001 AND student_name = 'John'; EXCEPTION WHEN schedule_conflict THEN dbms_output.put_line ('ERROR in UPDATE:'||errors_pkg.schedule_conflict_msg); END; / SELECT text FROM error_log WHERE student_name='John';
Execution of the above block leaves John's schedule unchanged. The block captures the exception and prints a text message with DBMS_OUTPUT. In a production environment, the application could display a pop-up message or choose to query the ERROR_LOG table for the reason why John's UPDATE failed. When you look at the business rule package body code below you will see the origination of the error message recorded in the ERROR_LOG table.
Showing the results from executing the above PL/SQL block:
ERROR in UPDATE:conflicting times.
John is registered for course BIOL 110/class_no=1005 scheduled from 900 to 1015 and this conflicts with course ENGL 101/class_no=1003 which spans:1000 to 1050.
Had John chosen the 2:00 pm English class (class number 1004), the UPDATE would succeed.
The application PL/SQL block (containing John's UPDATE) is short and simple. It just contains the UPDATE statement. All the business rule checks are in a single package, REGISTRATION RULES. The behavior of this business rule violation is identical to how errors with declarative constraints are processed. That is with a declared exception and the PRAGMA EXCEPTION INIT. In a way, this example extends the set of declarative constraints to include the specific case where class times conflict within the REGISTRATION table. As business rules change, the major coding effort will be limited to the business rules package.
Finally, the business rules package code. The first function declaration, in the package body, contains the PRAGMA AUTONOMOUS TRANSACTION. This insures that the error message is written and committed to the ERROR_LOG table even though the transaction to the REGISTRATION table is rolled back, by the trigger, using RAISE_APPLICATION_ERROR.
The functionality is split into two functions. The function, visible through the package specification, takes a VARRAY as a parameter. Then, each element in the VARRAY is checked individually by the overloaded function SCHEDULE_CONFLICT_EXISTS which accepts as parameters a student name and class number.
The key logic test is to determine if the new class replacement has a start time or a stop time between the start and stop times of any existing class. Showing the package body which contains overloaded versions of SCHEDULE_CONFLICT_EXISTS:
CREATE OR REPLACE PACKAGE BODY registration_rules is FUNCTION schedule_conflict_exists (v_student_name registration.student_name%TYPE, v_class_no registration.class_no%TYPE) RETURN BOOLEAN IS PRAGMA AUTONOMOUS_TRANSACTION; s error_log.text%TYPE; BEGIN FOR r IN ( SELECT a.student_name c1, b.course_name c2, b.class_no c3, b.start_time c4, b.stop_time c5, c.course_name c6, c.class_no c7, c.start_time c8, c.stop_time c9 FROM registration a, courses b, courses c WHERE a.class_no = b.class_no AND a.student_name = v_student_name AND c.class_no = v_class_no AND a.class_no <> c.class_no AND (c.start_time between b.start_time and b.stop_time OR c.stop_time between b.start_time and b.stop_time)) LOOP s := r.c1 ||' is registered for course '||r.c2||'/class_no='||r.c3 ||' scheduled from '||r.c4||' to '||r.c5 ||' and this conflicts with course ' ||r.c6||'/class_no='||r.c7||' which spans:'||r.c8 ||' to '||r.c9; INSERT INTO ERROR_LOG VALUES (r.c1, s); END LOOP; COMMIT; RETURN (s IS NOT NULL); END schedule_conflict_exists; FUNCTION schedule_conflict_exists(v reg_change_varray_type) RETURN BOOLEAN IS r reg_change; B BOOLEAN; BEGIN FOR i in 1..v.count LOOP r := v(i); B := schedule_conflict_exists(r.student_name, r.class_no); IF B THEN RETURN TRUE; END IF; END LOOP; RETURN FALSE; END schedule_conflict_exists; END registration_rules; /
[ Return to Table of Contents ]
There are many options to implementing error handling and business rule enforcement. The logic can be coded physically within the application. It can be encapsulated within business rules packages – these packages being called from the application. Another option is to invoke the rule enforcement from database triggers and business rules packages, thus insuring that the business rule is always enforced regardless of the UPDATE mechanism, be it a Web application, client/server of SQL*Plus.