MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle 12c’ Category

Session Variables

without comments

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable on a single line with the following command:

    SET @my_variable_name := 'My Value';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list

    SELECT @my_variable_name AS "The Value" FROM dual;

    or WHERE clause

    SELECT column_name
    FROM   table_name
    WHERE  column_name = @my_variable_name;

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable in a single line. It iss critical to note that you must use double quotes around the session variable name and single quotes for the value. You raise an error when you use a single quote instead a double quote around the session variable name. The syntax is:

    SET SESSION "videodb.table_name" = 'new_hire';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list with the current_setting() function call.

    SELECT current_setting('videodb.table_name') AS "The Value";

    or WHERE clause

    SELECT column_name
    FROM   table_name
    WHERE  column_name = current_setting('videodb.table_name');

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

  1. You set a session variable by using the VARIABLE keyword, a variable name, and data type. The supported data types are: BLOB, BFILE, BINARY_DOUBLE, BINARY_FLOAT, CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, REFCURSOR, and VARCHAAR2. You define a variable with the following syntax:

    VARIABLE bv_variable_name VARCHAR2(30)

  2. You assign a value to the bind variable inside an anonymous block by prefacing the variable name with a colon. You assign values inside PL/SQL with the walrus operator (:=) and a string enclosed by single quotes. Anonymous blocks start with a BEGIN and end with an END followed by a semicolon (;) and a forward slash (/) to dispatch the block for execution. The following example shows a full block:

    BEGIN
      :bv_variable_name := 'Some Value';
    END;
    /

  3. You can query any declared variable from the pseudo table dual or as a comparison value in the SELECT-list

    SELECT :bv_variable_name FROM dual;

    or WHERE clause

    SELECT column_name
    FROM   table_name
    WHERE  column_name = :bv_variable_name;

Written by maclochlainn

September 28th, 2019 at 9:01 pm

Misleading ORA- Message

without comments

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.

Written by maclochlainn

August 17th, 2019 at 4:52 pm

Oracle Error Bash f(x)

without comments

My students always struggle initially with basic Linux skills. I wrote little function for their .bashrc file to help them avoid the frustration. It finds and displays all errors by file name, line number and error message for a collection of log files in a single directory (or folder).

