Archive for the ‘pl/sql’ Category
Oracle Trigger Basics
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.
Column Substitutability
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.
Types & Subtypes
Object Types and Subtypes
This article teaches you how to use subtypes or subclasses. You can define an object type with or without dependencies. Object types can have two types of dependencies. The simplest case occurs when you define an object attribute with an object type instead of a data type. The more complex case occurs when you define an object subtype because it inherits the behavior of the base object type. The base object type is a superclass and a parent class. The subtype is a subclass and a child class.
The ability to capture various result sets is a key use case for object types and subtypes. That’s because you can define a table’s column with the object type, and then you can store in that column the object type or any of its subtypes.
A base object type should contain a unique identifier and an object name. The Object Types & Bodies Basic article explains the best practice for unique identifiers. It suggests that you populate the unique ID value with a no argument constructor function. The object name attribute should hold the object type name.
I’d like to suggest we consider base_t as the name of our superclass. You can define a base_t object type like this:
SQL> CREATE OR REPLACE 2 TYPE base_t IS OBJECT 3 ( obj_id NUMBER 4 , obj_name VARCHAR2(30) 5 , CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT 6 , MEMBER FUNCTION to_string RETURN VARCHAR2) 7 INSTANTIABLE NOT FINAL; 8 / |
Line 2 and 3 define two attributes. They are the unique identifier, or ID, and the object. The no argument constructor function assigns values to the obj_id and obj_name attributes. It assigns the base_t_s sequence value to the obj_id attribute and it assigns a string literal to the obj_name attribute. The to_string member function returns a concatenated string of the obj_id and obj_name values. The return value of the to_string function is what you want to disclose about the contents of an object type.
Line 7 declares the class as instantiable and not final. You can create an instance of a class when its instantiable, and you can create subtypes of a type when it’s NOT FINAL.
You need to create the base_t_s sequence before we can compile the base_t object body. The following statement creates the base_t_s sequence as a set of values starting at 1:
SQL> CREATE SEQUENCE base_t_s; |
The object body for the base_t object type is:
SQL> CREATE OR REPLACE 2 TYPE BODY base_t IS 3 4 /* Default constructor. */ 5 CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS 6 BEGIN 7 /* Assign a sequence value and string literal 8 to the instance. */ 9 self.obj_id := base_t_s.NEXTVAL; 10 self.obj_name := 'BASE_T'; 11 RETURN; 12 END; 13 14 /* A text output function. */ 15 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 16 BEGIN 17 RETURN 'UID#: ['||obj_id||']'||CHR(10) 18 || 'Type: ['||obj_name||']'; 19 END; 20 / |
Line 9 assigns a base_t_s sequence value to the obj_id attribute, which serves as a unique identifier. Line 10 assigns a string literal to the obj_name attribute. The obj_name attribute identifies the object type. Line 17 and 18 prints the contents of the base_t object type as a two-row string.
You can test the construction of the base_t object type with this query:
SQL> SELECT base_t() FROM dual; |
It displays:
BASE_T()(OBJ_ID, OBJ_NAME) ---------------------------- BASE_T(1, 'BASE_T') |
Alternatively, you can test the to_string member function with the TREAT function, like:
SQL> SELECT TREAT(base_t() AS base_t).to_string() AS "Text" 2 FROM dual; |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
Alternatively, you can test to_string member function with an anonymous block (by enabling SERVEROUTPUT):
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED SQL> BEGIN 2 dbms_output.put_line(base_t().to_string); 3 END; 4 / |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
There’s another way to query the object instance with a query. While I don’t think it’s effective for this situation, you should know how the syntax works. It requires that you create a collection of the base_t object type, which you can do with this syntax:
SQL> CREATE OR REPLACE 2 TYPE base_t_tab IS TABLE OF base_t; 3 / |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
You can query the base_t object type from inside a collection by using the CAST and COLLECT functions. The COLLECT function puts a single object instance into a base_t_tab collection. The CAST function puts the generic collection into a specific collection.
The syntax to perform this operation is:
SQL> COLUMN obj_id FORMAT 9999 SQL> COLUMN obj_name FORMAT A20 SQL> SELECT * 2 FROM TABLE(SELECT CAST(COLLECT(base_t()) AS base_t_tab) 3 FROM dual); |
Assuming the base_t_s sequence holds a current value of 3, the query returns:
OBJ_ID OBJ_NAME ------ -------------------- 5 BASE_T |
This type of query isn’t too useful in day-to-day programming. It’s more of a corner use case for testing an object type with a sequence value. While you expect an obj_id value of 4, the query returns a value of 5. Somewhere in the execution Oracle appears to call the sequence twice.
The COLLECT and TREAT functions increment the value of sequence when you put them inside object types. So, you shouldn’t use a sequence as a unique identifier inside an object type. I plan to cover the better approach in subsequent article.
Now that you have a solid base_t object, let’s create a hobbit_t subtype. The hobbit_t subtype adds one attribute to the two attributes in the base_t object type.
The following declares the hobbit_t object type as a subtype and overrides the to_string member function:
SQL> CREATE OR REPLACE 2 TYPE hobbit_t UNDER base_t 3 ( hobbit_name VARCHAR2(30) 4 , CONSTRUCTOR FUNCTION hobbit_t 5 ( hobbit_name VARCHAR2 ) RETURN SELF AS RESULT 6 , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) 7 INSTANTIABLE NOT FINAL; 8 / |
Assuming the base_t_s sequence holds a current value of 3, the query returns:
OBJ_ID OBJ_NAME ------ -------------------- 5 BASE_T |
Line 2 declares the hobbit_t subtype as UNDER the base_t object type. There isn’t a no argument constructor that mirrors the parent base_t object type. You also can’t call the parent type’s constructor like they do in Java.
Line 4 and 5 declare a single argument constructor. The hobbit_t object type’s constructor assigns values to the obj_id and obj_name attributes. More or less it performs the same function as its parent’s constructor. Then, the constructor assigns the parameter value to the hobbit_name attribute of the hobbit_t object type.
Line 6 declares an overriding to_string member function. The overriding to_string member function replaces the behavior of our parent class. It provides the subclass with its own a specialized behavior.
You implement the hobbit_t object type like this:
SQL> CREATE OR REPLACE 2 TYPE BODY hobbit_t IS 3 4 /* One argument constructor. */ 5 CONSTRUCTOR FUNCTION hobbit_t 6 ( hobbit_name VARCHAR2 ) RETURN SELF AS RESULT IS 7 BEGIN 8 /* Assign a sequence value and string literal 9 to the instance. */ 10 self.obj_id := base_t_s.NEXTVAL; 11 self.obj_name := 'HOBBIT_T'; 12 13 /* Assign a parameter to the subtype only attribute. */ 14 self.hobbit_name := hobbit_name; 15 RETURN; 16 END; 17 18 /* An output function. */ 19 OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS 20 BEGIN 21 RETURN (self AS base_t).to_string||CHR(10) 22 || 'Name: ['||hobbit_name||']'; 23 END; 24 END; 25 / |
Lines 10 assigns a sequence value to the obj_id attribute. Line 11 assigns a string literal to the obj_name attribute. Line 14 assigns the parameter value of the constructor to the hobbit_name attribute of the hobbit_t subtype. Line 21 is more complex than a simple assignment.
Line 21 contains a “generalized invocation” of the base_t object. A generalized invocation calls a parent or super class method. PL/SQL member functions or procedures are methods. Line 21 calls the base_t type’s to_string function. This way, the overriding to_string function returns a specialized result. It returns the result from the parent class and the value of its own hobbit_name attribute.
You can test the generalized invocation with the following query:
SQL> SELECT 2 TREAT( 3 hobbit_t('Bilbo') AS hobbit_t).to_string() AS "Text" 4 FROM dual; |
The query prints:
Text ----------------------- UID#: [1] Type: [HOBBIT_T] Name: [Bilbo] |
Together we’ve explored of how you create types and subtypes. You’ve learned a type is a generalization or superclass, and a subtype is a specialization or subclass. You’ve also learned how to create both a generalization and specialization. At this point, you may ask, “Why should I bother with subtypes?”
The benefit of subtypes is dynamic dispatch. Dynamic dispatch is the process of selecting an object type from an inverted tree of object types. The topmost object type is the root node or most generalized version of an object type. The bottom most object type is a leaf node or the most specialized version of an object type. All nodes between the root node and leaf nodes are simply nodes. Nodes become more specialized as you step down the hierarchy from the root node.
The process of selecting an object type from an inverted tree is polymorphism. Polymorphism means your program specifies the most general node at compile time. Then, the program accepts the root node or any subordinate nodes at runtime. Moreover, dynamic dispatch is like writing a function or procedure to do many things.
Another form of dynamic dispatch occurs when you overload a function or procedure in a PL/SQL package. Calls to overloaded functions or procedure choose which version to run based on the data types of the call parameters.
The key difference between overloading and selecting object types is simple. The first deals with choosing between different data types or object types. The second deals with choosing between object types in the same node tree.
You have two choices to demonstrate dynamic dispatch. One would use a SQL table or varray collection and the other would use column substitutability. Creating a table that uses substitutability seems the easiest approach.
The following creates a table of the base_t object type:
SQL> CREATE TABLE dynamic 2 ( character_type BASE_T ); |
You can now insert a base_t object type or any of the base_t subtypes. The base_t_s sequence is reset for the test case INSERT statements:
SQL> INSERT INTO dynamic VALUES (base_t()); SQL> INSERT INTO dynamic VALUES (hobbit_t('Bilbo Baggins')); SQL> INSERT INTO dynamic VALUES (hobbit_t('Peregrin Took')); |
The following query uses a CASE statement to identify whether the column returns a base_t or hobbit_t object type:
SQL> SELECT 2 CASE 3 WHEN TREAT(character_type AS hobbit_t) IS NOT NULL THEN 4 TREAT(character_type AS hobbit_t).to_string() 5 ELSE 6 TREAT(character_type AS base_t).to_string() 7 END AS "Text" 8 FROM dynamic; |
The query returns the following:
Text ----------------------- UID#: [3] Type: [BASE_T] UID#: [7] Type: [HOBBIT_T] Name: [Bilbo Baggins] UID#: [13] Type: [HOBBIT_T] Name: [Peregrin Took] |
The result set shows you that the character_type column holds different types of the base_t object type. It should also show you how you may store different result logs from DML row level triggers in a single table. Another article, I hope to write soon.
The unique identifier appears to increment three times with the first INSERT statement and five times with subsequent inserts. Actually, each INSERT statement increments the sequence five times. A debug statement would show you that it assigns the third call to the .NEXTVAL pseudo column value to the obj_id value. This is true for both the base_t and hobbit_t object type, and any other derived subtypes.
This article has shown you how to implement object types and subtypes. It also has explained how dynamic dispatch works and it provides a working example of dynamic dispatch leveraging column substitutability.
Type Getters & Setters
Object Types with Getters and Setters
This article is for you when you know the basics about how you work Oracle’s object types. It teaches you how to write effective getters, setters, comparators, and static methods. Please read my Object Types & Bodies Basic article if you’re not sure how to work with object types.
Getters access an object instance and return values from an instance variable. Along with getters, you have setters. Setters let you assign a new value to an instance variable. Formally, getters are accessor methods and setters are mutator methods. PL/SQL implements getters as functions and setters as procedures. After all a PL/SQL procedure is like a function that returns a void data type in Java.
The Object Types & Bodies Basic article introduces a people_obj object type. This article extends the behavior of the people_obj type. Extends is a funny word because it can have different meanings in object-oriented programming. Here, extends means to add functionality.
The first things we’ll add are getters and setters for all the attributes of the object instance. We need to add them to the object type and body because Oracle implements objects like it does packages. The object type defines the published functions and procedures. The object body implements the published functions and procedures.
Here’s the new people_obj type with getters and setters:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT 8 , CONSTRUCTOR FUNCTION people_obj 9 ( first_name VARCHAR2 10 , middle_name VARCHAR2 DEFAULT NULL 11 , last_name VARCHAR2 ) RETURN SELF AS RESULT 12 , MEMBER FUNCTION get_people_id RETURN NUMBER 13 , MEMBER FUNCTION get_first_name RETURN VARCHAR2 14 , MEMBER FUNCTION get_middle_name RETURN VARCHAR2 15 , MEMBER FUNCTION get_last_name RETURN VARCHAR2 16 , MEMBER PROCEDURE set_first_name (pv_first_name VARCHAR2) 17 , MEMBER PROCEDURE set_middle_name (pv_first_name VARCHAR2) 18 , MEMBER PROCEDURE set_last_name (pv_first_name VARCHAR2)) 19 INSTANTIABLE NOT FINAL; 20 / |
The new getters and setters are on lines 12 through 18. The closing parenthesis for the list of attributes, functions, and procedures moves from line 11 to line 18. While there are four attributes in the people_obj type and four getters for those attributes, there are only three setters. The reason for the difference is simple. The people_id attribute is a unique identifier. You should never change the value of a unique identifier.
Next, lets implement the object body. I’m opting to show the complete object body because some readers may not check out the earlier article. Here’s the people_obj body:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 4 /* Default constructor. */ 5 CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT IS 6 7 /* Set a counter variable using a sequence. */ 8 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 9 10 BEGIN 11 /* Assign a sequence value to the instance. */ 12 self.people_id := lv_people_obj_s; 13 14 /* Return a constructed instance. */ 15 RETURN; 16 END people_obj; 17 18 /* Override constructor. */ 19 CONSTRUCTOR FUNCTION people_obj 20 ( first_name VARCHAR2 21 , middle_name VARCHAR2 DEFAULT NULL 22 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 23 24 /* Create a empty default instance. */ 25 people PEOPLE_OBJ := people_obj(); 26 27 BEGIN 28 /* Create the instance with the default constructor. */ 29 people.first_name := first_name; 30 people.middle_name := middle_name; 31 people.last_name := last_name; 32 33 /* Assign a local instance this instance. */ 34 self := people; 35 36 /* Return the current instance. */ 37 RETURN; 38 END people_obj; 39 40 /* Get people ID attribute. */ 41 MEMBER FUNCTION get_people_id RETURN NUMBER IS 42 BEGIN 43 RETURN self.people_id; 44 END get_people_id; 45 46 /* Get first name attribute. */ 47 MEMBER FUNCTION get_first_name RETURN VARCHAR2 IS 48 BEGIN 49 RETURN self.first_name; 50 END get_first_name; 51 52 /* Get middle name attribute. */ 53 MEMBER FUNCTION get_middle_name RETURN VARCHAR2 IS 54 BEGIN 55 RETURN self.middle_name; 56 END get_middle_name; 57 58 /* Get last name attribute. */ 59 MEMBER FUNCTION get_last_name RETURN VARCHAR2 IS 60 BEGIN 61 RETURN self.last_name; 62 END get_last_name; 63 64 /* Set first name attribute. */ 65 MEMBER PROCEDURE set_first_name 66 ( pv_first_name VARCHAR2 ) IS 67 BEGIN 68 self.first_name := pv_first_name; 69 END set_first_name; 70 71 /* Set middle name attribute. */ 72 MEMBER PROCEDURE set_middle_name 73 ( pv_middle_name VARCHAR2 ) IS 74 BEGIN 75 self.middle_name := pv_middle_name; 76 END set_middle_name; 77 78 /* Set last name attribute. */ 79 MEMBER PROCEDURE set_last_name 80 ( pv_last_name VARCHAR2 ) IS 81 BEGIN 82 self.last_name := pv_last_name; 83 END set_last_name; 84 END; 85 / |
The get_people_id member function on lines 41-44 returns the unique identifier for the object instance. The get_first_name member function on lines 47-50 returns the first_name attribute. The get_middle_name member function on lines 53-56 returns the middle_name attribute. The get_last_name member function on lines 59-62 returns the last_name attribute. Each of these getters returns an instance attribute. The self reserved word identifies the current instance of the object type.
The set_first_name member procedure on lines 65-69 assigns a value to the first_name attribute. The set_middle_name procedure on lines 72-76 assigns a value to the middle_name attribute. The set_last_name member procedures on lines 79-83 assigns a value to the last_name attribute. The constructor functions create instances of the people_obj and return them to the calling scope. Each of these setters assigns a value to an instance attribute.
Comparative functions are limited to the MAP and ORDER member functions. The MAP function only works with the CHAR, DATE, NUMBER, or VARCHAR2 data type. You could implement a MAP function against the last_name attribute but not the collection of the three variable length strings. You would implement an ORDER member function to compare the collection of strings.
You can define an equals MAP function in the people_obj object type like:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , MAP MEMBER FUNCTION equals RETURN VARCHAR2) 20 INSTANTIABLE NOT FINAL; 21 / |
After creating the people_obj object type, you can implement the following MAP function:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 85 /* Implement an equals MAP function. */ 86 MAP MEMBER FUNCTION equals RETURN VARCHAR2 IS 87 BEGIN 88 RETURN self.last_name; 89 END equals; 90 91 END; 92 / |
The MAP function is inadequate when you compare multiple attributes. You can implement an ORDER MEMBER function with the following syntax in the people_obj object type.
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , ORDER MEMBER FUNCTION equals 20 (pv_people PEOPLE_OBJ) RETURN NUMBER) 21 INSTANTIABLE NOT FINAL; 22 / |
The ORDER function is more complete than the MAP function. You can implement a last name, first name, and middle name ORDER function as follows:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 85 /* Implement an equals MAP function. */ 86 ORDER MEMBER FUNCTION equals 87 (pv_people PEOPLE_OBJ) RETURN NUMBER IS 88 BEGIN 89 IF NVL(self.last_name,'A') > NVL(pv_people.last_name,'A') THEN 90 RETURN 1; 91 ELSIF NVL(self.last_name,'A') = NVL(pv_people.last_name,'A') AND 92 NVL(self.first_name,'A') > NVL(pv_people.first_name,'A') THEN 93 RETURN 1; 94 ELSIF NVL(self.last_name,'A') = NVL(pv_people.last_name,'A') AND 95 NVL(self.first_name,'A') = NVL(pv_people.first_name,'A') AND 96 NVL(self.middle_name,'A') > NVL(pv_people.middle_name,'A') THEN 97 RETURN 1; 98 ELSE 99 RETURN 0; 100 END IF; 101 END equals; 102 END; 103 / |
The equals ORDER function on lines 86 through 101 checks for a three conditions. First, it checks whether the instance’s last_name is greater than the parameter object’s last_name. Second, it checks whether the last names are equal and the instance’s first_name is greater than the parameter object’s first_name. Finally, it checks whether the last and first names are equal and the middle_name is greater than the parameter object’s middle_name value.
Unfortunately, it’s hard to test this comparison without adding a to_string function. The to_string function prints the formatted name. You can add the to_string function to the object type like so:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , MAP MEMBER FUNCTION equals RETURN VARCHAR2 21 , MEMBER FUNCTION to_string RETURN VARCHAR2) 20 INSTANTIABLE NOT FINAL; 21 / |
Line 21 shows the declaration of the to_string function, and the following code snippet shows you the implementation of the to_string function:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 103 /* Create a to_string function. */ 104 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 105 BEGIN 106 RETURN self.last_name || ', ' || self.first_name || ' ' || 107 self.middle_name; 108 END to_string; 109 110 END; 111 / |
After assembling all the parts, we can test whether the ORDER comparative function works. The following anonymous block program declares a people_list collection that holds instances of the people_obj object type.
SQL> DECLARE 2 /* Declare an object type. */ 3 TYPE people_list IS TABLE OF people_obj; 4 5 /* Declare three object types. */ 6 lv_obj1 PEOPLE_OBJ := people_obj('Fred',NULL,'Maher'); 7 lv_obj2 PEOPLE_OBJ := people_obj('John',NULL,'Fedele'); 8 lv_obj3 PEOPLE_OBJ := people_obj('James',NULL,'Fedele'); 9 lv_obj4 PEOPLE_OBJ := people_obj('James','Xavier','Fedele'); 10 11 /* Declare a list of the object type. */ 12 lv_objs PEOPLE_LIST := people_list( lv_obj1, lv_obj2 13 , lv_obj3, lv_obj4); 14 15 /* Swap A and B. */ 16 PROCEDURE swap 17 ( a IN OUT PEOPLE_OBJ 18 , b IN OUT PEOPLE_OBJ ) IS 19 /* Declare a third variable. */ 20 c PEOPLE_OBJ; 21 BEGIN 22 /* Swap values. */ 23 c := b; 24 b := a; 25 a := c; 26 END swap; 27 28 BEGIN 29 /* Nested loop comparison. */ 30 FOR i IN 1..lv_objs.COUNT LOOP 31 FOR j IN 1..lv_objs.COUNT LOOP 32 IF lv_objs(i).equals(lv_objs(j)) = 0 THEN 33 swap(lv_objs(i), lv_objs(j)); 34 END IF; 35 END LOOP; 36 END LOOP; 37 38 /* Print the reordered list. */ 39 FOR i IN 1..lv_objs.COUNT LOOP 40 dbms_output.put_line(lv_objs(i).to_string()); 41 END LOOP; 42 END; 43 / |
The people_obj instances on lines 6 through 9 are out of order in the starting collection. The local swap procedure reorders them on lines 30 through 36. You would see the following output from the preceding anonymous block:
Fedele, James Fedele, James Xavier Fedele, John Maher, Fred |
All of our work in this paper so far shows you how to work with implementing functions and procedures in instances of object types. PL/SQL object types support MEMBER functions and procedures to work with object instances. PL/SQL object types also support STATIC functions and procedures. You use STATIC functions and procedures when you want to write and call a module in an object type that works like a function or procedure in a package.
You can call a STATIC function or procedure without creating an instance of an object. Creating an instance of the object type is a key use of STATIC functions. This approach is very much like how Oracle implements temporary BLOB and CLOB columns.
Here’s the snippet of additional code required in the people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 21 , MEMBER FUNCTION to_string RETURN VARCHAR2 22 , STATIC FUNCTION get_people_obj 23 ( pv_people_id NUMBER) RETURN people_obj) 20 INSTANTIABLE NOT FINAL; 21 / |
The get_people_obj function is a STATIC function and it takes a single number to return a name. It accomplishes this by using a parameterized cursor. You would implement the get_people_obj function like so:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 109 /* Create a get_people_obj function. */ 110 STATIC FUNCTION get_people_obj 111 ( pv_people_id NUMBER ) RETURN PEOPLE_OBJ IS 112 113 /* Implement a cursor. */ 114 CURSOR get_people_obj 115 ( cv_people_id NUMBER ) IS 116 SELECT first_name 117 , middle_name 118 , last_name 119 FROM contact 120 WHERE contact_id = cv_people_id; 121 122 /* Create a cursor variable. */ 123 lv_contact get_people_obj%ROWTYPE; 124 125 /* Create a temporary instance of people_obj. */ 126 lv_people_obj PEOPLE_OBJ; 127 BEGIN 128 /* Open, fetch and close cursor. */ 129 OPEN get_people_obj(pv_people_id); 130 FETCH get_people_obj INTO lv_contact; 131 lv_people_obj := people_obj( first_name => lv_contact.first_name 132 , middle_name => lv_contact.middle_name 133 , last_name => lv_contact.last_name); 134 CLOSE get_people_obj; 135 RETURN lv_people_obj; 136 END get_people_obj; 137 138 END; 139 / |
The get_people_obj function takes a single numeric parameter. The numeric parameter passes the primary key value for the contact table. Then, the STATIC function returns an instance of the people_obj object type. It accomplishes that feat by using the numeric value as a lookup key in the contact table, as you can see in the get_people_obj cursor on lines 114 through 120. The STATIC method opens, fetches a single row, and closes on lines 129 through 135.
Now you can call the get_people_obj function in a query and return an instance of people_obj. You can also use the to_string method to view the output, as follows:
SQL> SELECT people_obj.get_people_obj(1003).to_string() 2 FROM dual; |
It prints:
PEOPLE_OBJ.GET_PEOPLE_OBJ(1003).TO_STRING() --------------------------------------------- Vizquel, Oscar |
This article has shown you how to write effective getters, setters, comparators, and static methods. It also has shown how to test and work with Oracle object types and bodies.
Type & Body Basics
Object Types and Bodies Basics
Oracle Database 10g gave us a new way to write PL/SQL – object types. Object types are different from standard PL/SQL functions, procedures, and packages. While you can pin packages in memory, object types go one step further. You can instantiate them, which means you can start them, assign values to their variables, and put them into your PGA’s memory. Object types provide you with new challenges writing programs in the Oracle database.
Oracle Database 12c makes using object types simpler. That’s because Oracle Database 12c supports type evolution. Type evolution lets you change an object type when it has dependents. An object type’s dependents can be a table, another object type, function, procedure, or package. Oracle Database 12c also lets you white list the callers of an object type.
You define object types with variables and methods, like you define packages. Object type methods are either functions or procedures. You can implement object type functions and procedures as instance or static methods. An instance method works on the object type’s variable, whereas, static methods work like ordinary functions and procedures. That means static methods can’t access object type variables.
You learn how to define and implement basic object types and bodies in this article. This article shows you how to use and deploy objects and shows you how to implement the specialized CONSTRUCTOR functions.
The following declares a basic people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20)); 7 / |
The CREATE OR REPLACE is SQL syntax creates an object type, like you would create a PL/SQL function, procedure, or package. Lines 2 through 6 declare a four element people_obj object type, and the semicolon on line 6 acts as a statement terminator. The forward slash on line 7 executes the CREATE TYPE statement.
To most developers the foregoing syntax appears to declare a record data structure. There’s more to it than that. The CREATE TYPE syntax also creates an implicit constructor function. You can call the people_obj constructor with a list of parameter that matches both the list of element names and their data types. The call syntax supports both named and positional notation.
You can test the people_obj object type with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj(1,'John','Paul','Jones'); 3 BEGIN 4 dbms_output.put_line( people.first_name || ' ' 5 ||people.middle_name || ' ' 6 ||people.last_name); 7 END; 8 / |
Line 2 declares a variable of the object type with positional notation, and then it assigns an instance of the people_obj object type. On the right side of the assignment operator, a call to the constructor function creates an instance of the people_obj object type. Object construction has the highest order of precedence, which means it always creates the people_obj instance first.
Lines 4 through 6 print the values of the first, middle, and last name elements. These values are the instance values held by the peoplevariable. It prints:
John Paul Jones |
The following example shows you how to call the default people_obj constructor with named notation:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , middle_name => 'Paul' 4 , last_name => 'Jones' 5 , people_id => 2); 6 BEGIN ... 10 END; 11 / |
The named notation on lines 2 through 5 let us vary the order of the object attributes. Oracle raises the following exception if you pare the list of call parameters by removing one of them.
PLS-00306: wrong number or types of arguments in call to 'PEOPLE_OBJ' |
You can add one or more override constructor functions to the people_obj object type. The first override constructor example has two call parameters, and they are the first_name and last_name parameters.
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj 8 ( first_name VARCHAR2 9 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 10 INSTANTIABLE NOT FINAL; 11 / |
Lines 7 through 9 declare the override constructor function. This override constructor function doesn’t provide a value for the people_id attribute. The concept of an object having a unique identifier, or ID, is part of good object-oriented design practices.
An Oracle sequence can help us guarantee the unique ID. You can create a people_obj_s sequence for the people_obj with the following syntax:
SQL> CREATE SEQUENCE people_obj_s; |
You can use the people_obj_s sequence in the override constructor to generate the unique ID. The following code implements the modified people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 CONSTRUCTOR FUNCTION people_obj 4 ( first_name VARCHAR2 5 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 6 7 /* Set a counter variable using a sequence. */ 8 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 9 10 BEGIN 11 /* Create the instance with the default constructor. */ 12 self := people_obj( people_id => lv_people_obj_s 13 , first_name => first_name 14 , middle_name => NULL 15 , last_name => last_name ); 16 /* Return the current instance. */ 17 RETURN; 18 END people_obj; 19 END; 20 / |
Line 8 declares a local lv_people_obj_s variable, and it assigns the next value from the people_obj_s sequence. The local variable is necessary because you can’t put a call to the .NEXTVAL pseudo column inside a call to an object type constructor function.
The self key word on line 12 represents the instance of an object. You call the default constructor on lines 12 through 15. The default constructor takes a local variable, two parameter values, and a null value.
You can test the new people_obj with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , last_name => 'Jones'); 4 BEGIN 5 dbms_output.put_line( '['|| people.people_id ||'] ' 6 ||'['|| people.first_name ||'] ' 7 ||'['|| people.middle_name ||'] ' 8 ||'['|| people.last_name ||']'); 9 END; 10 / |
It prints
[1] [John] [] [Jones] |
Clearly, the handling of the middle_name attribute is suboptimal. Actually, it’s more or less a joke. However, it does give us an opportunity to show how to handle optional parameters in a constructor function.
You would change the people_obj object type by adding a parameter to the override constructor function, like
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj 8 ( first_name VARCHAR2 9 , middle_name VARCHAR2 DEFAULT NULL 10 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 11 INSTANTIABLE NOT FINAL; 12 / |
There are only two changes to the implementation of the people_obj object body. One changes the list of parameters in the constructor function. The other replaces the null assignment with a parameter value from the overriding constructor function.
Here’s the implementation of the new people_obj object body:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 CONSTRUCTOR FUNCTION people_obj 4 ( first_name VARCHAR2 5 , middle_name VARCHAR2 DEFAULT NULL 6 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 7 8 /* Set a counter variable using a sequence. */ 9 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 10 11 BEGIN 12 /* Create the instance with the default constructor. */ 13 self := people_obj( people_id => lv_people_obj_s 14 , first_name => first_name 15 , middle_name => middle_name 16 , last_name => last_name ); 17 /* Return the current instance. */ 18 RETURN; 19 END people_obj; 20 END; 21 / |
Line 5 specifies the middle_name parameter as an optional parameter. The optional parameter in the middle of the list can present a problem when you make call to it with positional notation. A call with named notation on the other hand works without a hitch. Line 15 replaces the null value with the middle_name parameter from the constructor function.
You can test the modified people_obj with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , last_name => 'Jones'); 4 5 BEGIN 6 dbms_output.put_line( '['|| people.people_id ||'] ' 7 ||'['|| people.first_name ||'] ' 8 ||'['|| people.middle_name ||'] ' 9 ||'['|| people.last_name ||']'); 10 END; 11 / |
It prints
[1] [John] [] [Jones] |
If you modify the constructor call on lines 2 through 4, as follows:
2 people PEOPLE_OBJ := people_obj( first_name => 'James' 3 , middle_name => 'Wilson' 4 , last_name => 'Jones'); |
It prints
[1] [John] [Wilson] [Jones] |
There are still several problems with the current people_obj object type. The largest shortfall is that there’s no traditional default constructor. In many object-oriented language, a default constructor is a null argument constructor. A null argument constructor let’s you position logic that all other constructors can leverage.
A sequence value is an example of logic that you can share across constructor functions. The following version of the people_obj object type declares a standard no argument constructor function:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT 8 , CONSTRUCTOR FUNCTION people_obj 9 ( first_name VARCHAR2 10 , middle_name VARCHAR2 DEFAULT NULL 11 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 12 INSTANTIABLE NOT FINAL; 13 / |
Line 7 holds the declaration of a no argument constructor. The following people_obj object type implements a no argument constructor. The object body also makes access to the sequence a feature available to all overriding constructors.
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 4 /* Default constructor. */ 5 CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT IS 6 7 /* Set a counter variable using a sequence. */ 8 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 9 10 BEGIN 11 /* Assign a sequence value to the instance. */ 12 self.people_id := lv_people_obj_s; 13 14 /* Return a constructed instance. */ 15 RETURN; 16 END; 17 18 /* Override constructor. */ 19 CONSTRUCTOR FUNCTION people_obj 20 ( first_name VARCHAR2 21 , middle_name VARCHAR2 DEFAULT NULL 22 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 23 24 /* Create a empty default instance. */ 25 people PEOPLE_OBJ := people_obj(); 26 27 BEGIN 28 /* Create the instance with the default constructor. */ 29 people.first_name := first_name; 30 people.middle_name := middle_name; 31 people.last_name := last_name; 32 33 /* Assign a local instance this instance. */ 34 self := people; 35 36 /* Return the current instance. */ 37 RETURN; 38 END people_obj; 39 END; 40 / |
The implementation of the no argument constructor is on lines 5 through 16. It uses the .NEXTVAL pseudo column to secure the next sequence value as a unique ID. Then, the constructor function returns a uniquely identified but otherwise empty object instance.
Line 25 creates a people_obj instance inside the declaration block of the overriding constructor. Inside the execution block, the overriding parameters are assigned to the attributes of the local instance. Ultimately, the local instance is assigned to the current instance and returned to any caller of the overriding constructor.
You call the modified overriding function with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'Samuel' 3 , middle_name => 'Langhorne' 4 , last_name => 'Clemens'); 5 BEGIN 6 dbms_output.put_line( '['|| people.people_id ||'] ' 7 ||'['|| people.first_name ||'] ' 8 ||'['|| people.middle_name ||'] ' 9 ||'['|| people.last_name ||']'); 10 END; 11 / |
It prints
[3] [Samuel] [Langhorne] [Clemens] |
This article has shown you how to define and implement basic object types and bodies. It also has shown you how to work with default, no argument, and overriding constructor functions.
External Tables
Oracle Database 9i introduced external tables. You can create external tables to load plain text files by using Oracle SQL*Loader. Alternatively, you can create external tables that load and unload files by using Oracle Data Pump. This article demonstrates both techniques.
You choose external tables that use Oracle SQL*Loader when you want to import plain text files. There are three types of plain text files. They are comma-separated value (CSV), tab-separated value (TSV), and position specific text files.
External tables that use Oracle Data Pump don’t work with plain text files. They work with an Oracle proprietary format. That means you load source files previously created by an Oracle Data Pump export. You typically create external tables with Oracle Data Pump when you’re moving large data sets between database instances.
External tables use Oracle’s virtual directories. An Oracle virtual directory is an internal reference in the data dictionary. A virtual directory maps a unique directory name to a physical directory on the local operating system. Virtual directories were simple before Oracle Database 12c gave us the multitenant architecture. In a multitenant database there are two types of virtual directories. One services the schemas of the Container Database (CDB) and it’s in the CDB’s SYS schema. The other services the schemas of a Pluggable Database (PDB) and it’s in the ADMIN schema for the PDB.
You can create a CDB virtual directory as SYSTEM user with the following syntax in Windows:
SQL> CREATE DIRECTORY upload AS 'C:\Data\Upload'; |
or, like this in Linux or Unix:
SQL> CREATE DIRECTORY upload AS '/u01/app/oracle'; |
There are some subtle differences between these two statements. Windows directories or folders start with a logical drive letter, like C:\, D:\, and so forth. Linux and Unix directories start with a mount point like /u01.
One of the subtle differences is directory and file ownership. You can change ownership for a directory in Windows as the Administrator account. The change makes the directory publically accessible, and that’s probably fine for a test database. After such a change, the Oracle user can find the external file even when parent directories aren’t navigable. Although, a production database on Windows would requires more skill at setting and restricting file permissions.
Linux and Unix directories require that the oracle user can navigate the tree from the mount point to the target physical directory. Also, you must designate the ownership of external files as the same as the Oracle Database user. Assuming a standard install of the Oracle Database 11g XE instance, you would issue the following shell command as the root user to change file ownership and access privileges:
# chown –R oracle:dba /u01/app/oracle/upload # chmod –R 755 /u01/app/oracle/upload |
After you create the virtual directory, you must grant privileges or a role to the user that defines the external table. While data and log files should be separated, this example assumes they co-exist in the same directory.
The following statement grants read privilege for the data file and write privileges for the log files to a CDB user. You should run this statement as the system user.
SQL> GRANT read, WRITE ON DIRECTORY upload TO c##importer; |
or, like this in non-multitenant database or PDB user:
SQL> GRANT read, WRITE ON DIRECTORY upload TO importer; |
The last preparation steps require a plain text file in the physical directory. Let’s create a CSV file of key Avenger characters, and name it the avenger.csv file.
The avenger.csv file holds the following values:
1,'Anthony','Stark','Iron Man' 2,'Thor','Odinson','God of Thunder' 3,'Steven','Rogers','Captain America' 4,'Bruce','Banner','Hulk' 5,'Clinton','Barton','Hawkeye' 6,'Natasha','Romanoff','Black Widow' |
You create the external table after creating the virtual directory, granting read and write privileges on the virtual directory, and creating an external physical file. The syntax for the CREATE TABLE statement of an external table is very similar to the syntax of an ordinary table. The difference between the two types of tables is a clause. An internal table has a STORAGE clause, while an external table has an ORGANIZATION EXTERNAL clause.
The following creates the avenger table as an external table:
SQL> CREATE TABLE avenger 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_loader 8 DEFAULT DIRECTORY upload 9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS TERMINATED BY ',' 15 OPTIONALLY ENCLOSED BY "'" 16 MISSING FIELD VALUES ARE NULL) 17 LOCATION ('avenger.csv')) 18 REJECT LIMIT UNLIMITED; |
Lines 1 through 5 create the columns of the avenger table. Lines 6 through 17 contain the ORGANIZATION EXTERNAL clause. Line 7 designates the external table as managed by the Oracle SQL*Loader utility. Line 8 sets the default virtual directory. Lines 11 through 12 set the bad, discard, and log file location. The bad and discard files keep all that can’t be read. The log file keeps all rows read by a query against the avenger table.
You also have the option of making all reads automatic parallel. You simply add a PARALLEL clause, like this:
19 PARALLEL; |
A simple query with SQL*Plus formatting lets us test whether the avenger table works. The query to display all columns of all rows is:
SQL> COLUMN first_name FORMAT A10 SQL> COLUMN last_name FORMAT A10 SQL> COLUMN character_name FORMAT A15 SQL> SELECT * FROM avenger; |
Yields the following formatted output:
AVENGER_ID FIRST_NAME LAST_NAME CHARACTER_NAME
---------- ---------- ---------- ---------------
1 Anthony Stark Iron Man
2 Thor Odinson God of Thunder
3 Steven Rogers Captain America
4 Bruce Banner Hulk
5 Clinton Barton Hawkeye
6 Natasha Romanoff Black Widow
6 rows selected. |
It’s possible to redefine the avenger table to use either relative or fixed positional columns. You change the ACCESS PARAMETERS clause on lines 9 through 16 to make this change.
The following ACCESS PARAMETERS clause runs across lines 9 through 19 and creates relative position definition:
9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS 15 MISSING FIELD VALUES ARE NULL 16 ( avenger_id CHAR(4) 17 , first_name CHAR(20) 18 , last_name CHAR(20) 19 , character_name CHAR(4))) |
You can change from the relative position, to a fixed position by changing lines 16 through 19. The change for fixed length strings is:
16 ( avenger_id POSITION 1:4 17 , first_name POSITION 5:24 18 , last_name POSITION 25:44 19 , character_name POSITION 45:64)) |
Having worked with the Oracle SQL*Loader version of external tables, lets create one that uses Oracle Data Pump. Assuming we keep the same data structure, drop the avenger table, and create a catalog managed avenger_internal table.
This statement creates the avenger_internal table:
SQL> CREATE TABLE avenger_internal 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)); |
To avoid writing six INSERT statements, you can write one INSERT statement with a query against the SQL*Loader avenger table. The syntax for that INSERT statement is:
SQL> INSERT INTO avenger_internal 2 SELECT * FROM avenger; |
With an internally managed table, you create an avenger_export table that uses Oracle Data Pump like this:
SQL> CREATE TABLE avenger_export 2 ORGANIZATION EXTERNAL 3 ( TYPE oracle_datapump 4 DEFAULT DIRECTORY upload 5 LOCATION ('avenger_export.dmp')) AS 6 SELECT avenger_id 7 , first_name 8 , last_name 9 , character_name 10 FROM avenger_internal; |
The CREATE TABLE statement exports data to the avenger_export.dmp file immediately. You must drop and recreate the avenger_export table to get a fresh extract of the avenger_internal table’s data. You must also remove the previous avenger_export.dmp file before you try to recreate the avenger_export table.
You raise the following error when you fail to remove the previous export file:
CREATE TABLE avenger_export * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11012: FILE avenger_export.dmp IN /u01/... already EXISTS |
This is a simple example with only four columns. You might think you can use the SELECT * as the SELECT-list of the query on lines 6 through 10. If you’re running Oracle Database 12c, you can use the shorter syntax, but if you’re running Oracle Database 11g you can’t. If you attempt it in an Oracle Database 11g instance, the CREATE TABLE statement returns the following error:
ERROR at line 6:
ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE |
You create an avenger_import table with another twist on this now familiar Oracle SQL syntax. The CREATE TABLE statement is:
SQL> CREATE TABLE avenger_import 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_datapump 8 DEFAULT DIRECTORY up2load 9 LOCATION ('avenger_export.dmp')); |
Like the export process, the import process happens immediately when the CREATE TABLE statement runs. A query against the avenger_import table would show you the original six rows we started with in the plain text files.
This article has introduced Oracle external tables. It has shown you how to import plain text files with SQL*Loader. It has also shown you how to export files from tables.
External Tables + Merge
This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.
Step #1 : Create a virtual directory
You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a /u01/app/oracle/upload file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user.
The syntax for these steps is:
CREATE DIRECTORY upload AS '/u01/app/oracle/upload'; GRANT READ, WRITE ON DIRECTORY upload TO student; |
Step #2 : Position your CSV file in the physical directory
After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /u01/app/oracle/upload directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step.
Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data.
'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292', 'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1531', 'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635', 'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686', 'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0682', |
Step #3 : Reconnect as the student user
Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:
CONNECT student@xe |
Step #4 : Run the script that creates tables and sequences
Copy the following into a create_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- Conditionally drop tables and sequences. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create normalized kingdom table. CREATE TABLE kingdom ( kingdom_id NUMBER , kingdom_name VARCHAR2(20) , population NUMBER); -- Create a sequence for the kingdom table. CREATE SEQUENCE kingdom_s1; -- Create normalized knight table. CREATE TABLE knight ( knight_id NUMBER , knight_name VARCHAR2(24) , kingdom_allegiance_id NUMBER , allegiance_start_date DATE , allegiance_end_date DATE); -- Create a sequence for the knight table. CREATE SEQUENCE knight_s1; -- Create external import table. CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR2(20) , population NUMBER , knight_name VARCHAR2(24) , allegiance_start_date DATE , allegiance_end_date DATE) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BAFFLE 'UPLOAD':'kingdom_import.bad' DISCARDFILE 'UPLOAD':'kingdom_import.dis' LOGFILE 'UPLOAD':'kingdom_import.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('kingdom_import.csv')) REJECT LIMIT UNLIMITED; |
Step #5 : Test your access to the external table
There a number of things that could go wrong with setting up an external table, such as file permissions. Before moving on to the balance of the steps, you should test what you’ve done. Run the following query from the student account to check whether or not you can access the kingdom_import.csv file.
1 2 3 4 5 6 7 8 9 | COL kingdom_name FORMAT A8 HEADING "Kingdom|Name" COL population FORMAT 99999999 HEADING "Population" COL knight_name FORMAT A30 HEADING "Knight Name" SELECT kingdom_name , population , knight_name , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date , TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date FROM kingdom_knight_import; |
Step #6 : Create the upload procedure
Copy the following into a create_upload_procedure.sql file within a directory of your choice. Then, run it as the student account.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_kingdom IS BEGIN -- Set save point for an all or nothing transaction. SAVEPOINT starting_point; -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO kingdom target USING (SELECT DISTINCT k.kingdom_id , kki.kingdom_name , kki.population FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population) SOURCE ON (target.kingdom_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET kingdom_name = SOURCE.kingdom_name WHEN NOT MATCHED THEN INSERT VALUES ( kingdom_s1.nextval , SOURCE.kingdom_name , SOURCE.population); -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO knight target USING (SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date) SOURCE ON (target.kingdom_allegiance_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET allegiance_start_date = SOURCE.start_date , allegiance_end_date = SOURCE.end_date WHEN NOT MATCHED THEN INSERT VALUES ( knight_s1.nextval , SOURCE.knight_name , SOURCE.kingdom_id , SOURCE.start_date , SOURCE.end_date); -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / |
Step #7 : Run the upload procedure
You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.
EXECUTE upload_kingdom; |
Step #8 : Test the results of the upload procedure
You can test whether or not it worked by running the following queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Check the kingdom table. SELECT * FROM kingdom; -- Format Oracle output. COLUMN knight_id FORMAT 999 HEADING "Knight|ID #" COLUMN knight_name FORMAT A23 HEADING "Knight Name" COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|Allegiance|ID #" COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date" COLUMN allegiance_end_date FORMAT A11 HEADING "Allegiance|End Date" SET PAGESIZE 999 -- Check the knight table. SELECT knight_id , knight_name , kingdom_allegiance_id , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date , TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date FROM knight; |
It should display the following information:
KINGDOM_ID KINGDOM_NAME POPULATION
---------- -------------------- ----------
1 Narnia 42100
2 Narnia 77600
3 Camelot 15200
Kingdom
Knight Allegiance Allegiance Allegiance
ID # Knight Name ID # Start Date End Date
------ ----------------------- ---------- ----------- -----------
1 Peter the Magnificent 2 20-MAR-1272 19-JUN-1292
2 Edmund the Just 2 20-MAR-1272 19-JUN-1292
3 Susan the Gentle 2 20-MAR-1272 19-JUN-1292
4 Lucy the Valiant 2 20-MAR-1272 19-JUN-1292
5 Peter the Magnificent 1 12-APR-1531 31-MAY-1531
6 Edmund the Just 1 12-APR-1531 31-MAY-1531
7 Susan the Gentle 1 12-APR-1531 31-MAY-1531
8 Lucy the Valiant 1 12-APR-1531 31-MAY-1531
9 King Arthur 3 10-MAR-0631 12-DEC-0686
10 Sir Lionel 3 10-MAR-0631 12-DEC-0686
11 Sir Bors 3 10-MAR-0631 12-DEC-0635
12 Sir Bors 3 10-MAR-0640 12-DEC-0686
13 Sir Galahad 3 10-MAR-0631 12-DEC-0686
14 Sir Gawain 3 10-MAR-0631 12-DEC-0686
15 Sir Tristram 3 10-MAR-0631 12-DEC-0686
16 Sir Percival 3 10-MAR-0631 12-DEC-0686
17 Sir Lancelot 3 30-SEP-0670 12-DEC-0682 |
You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.
Type Dependency Tree
While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.
This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):
CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; / CREATE OR REPLACE TYPE person_t UNDER base_t ( first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)) NOT FINAL; / CREATE OR REPLACE TYPE driver_t UNDER person_t ( license VARCHAR2(20)); / |
Here’s a query to show the hierarchy of object types and attributes by object-level in the hierarchy:
COL type_name FORMAT A20 HEADING TYPE_NAME COL attr_no FORMAT 999 HEADING ATTR_NO COL attr_name FORMAT A20 HEADING ATTR_NAME COL TYPE FORMAT A12 HEADING TYPE SELECT DISTINCT LPAD(' ',2*(LEVEL-1)) || ut.type_name AS type_name , uta.attr_no , uta.attr_name , CASE WHEN uta.attr_type_name = 'NUMBER' THEN uta.attr_type_name WHEN uta.attr_type_name = 'VARCHAR2' THEN uta.attr_type_name || '(' || uta.LENGTH || ')' END AS TYPE FROM user_types ut , user_type_attrs uta WHERE ut.typecode = 'OBJECT' AND ut.type_name = uta.type_name AND uta.inherited = 'NO' START WITH ut.type_name = 'BASE_T' CONNECT BY PRIOR ut.type_name = ut.supertype_name ORDER BY uta.attr_no; |
It should return the following:
TYPE_NAME ATTR_NO ATTR_NAME TYPE
-------------------- ------- -------------------- ------------
BASE_T 1 BASE_ID NUMBER
PERSON_T 2 FIRST_NAME VARCHAR2(20)
PERSON_T 3 MIDDLE_NAME VARCHAR2(20)
PERSON_T 4 LAST_NAME VARCHAR2(20)
DRIVER_T 5 LICENSE VARCHAR2(20) |
As always, I hope this helps those looking to discover an Oracle object type hierarchy without examining each object type in turn.
Oracle Diagnostic Queries
It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.
For this example, you create the following table_struct object type and a table_list collection type:
/* Drop the types from most to least dependent. */ DROP TYPE table_list; DROP TYPE table_struct; /* Create the record type structure. */ CREATE OR REPLACE TYPE table_struct IS OBJECT ( table_name VARCHAR2(30) , column_cnt NUMBER , row_cnt NUMBER ); / /* Create the collection of a record type structure. */ CREATE OR REPLACE TYPE table_list IS TABLE OF table_struct; / |
The following listing function now reads all table names from the user_tables view. A subordinate cursor reads the user_tab_columns view for the number of columns in a table. A Native Dynamic SQL (NDS) cursor counts the number of rows in each tables found in the .
/* Create the listing function. */
CREATE OR REPLACE
FUNCTION listing RETURN table_list IS
/* Variable list. */
lv_column_cnt NUMBER;
lv_row_cnt NUMBER;
/* Declare a statement variable. */
stmt VARCHAR2(200);
/* Declare a system reference cursor variable. */
lv_refcursor SYS_REFCURSOR;
lv_table_cnt NUMBER;
/* Declare an output variable. */
lv_list TABLE_LIST := table_list();
/* Declare a table list cursor that excludes APEX tables. */
CURSOR c IS
SELECT table_name
FROM user_tables
WHERE table_name NOT IN
('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS'
,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS'
,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY'
,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
,'DEMO_ORDER_ITEMS','DEMO_STATES');
/* Declare a column count. */
CURSOR cnt
( cv_table_name VARCHAR2 ) IS
SELECT table_name
, COUNT(column_id) AS cnt_columns
FROM user_tab_columns
WHERE table_name = cv_table_name
GROUP BY table_name;
BEGIN
/* Read through the data set of non-environment variables. */
FOR i IN c LOOP
/* Count the columns of a table. */
FOR j IN cnt(i.table_name) LOOP
lv_column_cnt := j.cnt_columns;
END LOOP;
/* Declare a statement. */
stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
/* Open the cursor and write set to collection. */
OPEN lv_refcursor FOR stmt;
LOOP
FETCH lv_refcursor INTO lv_table_cnt;
EXIT WHEN lv_refcursor%NOTFOUND;
lv_list.EXTEND;
lv_list(lv_list.COUNT) := table_struct(
table_name => i.table_name
, column_cnt => lv_column_cnt
, row_cnt => lv_table_cnt );
END LOOP;
END LOOP;
RETURN lv_list;
END;
/ |
The following query pulls the processed data set as the function’s result:
COL table_name FORMAT A20 HEADING "Table Name" COL column_cnt FORMAT 9,999 HEADING "Column #" COL row_cnt FORMAT 9,999 HEADING "Row #" SELECT table_name , column_cnt , row_cnt FROM TABLE(listing); |
It returns the following result set:
Table Name Column # Row # -------------------- -------- ------ SYSTEM_USER 11 5 COMMON_LOOKUP 10 49 MEMBER 9 10 CONTACT 10 18 ADDRESS 10 18 STREET_ADDRESS 8 28 TELEPHONE 11 18 RENTAL 8 4,694 ITEM 14 93 RENTAL_ITEM 9 4,703 PRICE 11 558 TRANSACTION 12 4,694 CALENDAR 9 300 AIRPORT 9 6 ACCOUNT_LIST 8 200 15 rows selected. |
As always, I hope this helps those trying to work with the Oracle database.
Debug PL/SQL Web Pages
What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.
Here’s a sample web page that fails to run successfully …
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREATE OR REPLACE PROCEDURE html_table_values ( name_array OWA_UTIL.VC_ARR , value_array OWA_UTIL.VC_ARR ) IS BEGIN /* Print debug to SQL*Plus session. */ FOR i IN 1..name_array.COUNT LOOP DBMS_OUTPUT.put_line('Value ['||name_array(i)||'='||value_array(i)||']'); END LOOP; /* Open HTML page with the PL/SQL toolkit. */ htp.print('<!DOCTYPE html>'); htp.htmlopen; htp.headopen; htp.htitle('Test'); htp.headclose; htp.bodyopen; htp.line; htp.print('Test'); htp.line; htp.bodyclose; htp.htmlclose; END; / |
You can test the program with the following anonymous block as the SYSTEM user, which is equivalent to the following URL:
http://localhost:8080/db/html_table_values?begin=1004&end=1012 |
The following test program lets you work:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE x OWA_UTIL.VC_ARR; y OWA_UTIL.VC_ARR; BEGIN /* Insert first row element. */ x(1) := 'begin'; y(1) := '1004'; /* Insert second row element. */ x(2) := 'end'; y(2) := '1012'; /* Call the anonymous schema's web page. */ anonymous.html_table_values(x,y); END; / |
It should print:
Value [begin=1004] Value [end=1012] |
I hope this helps those looking for a solution.
Subscribe via RSS