errors()
{
  # Determine if any log files exist and check for errors.
  label="File Name:Line Number:Error Code"
  list=`ls ./*.$1 | wc -l`
  if [[ $list} -eq 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    filename=`ls *.txt`
    echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`
  elif [[ ${list} -gt 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-
  fi
}

Let’s say you name your log files with a file extension of .txt, then you would call the function like this:

errors txt

It would return output like the following:

common_lookup_lab.txt:229:ORA-02275: such a referential constraint already exists in the table
common_lookup_lab.txt:239:ORA-02275: such a referential constraint already exists in the table

As always, I hope this helps those looking for a solution.

Written by maclochlainn

August 13th, 2019 at 8:17 pm

Create Oracle User

without comments

After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.

  1. Create a student Oracle user account with the following command:

    CREATE USER student IDENTIFIED BY student
    DEFAULT TABLESPACE users QUOTA 200M ON users
    TEMPORARY TABLESPACE temp;

  2. Grant necessary privileges to the newly created student user:

    GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
    ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
    ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
    ,     CREATE VIEW TO student;

As always, I hope this helps those looking for how to do something that’s less than clear because everybody uses tools.

Written by maclochlainn

August 13th, 2019 at 1:39 pm

Python & Oracle 1

without comments

While Python is an interpreted language, Python is a very popular programming language. You may ask yourself why it is so popular? The consensus answers to why it’s so popular points to several factors. For example, Python is a robust high-level programming language that lets you:

  • Get complex things done quickly
  • Automate system and data integration tasks
  • Solve complex analytical problems

You find Python developers throughout the enterprise. Development, release engineering, IT operations, and support teams all use Python to solve problems. Business intelligence and data scientists use Python because it’s easy to use.

Developers don’t generally write end-user applications in Python. They mostly use Python as scripting language. Python provides a simple syntax that lets developers get complex things done quickly. Python also provides you with a vast set of libraries that let you can leverage to solve problems. Those libraries often simplify how you analyze complex data or automate repetitive tasks.

This article explains how to use the Python programming language with the Oracle database. It shows you how to install and use the cx_Oracle library to query data. Part 2 will cover how you insert, update, and delete data in the Oracle database, and how you call and use PL/SQL stored functions and procedures.

The article has two parts:

  • How you install and use cx_Oracle with the Oracle database
  • How you query data statically or dynamically

This audience for this article should know the basics of writing a Python program. If you’re completely new to Python, you may want to get a copy of Eric Matthes’ Python Crash Course: A Hands-On, Project-Based Introduction to Programming. More experienced developers with shell scripting backgrounds may prefer Al Sweigart’s Automate the Boring Stuff with Python.

This article uses Python 2.7, which appears to be the primary commercial version of Python in most organizations. At least, it’s what most vendors ship with Linux distros. It also happens to be the Python distro on Fedora Linux.

How you install and use cx_Oracle with the Oracle database

The first step requires that you test the current version of Python on your Operating System (OS). For the purpose of this paper, you use the student user account. The student user is in the sudoer list, which gives the account super user privileges.

You can find the Python version by opening a Terminal session and running the following command:

[student@localhost ~]$ python -V

It displays:

Python 2.7.5

You can download the current version of the cx_Oracle library at the Python Software Foundation’s web site. At the time of writing, the current version of the cx_Oracle is the cx_Oracle 5.2.1 version. The cx_Oracle library is available for download as a Red Hat Package Manager (RPM) module.

You download the cx_Oracle-5.2.1-11g-py26-1.x86_64.rpm to the /tmp directory or to a sudoer-enabled user’s downloads directory. Let’s assume you download the RPM into the /tmp directory. After you download the RPM, you can install it with the yum utility with this syntax:

yum install -y /tmp/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm

However, the most current version is now 7.0. You want the following file on Fedora 64-bit Linux, which can be found at the Python Package Index web site:

cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl

A wheel file requires that you use the pip utility (make sure to upgrade to the current version), like:

sudo pip install cx_Oracle-7.0.0-cp27-cp27mu*.whl

It should print the following to the console:

Processing ./cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl                                            
Installing collected packages: cx-Oracle                                                                  
Successfully installed cx-Oracle-7.0.0

The cx_Oracle library depends on the Oracle Client software, which may or may not be installed. It installs without a problem but would raise a runtime error when using the Python software. You can check whether cx_Oracle is installed with the following syntax:

rpm –qa oracle-instantclient11.2-basic

If the oracle-instantclient11.2-basic library isn’t installed, the command returns nothing. If the oracle-instantclient11.2-basic library is installed it returns the following:

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64

Assuming you don’t have the Oracle Client software installed, you should download it from Oracle’s Instant Client Downloads web page. After you download the RPM, you install the Oracle 11g Release 2 Client software with the following syntax:

yum install -y /tmp/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

You now have the necessary software installed and configured to run and test Python programs that work with the Oracle database. Python uses a standard path configuration to look for Python modules or libraries. You can see that set of path values by connecting to the Python IDLE environment, which is the runtime environment. The IDLE environment is very much like the SQL*Plus environment.

You connect to the Python IDLE environment by typing the following:

python

It opens the Python IDLE environment. It should display the following:

Python 2.7.5 (default, Apr 10 2015, 08:09:05) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

You import the sys library and then you can print the path elements with the following command:

>>> import sys
print sys.path

It should print the following for Python 2.7 in Fedora Linux:

['', '/usr/lib64/python27.zip', '/usr/lib64/python2.7', '/usr/lib64/python2.7/plat-linux2', '/usr/lib64/python2.7/lib-tk', '/usr/lib64/python2.7/lib-old', '/usr/lib64/python2.7/lib-dynload', '/usr/lib64/python2.7/site-packages', '/usr/lib64/python2.7/site-packages/gtk-2.0', '/usr/lib/python2.7/site-packages']

You can now test whether the environment works by typing the following commands in the IDLE environment:

>>> import cx_Oracle
db = cx_Oracle.connect("student/student@xe")
print db.version

It prints:

11.2.0.2.0

The other two sections require you to test components inside Python files. That means you need to supplement the default Python path variable. You do that by adding values to the Python environment variable, which is $PYTHONPATH.

The following adds the /home/student/Code/python directory to the Python path variable:

export set PYTHONPATH=/home/student/Code/python

Next, we create an connection.py file, which holds the following:

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Print a message.
  print "Connected to the Oracle " + db.version + " database."
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally
  # Close connection. 
  db.close()

The import statement adds the cx_Oracle library to the program scope. The cx_Oracle library’s connect function takes either the user name and password, or the user name, password, and TNS alias.

The except block differs from what you typically see. The code value maps to the SQLCODE value and the message value maps to the SQLERRM value.

You can test the connection.py file as follows in the /home/student/Code/python directory:

python connection.py

It prints the following:

Connected to the Oracle 11.2.0.2.0 database.

This section has shown you how to setup the cx_Oracle library, and how you can test the cx_Oracle library with Python programs.

How you query data statically or dynamically

The prior section shows you how to connect to an Oracle instance and how to verify the driver version of the cx_Oracle library. Like most ODBC and JDBC software, Python first creates a connection. Then, you need to create a cursor inside a connection.

The basicCursor.py program creates a connection and a cursor. The cursor holds a static SQL SELECT statement. The SELECT statement queries a string literal from the pseudo dual table.

# Import the Oracle library.
import sys
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT 'Hello world!' FROM dual")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
}  db.close()

The connect function assigns a database connection to the local db variable. The cursor function returns a cursor and assigns it to the local cursor variable. The execute function dispatches the query to Oracle’s SQL*Plus and returns the result set into a row element of the local cursor variable. The for-each loop reads the row element from the cursor variable and prints one row at a time. Since the cursor only returns a string literal, there’s only one row to return.

You test the program with this syntax:

python basicConnection.py

It prints:

Hello world!

The next basicTable.py program queries the item table. The item table holds a number of rows of data. The code returns each row inside a set of parentheses.

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT item_title " +
                 ",      item_rating " +
                 "FROM   item " +
                 "WHERE  item_type = "
                 "        (SELECT common_lookup_id " +
                 "         FROM   common_lookup " +
                 "         WHERE  common_lookup_type = 'DVD_WIDE_SCREEN')")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0], row[1]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

The SQL query is split across several lines by using the + operator. The + operator concatenates strings, and it lets you format a long query statement. The range for loop returns tuples from the cursor. The tuples are determined by the SELECT-list of the query.

The query returns the following type of results:

('Casino Royale', 'PG-13')
...
('Star Wars - Episode I', 'PG')
('Star Wars - Episode II', 'PG')
('Star Wars - Episode III', 'PG-13')
('Star Wars - Episode IV', 'PG')
('Star Wars - Episode V', 'PG')
('Star Wars - Episode VI', 'PG')

At this point, you know how to work with static queries. The next example shows you how to work with dynamic queries. The difference between a static and dynamic query is that an element of the string changes.

You have two options for creating dynamic strings. The first lets you glue a string inside a query. The second lets you embed one or more bind variables in a string. As a rule, you should use bind variables because they avoid SQL injection risks.

The following is the basicDynamicTable.py script

# Import the Oracle library.
import cx_Oracle
 
sRate = 'PG-13'
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Define a dynamic statment.
  stmt = "SELECT item_title, item_rating FROM item WHERE item_rating = :rating"
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a statement with a bind variable.
  cursor.execute(stmt, rating = sRate)
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0], row[1]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

You need to assign a dynamic SQL statement to a local string variable. The bind variable is preceded with a colon (:). The execute function takes a string variable with the dynamic SQL statement. Then, you provide a name and value pair. The name needs to match the bind variable in the dynamic SQL statement. The value needs to map to a local Python variable.

The query should return a full list from the item table for the two item_title and item_rating columns:

('Casino Royale', 'PG-13')
...
('Harry Potter and the Goblet of Fire', 'PG-13')
('Harry Potter and the Order of the Phoenix', 'PG-13')
('The Lord of the Rings - Fellowship of the Ring', 'PG-13')
('The Lord of the Rings - Two Towers', 'PG-13')
('The Lord of the Rings - The Return of the King', 'PG-13')
('The Lord of the Rings - The Return of the King', 'PG-13')

This article should have shown you how to effectively work static and dynamic queries. You can find the scripts on the github.com server.

Written by maclochlainn

December 6th, 2018 at 11:40 pm

Lab Correction

without comments

Anyone using the August 2018 Fedora image should note that I neglected to put the right transaction_upload2.csv file in the /u01/app/oracle/upload directory. You can fix that by navigating to the Lab 12 Instructions web page and click on the zip file link to download the correct file. You will see the following dialog asking whether you want to open the file with the Ark utility, click OK to continue:

After clicking OK to open in Ark, you will see the following Ark dialog:

Click on the Home option in the Places dialog to the left, then click the Downloads option. You should see the following dialog before you click the Extract button.

Open a Konsole session and become the root superuser with the following command:

su - root

Change directory to Lab8_Final_CSV_Files directory where you extracted the transaction_upload2.csv file, like this:

cd /home/student/Downloads/Lab8_Final_CSV_Files

Copy the transaction_upload2.csv file to the /u01/app/oracle/upload directory with the following command:

cp /home/student/Downloads/Lab8_Final_CSV_Files/transaction_upload2.csv /u01/app/oracle/upload/.

Change directory to the /u01/app/oracle/upload directory and run the following long list (ll) command:

ll

You should see the following:

-rw-r--r--. 1 oracle dba      80 Aug 23 22:13 character.csv
drwxr-xr-x. 2 oracle dba    4096 Aug 23 20:44 preproc
drwxr-xr-x. 2 oracle dba    4096 Aug 23 23:35 textfile
-rw-r--r--. 1 oracle dba  128700 Dec  4 15:46 transaction_upload2.csv
-rw-r--r--. 1 oracle dba 1739520 Aug 23 22:04 transaction_upload.csv

The transaction_upload2.csv file contains a value of 3 for the created_by and last_updated_by user values. There shouldn’t be a value of 3 in the system_user_id column of the system_user table. The transaction_upload2.csv file should contain a value of 1002 for the created_by and last_updated_by user values.

You can modify the transaction_upload2.csv file once you’ve put it in the correct directory as the root user with the following command:

cat transaction_upload2.csv | sed -e 's/\,3\,/\,1002\,/g' > x; cp x transaction_upload2.csv; rm x

The new image will correct this problem.

Written by maclochlainn

December 4th, 2018 at 4:34 pm

Logging Triggers

without comments

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:

  1. Five tables and sequences, and one UDT base type and two subtypes that map to the specific tables
  2. A reusable autonomous stored procedure
  3. Two data manipulation language (DML) triggers
  4. 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.

Written by maclochlainn

November 25th, 2018 at 6:26 pm

Critical Triggers

with one comment

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.

Written by maclochlainn

November 25th, 2018 at 5:26 pm

Oracle Trigger Basics

without comments

Oracle Trigger Basics

Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.

Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. A primary key constraint guarantees a column or a set of columns are unique and not null. A foreign key constraint guarantees a column only contains a value or set of values found in the primary key. A not null constraint makes a column mandatory when you insert or update a row in a table. A unique constraint guarantees a column or set of columns only exist in one row of a table. A check constraint guarantees a column’s value must comply with a set of rules defined with a row of data.

Database constraints do have limits. For example, a foreign key constraint doesn’t guarantee the right foreign key value because it only guarantees a foreign key value is a valid value in a list of possible values. That means it’s possible to insert or update a foreign key column or set of columns with an incorrect foreign key value. Only a database trigger can guarantee the insert or update of a correct foreign key value. The database trigger verifies the correct behavior by validating conditions before an insert or update.

While a unique constraint guarantees uniqueness and a check constraint guarantees compliance against a set of rules in a row, only a database trigger can guarantee the maximum number of like rows in a table that comply with a rule. Also, there is no constraint that manages inserts, updates, and deletes with dependencies on data in other tables.

A Data Manipulation Language (DML) trigger lets you manage these shortfalls and more. You have two options when implementing DML triggers. One implements a statement trigger and the other implements a row-level trigger. A statement-level trigger runs once for any and all rows affected by an INSERT, UPDATE, or DELETE statement. A row-level trigger runs once for each row affected by an INSERT, UPDATE, or DELETE statement.

Both of these triggers have two components – a trigger and a trigger body. The trigger defines what event to manage and the trigger body implements the logic that manages the event.

Statement-Level Triggers

You should create two tables to work with statement-level triggers. The first is the avenger table and the second is the avenger_log table.  Your inserts, updates, and deletes to the avenger table act as events that fire triggers. Statement-level triggers can be defined to run before or after INSERT, UPDATE, and DELETE statements. Statement-level triggers are narrowly scoped events and they log message data to the avenger_log table.

This is the definition of the avenger table:

SQL> CREATE TABLE avenger
  2  ( avenger_id    NUMBER
  3  , avenger_name  VARCHAR2(30)
  4  , first_name    VARCHAR2(20)
  5  , last_name     VARCHAR2(20));

This is the definition of the avenger_log table:

SQL> CREATE TABLE avenger_log
  2  ( avenger_log_id  NUMBER
  3  , trigger_name    VARCHAR2(30)
  4  , trigger_timing  VARCHAR2(6)
  5  , trigger_event   VARCHAR2(6)
  6  , trigger_type    VARCHAR2(12));

The following avenger_t1 creates a BEFORE INSERT statement trigger:

SQL> CREATE OR REPLACE TRIGGER avenger_t1
  2    BEFORE INSERT ON avenger
  3  DECLARE
  4    /* Declare local trigger-scope variables. */
  5    lv_sequence_id    NUMBER := avenger_log_s.NEXTVAL;
  6    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T1';
  7    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  8    lv_trigger_type   VARCHAR2(12) := 'STATEMENT';
  9    lv_trigger_timing VARCHAR2(6) := 'BEFORE';
 10  BEGIN
 11    /* Log event into the avenger_log table. */
 12    INSERT INTO avenger_log
 13    ( avenger_log_id
 14    , trigger_name
 15    , trigger_event
 16    , trigger_type
 17    , trigger_timing )
 18    VALUES
 19    ( lv_sequence_id
 20    , lv_trigger_name
 21    , lv_trigger_event
 22    , lv_trigger_type
 23    , lv_trigger_timing );
 24  END avenger_t1;
 25  /

Lines 1 and 2 declare the trigger. Lines 3 through 24 implements an anonymous PL/SQL block as the trigger’s body, and line 25 executes the trigger. Lines 6 through 9 store literal values for the trigger’s name, event, type, and timing. The trigger uses these literal values when logging events to the avenger_log table.

You access the data catalog information about triggers in the DBA_, ALL_, and USER_TRIGGERS views in a non-containerized database. Triggers also exist in those views for containerized databases (CDB). CDBs have an additional CDB_TRIGGERS view that stores triggers. The trigger body is stored in the TRIGGER_BODY column of those views in a LONG column.

You can create an AFTER STATEMENT trigger by simply changing the first two lines or the trigger declaration, as follows:

SQL> CREATE OR REPLACE TRIGGER avenger_t1
  2    AFTER INSERT ON avenger

Then, you need to change values of the string literals on lines 6, 7, and 9 as follows:

  6    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T2';
  7    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  8    lv_trigger_type   VARCHAR2(12) := 'STATEMENT';
  9    lv_trigger_timing VARCHAR2(6) := 'AFTER';

Compiling the database triggers, let’s insert a row into the avenger table, like this:

SQL> INSERT INTO avenger
  2  VALUES
  3  ( avenger_s.NEXTVAL
  4  ,'Captain America'
  5  ,'Steven'
  6  ,'Rogers');

Then, you can query the avenger_log table, like this:

SQL> COLUMN avenger_log_id FORMAT 999 HEADING "Avenger|Log ID #"
SQL> COLUMN trigger_name   FORMAT A12 HEADING "Trigger|Name"
SQL> COLUMN trigger_timing FORMAT A7  HEADING "Trigger|Timing"
SQL> COLUMN trigger_event  FORMAT A7  HEADING "Trigger|Event"
SQL> COLUMN trigger_type   FORMAT A12 HEADING "Trigger|Type"
SQL> SELECT * FROM avenger_log;

It returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:

Avenger TRIGGER      TRIGGER TRIGGER TRIGGER
LOG ID # Name         TIMING  Event   TYPE
-------- ------------ ------- ------- ----------
       1 AVENGER_T2   AFTER   INSERT  STATEMENT
       2 AVENGER_T1   BEFORE  INSERT  STATEMENT

Both of the triggers use the avenger_log_s1 sequence. You may notice that the AFTER STATEMENT trigger ran before the BEFORE STATEMENT trigger. That shows you that triggers aren’t sequenced by default, even when you think that the timing event should sequence them.

Oracle lets you sequence triggers by using the FOLLOWS clause when you define database triggers. The following modifies the avenger_t2 by adding a FOLLOWS clause on line 3, and it uses ellipses to shorten the example:

SQL> CREATE OR REPLACE TRIGGER avenger_t2
  2    BEFORE INSERT ON avenger
  3    FOLLOWS avenger_t1
  4  DECLARE
  …
 11  BEGIN
 …
 25  END avenger_t2;
 26  /

The testing script drops and creates the avenger_log table before creating fresh copies of the avenger_t1 and avenger_t2 triggers. The script lets you re-query the avenger_log table without the baggage of the previous two rows.

Like before, it returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:

Avenger TRIGGER      TRIGGER TRIGGER TRIGGER
LOG ID # Name         TIMING  Event   TYPE
-------- ------------ ------- ------- -----------
       1 AVENGER_T1   BEFORE  INSERT  STATEMENT
       2 AVENGER_T2   AFTER   INSERT  STATEMENT

You should note that the BEFORE STATEMENT trigger now runs before the AFTER STATEMENT trigger. The FOLLOWS clause lets you guarantee the order of trigger execution.

As you can see, statement-level triggers don’t give us the ability to see, change, or log the before and after values of data. You can do that with row-level triggers.

Row-Level Triggers

Row-level triggers let you see the initial column values you add into a table with an INSERT statement. Row-level triggers let you see existing column values and the column values provided by an UPDATE statement. The DELETE statement only provides the existing column values to a trigger because it removes the row from the database. Inside the row-level trigger you can change new values based on rules that you put in code inside the database trigger.

The avenger_log table requires major changes to support a row-level database trigger because it needs to store the old and new values of a table’s data column. Data columns hold values that describe an instance of the table. A data column or set of data columns should also define a unique key that makes each row unique in a table.

After engineering a table, you should also add a surrogate key column. A surrogate (stand-in) key column contains a value generated from a sequence, and a surrogate key is generally unrelated to the subject of a table. You use the natural key to find a unique row in a table, and you copy the surrogate key value when you want a foreign key to link another row with the row identified by the surrogate key.
Both the surrogate key column and natural key (one or more columns) should both identify unique rows. That means for every surrogate key there should be a natural key.

The data columns in the avenger table are the avenger_name, first_name, and last_name columns. You should define an old and new column for each of the data columns when you create a logging table.

This defines the new avenger_log table:

SQL> CREATE TABLE avenger_log
  2  ( avenger_log_id    NUMBER
  3  , trigger_name      VARCHAR2(30)
  4  , trigger_timing    VARCHAR2(6)
  5  , trigger_event     VARCHAR2(6)
  6  , trigger_type      VARCHAR2(12)
  7  , old_avenger_name  VARCHAR2(20)
  8  , old_first_name    VARCHAR2(20)
  9  , old_last_name     VARCHAR2(20)
 10  , new_avenger_name  VARCHAR2(20)
 11  , new_first_name    VARCHAR2(20)
 12  , new_last_name     VARCHAR2(20));

The first row-level database trigger you create runs when an INSERT statement adds a new row to the avenger table. The code exists below:

SQL> CREATE OR REPLACE TRIGGER avenger_t3
  2    BEFORE INSERT ON avenger
  3    FOR EACH ROW
  4  DECLARE
  5    /* Declare local trigger-scope variables. */
  6    lv_sequence_id    NUMBER := avenger_log_s.NEXTVAL;
  7    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T3';
  8    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  9    lv_trigger_type   VARCHAR2(12) := 'FOR EACH ROW';
 10    lv_trigger_timing VARCHAR2(6) := 'BEFORE';
 11  BEGIN
 12    /* Log event into the avenger_log table. */
 13    INSERT INTO avenger_log
 14    ( avenger_log_id
 15    , trigger_name
 16    , trigger_event
 17    , trigger_type
 18    , trigger_timing
 19    , old_avenger_name
 20    , old_first_name
 21    , old_last_name
 22    , new_avenger_name
 23    , new_first_name
 24    , new_last_name )
 25    VALUES
 26    ( lv_sequence_id
 27    , lv_trigger_name
 28    , lv_trigger_event
 29    , lv_trigger_type
 30    , lv_trigger_timing
 31    , :old.avenger_name
 32    , :old.first_name
 33    , :old.last_name
 34    , :NEW.avenger_name
 35    , :NEW.first_name
 36    , :NEW.last_name );
 37  END avenger_t3;
 38  /

Line 3 declares the avenger_t3 trigger as a row-level trigger. Lines 31 through 36 inserts the old and new values from the row of the avenger table when the INSERT statement runs with the following three values:

SQL> INSERT INTO avenger
  2  VALUES
  3  ( avenger_s.NEXTVAL
  4  ,'Capt. America'
  5  ,'Steven'
  6  ,'Rogers');

Since the script drops and recreates the avenger and avenger_log tables and drops the avenger_t1 and avenger_t2 statement-level triggers, you can write a query to return only the test row. The following anonymous PL/SQL block let’s you print the old and new column values next to one another. The program helps make the row-level trigger’s ability to see before and after values clear.

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> BEGIN
  2    FOR i IN (SELECT * FROM avenger_log) LOOP
  3      dbms_output.put_line(
  4         'Trigger Name   ['
  5       || i.trigger_name||']');
  6      dbms_output.put_line(
  7         'Trigger Event  ['
  8       || i.trigger_event||']');
  9      dbms_output.put_line(
 10         'Trigger Type   ['
 11       || i.trigger_type||']');
 12      dbms_output.put_line(
 13         'Trigger Timing ['
 14       || i.trigger_timing||']');
 15      dbms_output.put_line(
 16         'Avenger Name   ['
 17       || i.old_avenger_name||']['
 18        || i.new_avenger_name||']');
 19      dbms_output.put_line(
 20         'First Name     ['
 21        || i.old_first_name||']['
 22        || i.new_first_name||']');
 23      dbms_output.put_line(
 24         'Last Name      ['
 25        || i.old_last_name||']['
 26        || i.new_last_name||']');
 27    END LOOP;
 28  END;
 29  /

This anonymous block prints the following from the avenger_log table:

TRIGGER Name   [AVENGER_T3]
TRIGGER Event  [INSERT]
TRIGGER TYPE   [FOR EACH ROW]
TRIGGER TIMING [BEFORE]
Avenger Name   [][Capt. America]
FIRST Name     [][Steven]
LAST Name      [][Rogers]

This has demonstrated how you write a row-level trigger against an INSERT event. You should note that the old values for the avenger_name, first_name, and last_name are null values between the square brackets. Next, you should examine how to write a row-level trigger against more than one type of event.

The Oracle Database lets you write individual triggers for INSERT, UPDATE, or DELETE statement, or a single trigger to manage INSERT, UPDATE, and DELETE events. The following modifies the avenger_t3 trigger so that it works for an INSERT, UPDATE, and DELETE events:

SQL> CREATE OR REPLACE TRIGGER avenger_t3
  2    BEFORE INSERT OR UPDATE OR DELETE ON avenger
  3    FOR EACH ROW

Line 2 of the previous trigger is where we change the avenger_t3 trigger to also work with UPDATE and DELETE events. Then, we need to change one other line and then add a small IF-block to the trigger.

Line 8 of the original trigger assigns a default value to the lv_trigger_event variable, but you need to remove the value assignment. The modified line looks like this:

  8    lv_trigger_event  VARCHAR2(6);

You also need to add an IF-block that manages Data Manipulation Language (DML) event functions. The IF-block should be the first thing in the execution block of the trigger body, and it should implement this logic:

 11  BEGIN
 12    /* Evaluate and assign event for logging. */
 13    IF   INSERTING THEN lv_trigger_event := 'INSERT';
 14    ELSIF UPDATING THEN lv_trigger_event := 'UPDATE';
 15    ELSIF DELETING THEN lv_trigger_event := 'DELETE';
 16    END IF;
 ...

The INSERTING event function on line 13 occurs when an INSERT statement activates the trigger. The UPDATING and DELETING event functions on lines 14 and 15 occur when a respective UPDATE or DELETE statement activity fires a trigger.

The following UPDATE statement now creates an event that the avenger_t3 trigger is monitoring:

SQL> UPDATE avenger
  2  SET    avenger_name = 'Captain America'
  3  WHERE  avenger_name = 'Capt. America';

Next, let’s test a DELETE statement with the following:

SQL> DELETE
  2  FROM    avenger
  3  WHERE   avenger_name = 'Captain America';

The following anonymous block program lets you see the log values inserted into the avenger_log table from the INSERT, UPDATE, and DELETE statement triggers:

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> BEGIN
  2    FOR i IN (SELECT * FROM avenger_log) LOOP
  3      dbms_output.put_line(
  4         'Row Number     ['
  5       || i.avenger_log_id ||']['
  6       || i.trigger_event ||']');
  7      dbms_output.put_line(
  8         'Avenger Name   ['
  9       || i.old_avenger_name ||']['
 10        || i.new_avenger_name ||']');
 11      dbms_output.put_line(
 12         'First Name     ['
 13        || i.old_first_name ||']['
 14        || i.new_first_name ||']');
 15      dbms_output.put_line(
 16         'Last Name      ['
 17        || i.old_last_name ||']['
 18        || i.new_last_name ||']');
 19    END LOOP;
 20  END;
 21  /

The anonymous block returns the following:

Row Number     [1][INSERT]
Avenger Name   [][Capt. America]
First Name     [][Steven]
Last Name      [][Rogers]
Row Number     [2][UPDATE]
Avenger Name   [Capt. America][Captain America]
First Name     [Steven][Steven]
Last Name      [Rogers][Rogers]
Row Number     [3][DELETE]
Avenger Name   [Captain America][]
First Name     [Steven][]
Last Name      [Rogers][]

You should notice the old values for the INSERT event are missing because there isn’t a row before running the INSERT statement. Likewise, you should notice the new values for the DELETE event are missing because there isn’t a row after running a DELETE statement. Only the UPDATE event has both an old and new value because the row exists before and after any change. The old values hold the row’s values before the UPDATE statement and the new values hold the row’s values after the UPDATE statement.

Written by maclochlainn

November 25th, 2018 at 1:42 pm

Column Substitutability

without comments

Object Types and Column Substitutability

This article shows you how to use extend parent (or superclass) objects. You extend parent classes when you implement specialized behaviors (or methods) in subtypes. That’s because SQL statements can’t work with specialized methods when a table’s column stores subclasses in a superclass column type.

Substitutability is the process of storing subtypes in a super type column. It is a powerful feature of the Oracle database. The “type evolution” feature of the Oracle Database 12c release makes it more important because it makes it more flexible. The flexibility occurs because Oracle lets you evolve parent classes.

You evolve parent classes when you implement MEMBER functions or procedures, and you want to access them for all substitutable column values. That’s necessary because you need to define the MEMBER function or procedure in the column’s base object type. Prior to Oracle Database 12c, you couldn’t change (evolve) a base type. If you’re new to the idea of object types and subtypes, you may want to check out my earlier “Object Types and Subtypes” article.

Before discussing the complexity of creating and evolving object types to support column substitutability, let’s create a base_t object type. The base_t object type will become our root node object type. A root node object type is our most general object type. A root node is also the topmost node of an inverted tree of object types. All subtypes of the root node become child nodes, and child nodes without their own children are at the bottom of the tree and they’re leaf nodes.

The following creates the base_t object type. It is similar to object types that I use in related articles to keep ideas consistent and simple across the articles. This version of the base_t object doesn’t try to maintain an internal unique identifier because the table maintains it as a surrogate key.

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 oname attribute on line two holds the name of the object type. Lines 4 and 5 define the default constructor, which has no formal parameters. Line 6 defines an accessor method, or getter, and line 7 defines a mutator, or setter. Line 8 defines a traditional to_string method that lets you print the contents of the object type.

Next, let’s implement the base_t object type’s 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  /

Line 7 assigns a literal value to the oname attribute. Line 24 returns the value of the oname attribute for the instance. The remainder of the object type is generic. You can read about the generic features in my “Object Types and Bodies Basics” and about accessor and mutator methods in my “Object Types with Getters and Setters” articles.

Let’s define and implement a hobbit_t subtype of our base_t object type. The hobbit_t object type is:

SQL> CREATE OR REPLACE TYPE hobbit_t UNDER base_t
  2  ( genus VARCHAR2(20)
  3  , name   VARCHAR2(20)
  4  , CONSTRUCTOR FUNCTION hobbit_t
  5    ( genus VARCHAR2
  6    , name   VARCHAR2) RETURN SELF AS RESULT
  7  , MEMBER FUNCTION get_genus RETURN VARCHAR2
  8  , MEMBER FUNCTION get_name RETURN VARCHAR2
  9  , MEMBER PROCEDURE set_genus (genus VARCHAR2)
 10  , MEMBER PROCEDURE set_name (name VARCHAR2)
 11  , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2)
 12    INSTANTIABLE NOT FINAL;
 13  /

Lines 2 and 3 add two new genus and name attributes to the hobbit_t subtype. The hobbit_t subtype also inherits the oname attribute from its parent base_t type. Lines 7 and 8 define two getters and lines 9 and 10 define two setters, which support the genus and name attributes of the hobbit_t subtype. The hobbit_t object type’s getter and setters are unique to the subtype. They are also a specialization of the base_t object type. As such, these getters and setters are inaccessible to instances of the base_t object type. Line 11 defines an overriding to_string function for the base_t type’s to_string function.

The following implements the hobbit_t object body:

SQL> CREATE OR REPLACE TYPE BODY hobbit_t IS
  2    /* A default constructor with two formal parameters. */
  3    CONSTRUCTOR FUNCTION hobbit_t
  4    ( genus VARCHAR2
  5    , name   VARCHAR2 )
  6    RETURN SELF AS RESULT IS
  7      BEGIN
  8        self.oname := 'HOBBIT_T';
  9        self.name := name;
 10        self.genus := genus;
 11        RETURN;
 12      END;
 13    /* An accessor, or getter, method. */
 14    MEMBER FUNCTION get_genus RETURN VARCHAR2 IS
 15      BEGIN
 16        RETURN self.genus;
 17      END get_genus;
 18    /* An accessor, or getter, method. */
 19    MEMBER FUNCTION get_name RETURN VARCHAR2 IS
 20      BEGIN
 21        RETURN self.name;
 22      END get_name;
 23    /* A mutator, or setter, method. */
 24    MEMBER PROCEDURE set_genus
 25    ( genus VARCHAR2 ) IS
 26      BEGIN
 27        self.genus := genus;
 28      END set_genus;
 29    /* A mutator, or setter, method. */
 30    MEMBER PROCEDURE set_name
 31    ( name VARCHAR2 ) IS
 32      BEGIN
 33        self.name := name;
 34      END set_name;
 35      /* A to_string conversion method. */
 36    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
 37      BEGIN
 38        /* Uses general invocation on parent to_string
 39           function. */
 40       RETURN (self AS base_t).to_string
 41              || '['||self.genus||']['||self.name||']';
 42    END to_string;
 43  END;
 44  /

Lines 4 and 5 list the parameters for the hobbit_t constructor. Line 8 assigns a literal value to the oname attribute of the base_t object type. Lines 9 and 10 assign the formal parameters to the genus and name attributes of the hobbit_t subtype. Line 40 uses a general invocation statement to call the base_t’s to_string function.

You can now create a table that has a substitutable column that uses the base_t parent object type. The Oracle database assumes object type columns are substitutable at all levels, unless you turn off a column’s substitutability.

The following creates a tolkien table, and it has only two columns. One column has a NUMBER data type and the other has a user-defined object type. The base_t object type column is substitutable at all levels:

SQL> CREATE TABLE tolkien
  2  ( tolkien_id NUMBER
  3  , character   BASE_T );

You create a tolkien_s sequence for the unique tolkien_id column with the following:

SQL> CREATE SEQUENCE tolkien_s START WITH 1001;

You can insert one base_t and two hobbit_t object types with the following INSERT statements:

SQL> INSERT INTO tolkien VALUES
  2  ( tolkien_s.NEXTVAL, base_t() );
SQL> INSERT INTO tolkien VALUES
  2  ( tolkien_s.NEXTVAL, hobbit_t('HOBBIT','Bilbo') );
SQL> INSERT INTO tolkien VALUES
  2  ( tolkien_s.NEXTVAL, hobbit_t('HOBBIT','Frodo') );

The following simple query shows you the unique identifier in the tolkien_id column and collapsed object types in the character column of the tolkien table:

SQL> COLUMN character FORMAT A40
SQL> SELECT   tolkien_id
  2  ,        character
  3  FROM     tolkien;

It should display the following:

TOLKIEN_ID CHARACTER(ONAME)
---------- ----------------------------------------
     1001 BASE_T('BASE_T')
     1002 HOBBIT_T('HOBBIT_T', 'HOBBIT', 'Bilbo')
     1003 HOBBIT_T('HOBBIT_T', 'HOBBIT', 'Frodo')

Oracle always stores object instances as collapsed object instances in tables. You need to use the TREAT function in SQL to read instances of an object type.

The TREAT function lets you place in memory an instance of an object type. The TREAT function requires that you designate the type of object instance. If you want the TREAT function to work with all rows of the table, you designate the column’s object type as the base (or superclass) type. Designating a subtype to work like a parent, grandparent, or any antecedent type is a form of casting. Though casting in this case is actually dynamic dispatch.

Dynamic dispatch lets you pass any subtype as a parent or antecedent type. Dynamic dispatch inspects the object and treats it as a unique object.

The following query uses the TREAT function to read the parent and any subtype of the parent object type:

SQL> COLUMN to_string FORMAT A40
SQL> SELECT tolkien_id
  2  ,      TREAT(character AS BASE_T).to_string() AS to_string
  3  FROM   tolkien;

It prints the oname attribute for base_t instances and the oname, genus, and name attributes for hobbit_t instances, like

TOLKIEN_ID TO_STRING
---------- ---------------------------
     1001 [BASE_T]
     1002 [BASE_T]
     1003 [HOBBIT_T][HOBBIT][Bilbo]
     1004 [HOBBIT_T][HOBBIT][Frodo]

The TREAT function manages dynamic dispatch but requires any specialized method of a subtype to exist in the parent or antecedent type to which it is cast. Any query can cast to the root or an intermediate parent subtype. The TREAT function raises an exception when you don’t have an equivalent method stub (definition) in the parent or antecedent type.

For example, let’s modify the previous query and change the method call on line 2 from the to_string function to the get_name function. The new query is:

SQL> COLUMN to_string FORMAT A40
SQL> SELECT tolkien_id
  2  ,      TREAT(character AS BASE_T).get_name() AS get_name
  3  FROM   tolkien;

It fails with the following error:

,       TREAT(character AS BASE_T).get_name() AS get_name
                                           *
ERROR at line 2:
ORA-00904: "STUDENT"."BASE_T"."GET_NAME": invalid identifier

The reason for the failure is interesting. It occurs because the get_name function is only part of the hobbit_t subtype and can’t be found as an identifier inside the base_t object type. PL/SQL identifiers are: reserved or key words; predefined identifiers; quoted identifiers; user-identifiers; and user-defined variables, subroutine, and data or object type names.

You can access the MEMBER functions or procedures (method) of a subtype when you cast to a parent type provided you meet two conditions. First, you must implement the MEMBER method in the subtype. Second, you must define the same MEMBER method in the parent type.

Accessing a subtype MEMBER method differs from general invocation. General invocation occurs when you call a MEMBER method from a parent or antecedent type from a subtype’s OVERRIDING MEMBER method. Oracle doesn’t explain how you call a subtype’s method from a parent or antecedent type but there is a close corollary – packages.

For example, you can only call a package function or procedure from another PL/SQL block when you’ve defined it in the package specification. This means you need to implement a stub for the get_name function inside the base_t object type because it acts as the specification.

You add a get_name function to the base_t object type in the next example:

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_name RETURN VARCHAR2
  7    , MEMBER FUNCTION get_oname RETURN VARCHAR2
  8    , MEMBER PROCEDURE set_oname (oname VARCHAR2)
  9    , MEMBER FUNCTION to_string RETURN VARCHAR2)
 10    INSTANTIABLE NOT FINAL;
 11  /

Line 6 adds the get_name function to the base_t object type. The following shows you how to implement get_name function stub in the object type body:

SQL> CREATE OR REPLACE
  2    TYPE BODY base_t IS
  3    CONSTRUCTOR FUNCTION base_t
  4    RETURN SELF AS RESULT IS
  5      BEGIN
  6        self.oname := 'BASE_T';
  7      RETURN;
  8    END;
  9    MEMBER FUNCTION get_name RETURN VARCHAR2 IS
 10    BEGIN
 11      RETURN NULL;
 12    END get_name;
 13    MEMBER FUNCTION get_oname RETURN VARCHAR2 IS
 14    BEGIN
 15      RETURN self.oname;
 16    END get_oname;
 17    MEMBER PROCEDURE set_oname
 18    ( oname VARCHAR2 ) IS
 19    BEGIN
 20      self.oname := oname;
 21    END set_oname;
 22    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
 23    BEGIN
 24      RETURN '['||self.oname||']';
 25    END to_string;
 26  END;
 27  /

Lines 9 through 12 implement the get_name function stub. You should note that it returns a null value because the name attribute doesn’t exist in the root node (base_t) object type.

The change to the hobbit_t object type is simpler. All you need to do is add the OVERRIDING keyword before the get_name member function in the hobbit_t object type and body. With that change, you can successfully run the following query:

SQL> COLUMN get_name FORMAT A20
SQL> SELECT tolkien_id
  2  ,      TREAT(character AS BASE_T).get_name() AS get_name
  3  FROM   tolkien;

It now works and prints:

TOLKIEN_ID GET_NAME
---------- --------------------
     1001
     1002 Bilbo
     1003 Frodo

This article showed you how to extend parent object types. It also showed you how to modify parent types to support generalized calls with the TREAT function. Together these principles show you how to leverage substitutability on columns.

Written by maclochlainn

November 24th, 2018 at 1:09 am