Archive for the ‘Oracle 23c Free’ Category
Updating Nested ADTs
The first part of this series showed how you can leverage Oracle’s SQL syntax with UDT columns and collection columns. It would be nice if Oracle gave you some SQL to work with the elements of ADT collections, but they don’t. After all, that’s why you have this article.
While you could change the setup of the prior example table, it’s easier to create a new customer table. The new customer table drops the address column. There’s also a new pizza table. The pizza table includes an ingredient ADT collection column, which by design holds a unique set of ingredients for each pizza.
Realistically, ADT collections of numbers, characters, and dates have little value by themselves. That’s because those data types typically don’t have much meaning. A set of unique strings can be useful for certain use cases.
You create the list ADT type with this syntax:
SQL> CREATE OR REPLACE 2 TYPE list IS TABLE OF VARCHAR2(20); 3 / |
You create the customer and pizza tables, and customer_s and pizza_s sequences with the following syntax:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)); SQL> CREATE SEQUENCE customer_s; SQL> CREATE TABLE pizza 2 ( pizza_id NUMBER 3 , customer_id NUMBER 4 , pizza_size VARCHAR2(10) 5 , ingredients LIST 6 , CONSTRAINT pk_pizza PRIMARY KEY (pizza_id) 7 , CONSTRAINT ck_pizza_size 8 CHECK (pizza_size IN ('Mini','Small','Medium','Large','Very Large'))) 9 NESTED TABLE ingredients STORE AS ingredient_table; SQL> CREATE SEQUENCE pizza_s; |
The customer table only has scalar columns. The pizza table has the ingredient ADT collection column. Line 9 creates a nested ingredient_table for the ingredient ADT collection column.
There is a primary and foreign key relationship between the customer and pizza tables. That relationship between the tables requires that you insert rows into the customer table before you insert rows into the pizza table.
The sample script populates the customer table with characters from the Green Arrow television show, as follows:
Customer
ID # Last Name First Name
-------- ---------- ----------
1 Queen Oliver
2 Queen Thea
3 Queen Moira
4 Lance Dinah
5 Lance Quentin
6 Diggle John
7 Wilson Slade |
Next, you can insert three rows into the pizza table. Each has different ingredients in the ingredient ADT column.
The following is the syntax for the INSERT statements:
SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Quentin' AND c.last_name = 'Lance') 6 ,'Large' 7 , list('Cheese','Marinara Sauce','Sausage','Salami')); SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Thea' AND c.last_name = 'Queen') 6 ,'Medium' 7 , list('Cheese','Marinara Sauce','Canadian Bacon','Pineapple')); SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'John' AND c.last_name = 'Diggle') 6 ,'Small' 7 , list('Cheese','BBQ Sauce','Chicken')); |
Querying results from tables with nested ADT columns provides interesting results. An ordinary query, like this:
SQL> COL pizza_id FORMAT 99999 HEADING "Pizza|ID #" SQL> COL pizza_size FORMAT A6 HEADING "Pizza|Size" SQL> COL ingredients FORMAT A64 HEADING "Ingredients" SQL> SELECT pizza_id 2 , pizza_size 3 , ingredients 4 FROM pizza; |
… returns the following results with a flattened object type:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------“
1 Large LIST('Cheese', 'Marinara Sauce', 'Sausage', 'Salami')
2 Medium LIST('Cheese', 'Marinara Sauce', 'Canadian Bacon', 'Pineapple')
3 Small LIST('Cheese', 'BBQ Sauce', 'Chicken') |
If you use a CROSS JOIN it multiplies each row times the number of items in the ADT collection column. The multiplication hides the results.
The best solution for displaying results from an ADT collection requires that you serialize the results. The following serialize_set PL/SQL function creates a serialized comma separated list:
SQL> CREATE OR REPLACE 2 FUNCTION serialize_set (pv_list LIST) RETURN VARCHAR2 IS 3 /* Declare a return string as large as you need. */ 4 lv_comma_string VARCHAR2(60); 5 BEGIN 6 /* Read list of values and serialize them in a string. */ 7 FOR i IN 1..pv_list.COUNT LOOP 8 IF NOT i = pv_list.COUNT THEN 9 lv_comma_string := lv_comma_string || pv_list(i) || ', '; 10 ELSE 11 lv_comma_string := lv_comma_string || pv_list(i); 12 END IF; 13 END LOOP; 14 RETURN lv_comma_string; 15 END serialize_set; |
You can now write a query that uses your PL/SQL function to format the ADT collection column values into a single row. The syntax for the query is:
SQL> SELECT pizza_id 2 , pizza_size 3 , serialize_set(ingredients) AS ingredients 4 FROM pizza; |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Pineapple
3 Small Cheese, BBQ Sauce, Chicken |
At this point, you know how to create a table with an ADT collection column and how to insert values. The Oracle documentation says you can only replace the whole content of the ADT column in an UPDATE statement. That’s true in practice but not in principle.
The principal differs because you can write PL/SQL functions that add, change, or remove elements from the ADT collection that works in an UPDATE statement. The trick is quite simple. You achieve it by:
- Passing the current ADT collection as a IN-only mode parameter
- Passing any new parameters when you add or change elements
- Passing any old parameters when you change or remove elements
Now, you will learn how to create the add_elements, change_elements, and remove_elements PL/SQL functions. They let you use an UPDATE statement to add, change, or remove elements from an ADT collection column.
Adding ADT elements with an UPDATE statement
This section shows you how to add elements to an ADT collection column with an UPDATE statement. The add_elements PL/SQL function can add one or many elements to an ADT collection column. That’s possible because the new element or elements are passed to the function inside an ADT collection parameter.
The merit of this type of solution is that you only need one function to accomplish two tasks. The test cases show you how to pass one new element or a set of new elements.
An alternative solution would have you write two functions. One would accept a collection parameter and a variable length string, and the other would accept two collection parameters. Many developers might choose to do that because they would like to leverage overloading inside PL/SQL packages. You should ask yourself one question when you make the decision about your approach to this problem: Which is easier to maintain and use?
The following creates the add_elements PL/SQL function:
SQL> CREATE OR REPLACE 2 FUNCTION add_elements 3 ( pv_list LIST 4 , pv_element LIST ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiated collection and initialize when necessary. */ 9 IF pv_list IS NULL THEN 10 lv_list := list(); 11 ELSE 12 /* Assign parameter collection to local collection variable. */ 13 lv_list := pv_list; 14 FOR i IN 1..pv_element.COUNT LOOP 15 /* Check to avoid duplicates, allocate memory and assign value. */ 16 IF NOT list(pv_element(i)) SUBMULTISET OF lv_list THEN 17 lv_list.EXTEND; 18 lv_list(lv_list.COUNT) := pv_element(i); 19 END IF; 20 END LOOP; 21 END IF; 22 23 /* Return new collection. */ 24 RETURN lv_list; 25 END add_elements; 26 / |
Line 3 and 4 define the two parameters of the add_elements function as ADT collections. Line 4 also designates the return type of the function, which is the same ADT collection.
Line 6 declares a local ADT collection variable. You need a local lv_list ADT collection variable because you want to accept two collections and merge them into the local ADT collection variable. Then, you return the local ADT collection variable as the function outcome.
Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.
Line 16 use the SUBMULTISET set operator to ensure that only new add elements when they don’t already exist in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns a new element to it.
You could extend memory for the total count of elements but that would make the index assignment on line 18 more complex. Combining them increments the count of items and lets you use the count as the index value. Line 24 returns the local ADT collection and replaces the original ingredient column value.
The test case for the function should ensure that only unique values are assigned to the ingredient ADT collection column value. This can be done by a three-step test case. The test queries the values in the ADT collection column, updates them, and re-queries them.
The following query shows you the contents of the row:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 ( SELECT customer_id FROM customer 6 WHERE first_name = 'Quentin' AND last_name = 'Lance' ); |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami |
You can update the ADT collection column’s values with the following UPDATE statement. It attempts to add Sausage and Italian Sausage to the list of values. The function should add only Italian Sausage because Sausage already exists in the list of values. When you re-query the row you will see that the add_elements added only the element Italian Sausage.
You would use the following UPDATE statement:
SQL> UPDATE pizza 2 SET ingredients = 3 add_elements(ingredients,list('Italian Sausage','Sausage')) 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Quentin' AND last_name = 'Lance'); |
Line 3 calls the add_elements PL/SQL function with the ingredient ADT collection column’s value as the first parameter. The second parameter is a dynamically created list of the elements. It contains the element or elements you want to add to the ingredient column’s values.
Re-querying the row, you should see that the UPDATE statement added only the Italian Sausage element to the row. You should see the following output:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami, Italian Sausage' |
As you can see, the call to the add_elements function adds only Italian Sausage to the list of values in the ingredient column, while a comma delimited list of single quote delimited strings allows you to add multiple elements. You add one element by making it the only single quote delimited item in the list constructor call.
Updating ADT elements with an UPDATE statement
This section shows you how to change elements in an ADT collection column with an UPDATE statement. The change_elements PL/SQL function can change one to many elements in an ADT collection column. That’s possible because the change element or elements are passed to the function inside ADT collection parameters.
Unlike the add_elements function, the change_elements function requires an ADT collection parameter and a UDT collection element. The UDT collection needs to hold an old and new value.
The alternative approach would require you to try and synchronize two ADT collection value sets. One would hold all the old values and the other would hold all the new values, and they would both need to be synchronized in mirrored positional order.
You define a pair UDT object type such as the following:
SQL> CREATE OR REPLACE 2 TYPE pair IS OBJECT 3 ( old VARCHAR2(20) 4 , NEW VARCHAR2(20)); 5 / |
Next, you define a change UDT collection type:
SQL> CREATE OR REPLACE 2 TYPE change IS TABLE OF pair; 3 / |
You define the change_element function as shown below:
SQL> CREATE OR REPLACE 2 FUNCTION change_elements 3 ( pv_list LIST 4 , pv_element CHANGE ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiated collection and initialize when necessary. */ 9 IF pv_list IS NULL THEN 10 lv_list := list(); 11 ELSE 12 /* Assign parameter collection to local collection variable. */ 13 lv_list := pv_list; 14 FOR i IN 1..pv_element.COUNT LOOP 15 /* Check to avoid duplicates, allocate memory and assign value. */ 16 IF NOT list(pv_element(i).old) SUBMULTISET OF lv_list THEN 17 lv_list.EXTEND; 18 lv_list(lv_list.COUNT) := pv_element(i).NEW; 19 END IF; 20 END LOOP; 21 END IF; 22 23 /* Return new collection. */ 24 RETURN lv_list; 25 END change_elements; 26 / |
Line 3 and 4 define the two parameters of the change_elements function. The first pv_list parameter uses the list ADT collection type and the list type that matches the ingredient column’s data type. Line 4 defines a parameter that uses the change UDT collection type, which is a collection of the pair UDT type.
Line 6 declares a local ADT collection variable, such as the add_elements function. The lv_list variable also serves the same purpose as it does in the add_elements function.
Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.
Line 16 uses the SUBMULTISET set operator to ensure that the old element exists in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns the new element to it.
The change_elements function couples the memory allocation with the assignment of new values. Line 24 returns the local ADT collection and replaces the original ingredient column value.
The test case shows you how to pass one old and one new element or a set of old and new elements. The initial query shows you the data before the update:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen'); |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon |
You now update the row with the following query:
SQL> UPDATE pizza 2 SET ingredients = 3 change_elements(ingredients 4 ,change(pair(old => 'Italian Sausage' 5 ,NEW => 'Linguica'))) 6 WHERE customer_id = 7 ( SELECT customer_id FROM customer 8 WHERE first_name = 'Thea' AND last_name = 'Queen' ); |
When you re-query the row, it shows you the following:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica |
As you can see, the call to the change_elements function changes onlyItalian Sausage to Linguica in the list of values in the ingredient column, while a comma delimited list of pair UDT values allows you to change multiple elements. You change one element by making it the only pair UDT in the change constructor call.
Removing ADT elements with an UPDATE statement
This section shows you how to remove elements from an ADT collection column with an UPDATE statement. The remove_elements PL/SQL function can remove one to many elements from an ADT collection column.
The remove_elements function works much like the add_elements function. It uses the same ADT collections as the add_elements function.
The code for the remove_elements function is:
SQL> CREATE OR REPLACE 2 FUNCTION remove_elements 3 ( pv_list LIST 4 , pv_elements LIST ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiation and element membership. */ 9 IF NOT (pv_list IS NULL AND pv_elements IS NULL) AND 10 (pv_list.COUNT > 0 AND pv_elements.COUNT > 0) THEN 11 /* Assign parameters to local variables. */ 12 lv_list := pv_list; 13 /* Remove any elements from a collection. */ 14 FOR i IN 1..lv_list.COUNT LOOP 15 FOR j IN 1..pv_elements.COUNT LOOP 16 IF lv_list(i) = pv_elements(j) THEN 17 lv_list.DELETE(i); 18 EXIT; 19 END IF; 20 END LOOP; 21 END LOOP; 22 END IF; 23 24 /* Return modified collection. */ 25 RETURN lv_list; 26 END remove_elements; 27 / |
Lines 3, 4, and 6 work like the add_elements function. Lines 9 and 10 differ because they check for initialized collections that hold at least one element each. Line 12 mimics the behavior of line 13 in the add_elements function. Lines 14 through 16 implements a nested loop and filtering IF-statement. The IF-statement checks for a valid element to remove from the ingredient ADT column’s list of values.
Line 17 removes an element from the list. Line 18 exits the inner loop to skip the evaluation of other non-matches. It’s possible to do this because the add_elements and change_elements functions ensure a unique list of string values in the ingredient ADT collection.
The test case for the remove_elements function works like the earlier tests. You query the row that you will update to check its values; for instance:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen'); |
It should return:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica |
You would remove an element from the ingredient ADT collection column with the following UPDATE statement:
SQL> UPDATE pizza 2 SET ingredients = 3 remove_elements(ingredients,list('Canadian Bacon')) 4 WHERE customer_id = 5 ( SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen' ); |
When you re-query the row, you should see that Canadian Bacon is no longer an element in the ingredient ADT collection column. Like this:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Linguica |
This two article series has shown you the differences between working with ADT and UDT collection. It has also shown you how to create PL/SQL functions to enable you to add, change, and remove elements from ADT column inside an UPDATE statement.
The next step would be for you to put the serialize_set, add_elements, change_elements, and remove_elements functions into an adt package. That package would look like:
SQL> CREATE OR REPLACE 2 PACKAGE adt IS 3 4 FUNCTION add_elements 5 ( pv_list LIST 6 , pv_element LIST ) RETURN LIST; 7 8 FUNCTION change_elements 9 ( pv_list LIST 10 , pv_element CHANGE ) RETURN LIST; 11 12 FUNCTION remove_elements 13 ( pv_list LIST 14 , pv_elements LIST ) RETURN LIST; 15 16 FUNCTION serialize_set 17 (pv_list LIST) RETURN VARCHAR2; 18 19 END adt; 20 / |
Beyond writing an ADT package to manage a list of variable length strings, you have the opportunity to extend behaviors further through overloading. Overloading lets you define functions that use the same name with different parameter lists.
For example, you could define the LIST_D, LIST_N, and LIST_S as SQL ADT where they would implement ADTs of dates, numbers, and strings respectively. Then, you would write three versions of the preceding four functions. Each set of functions would work with one of the type specific ADTs, and provide you with a powerful utility package to add, change, remove, and serialize the values of date, number, and string ADTs.
When you put all the related functions into a package you simplify access and organize for reusability. That way you have all the tools you need inside a single adt package to write advanced UPDATE statements against ADT nested tables.
Updating Nested Tables
This two-part series covers how you update User-Defined Types (UDTs) and Attribute Data Types (ADTs). There are two varieties of UDTs. One is a column of a UDT object type and the other a UDT collection of a UDT object type.
You update nested UDT columns by leveraging the TABLE function. The TABLE function lets you create a result set, and access a UDT object or collection column. You need to combine the TABLE function and a CROSS JOIN to update elements of a UDT collection column.
ADTs are collections of a scalar data types. Oracle’s scalar data types are DATE, NUMBER, CHAR and VARCHAR2 (or, variable length strings). ADTs are unique and from some developer’s perspective difficult to work with.
The first article in this series shows you how to work with a UDT object type column and a UDT collection type. The second article will show you how to work with an ADT collection type.
PL/SQL uses ADT collections all the time. PL/SQL also uses User-Defined Types (UDTs) collections all the time. UDTs can be record or object types, or collections of records and objects. Record types are limited, and only work inside a PL/SQL scope. Object types are less limited and you can use them in a SQL or PL/SQL scope.
Object types come in two flavors. One acts as a typical record structure and has no methods and the other acts like an object type in any object-oriented programming language (OOPL). This article refers only to object types like typical record structures. That means when you read ADTs you should think of a SQL collection of a scalar data type, and when you read UDTs you should think of a SQL collection of an object type without methods.
You can create tables that hold nested tables. Nested tables can use a SQL ADT or UDT data type. Inserting data into nested tables is straightforward when you understand the syntax, but updating nested tables can be complex. The complexity exists because Oracle treats nested tables of ADTs differently than UDTs. My article series will show you how to simplify updating ADT columns.
That’s why it has two parts:
- How you insert and update rows with UDT columns and collection columns
- How you insert and update rows with ADT collection columns
If you’re asking yourself why there isn’t a section for deleting rows, that’s simple. You delete them the same way as you would any other row, using the DELETE statement.
How you insert and update rows with UDT columns and collection columns
This section shows you how to create a table with a UDT column and a UDT collection column. It also shows you how to insert and update the embedded columns.
You insert into any ordinary UDT column by prefacing the data with a constructor name. A constructor name is the same as a UDT name. The following creates an address_type UDT that you will use inside a customer table:
SQL> CREATE OR REPLACE 2 TYPE address_type IS OBJECT 3 ( street VARCHAR2(20) 4 , city VARCHAR2(30) 5 , state VARCHAR2(2) 6 , zip VARCHAR2(5)); 7 / |
You should take note that the address_type UDT doesn’t have any methods. All object types without methods have a default constructor. The default constructor follows the same rules as tables in the database.
Create the sample customer table with an address column that uses the address_type UDT as its data type; for instance:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , address ADDRESS_TYPE 6 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)); |
Line 5 defines the address column with the address_type UDT. You insert a row with an embedded address_type data record as follows:
SQL> INSERT 2 INTO customer 3 VALUES 4 ( customer_s.NEXTVAL 5 ,'Oliver' 6 ,'Queen' 7 , address_type( street => '1 Park Place' 8 , city => 'Starling City' 9 , state => 'NY' 10 , zip => '10001')); |
Lines 7 through 10 includes the constructor call to the address_type UDT. The address_type constructor uses named notation rather than positional notation. You should always try to use named notation for object type constructor calls.
Updating an element of a UDT object structure is straightforward, because you simply refer to the column and a member of the UDT object structure. The syntax for that type of UPDATE statement follows:
SQL> UPDATE customer c 2 SET c.address.state = 'NJ' 3 WHERE c.first_name = 'Oliver' 4 AND c.last_name = 'Queen'; |
The address_type UDT works for an object structure but not for a UDT collection. You need to add a column to differentiate between rows of the nested collection. You can redefine the address_type UDT as follows:
SQL> CREATE OR REPLACE 2 TYPE address_type IS OBJECT 3 ( status VARCHAR2(8) 4 , street VARCHAR2(20) 5 , city VARCHAR2(30) 6 , state VARCHAR2(2) 7 , zip VARCHAR2(5)); 8 / |
After creating the UDT object type, you need to create an address_table UDT collection of the address_type UDT object type. You use the following syntax to create the SQL collection:
SQL> CREATE OR REPLACE 2 TYPE address_table IS TABLE OF address_type; 3 / |
Having both the UDT object and collection types, you can drop and create the customer table with the following syntax:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , address ADDRESS_TABLE 6 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)) 7 NESTED TABLE address STORE AS address_tab; |
Line 5 defines the address column as a UDT collection. Line 7 instructs how to store the UDT collection as a nested table. You designate the address column as the nested table and store it as an address_tab table. You can access the nested table only through its container, which is the customer table.
You can insert rows into the customer table with the following syntax. This example stores a single row with two elements of the address_type in the nested table:
SQL> INSERT 2 INTO customer 3 VALUES 4 ( customer_s.NEXTVAL 5 ,'Oliver' 6 ,'Queen' 7 , address_table( 8 address_type( status => 'Obsolete' 9 , street => '1 Park Place' 10 , city => 'Starling City' 11 , state => 'NY' 12 , zip => '10001') 13 , address_type( status => 'Current' 14 , street => '1 Dockland Street' 15 , city => 'Starling City' 16 , state => 'NY' 17 , zip => '10001'))); |
Lines 7 through 17 have two constructor calls for the address_type UDT object type inside the address_table UDT collection. After you insert an address_table UDT collection, you can query an element by using the SQL built-in TABLE function and a CROSS JOIN. The TABLE function returns a SQL result set. The CROSS JOIN lets you create cross product that you can filter inside the WHERE clause.
A CROSS JOIN between two tables or a table and result set from a nested table matches every row in the customer table with every row in the nested table. A best practice would include a WHERE clause that filters the nested table to a single row in the result set.
The syntax for such a query is complex, and follows below:
SQL> COL first_name FORMAT A8 HEADING "First|Name" SQL> COL last_name FORMAT A8 HEADING "Last|Name" SQL> COL street FORMAT A20 HEADING "Street" SQL> COL city FORMAT A14 HEADING "City" SQL> COL state FORMAT A5 HEADING "State" SQL> SELECT c.first_name 2 , c.last_name 3 , a.street 4 , a.city 5 , a.state 6 FROM customer c CROSS JOIN TABLE(c.address) a 7 WHERE a.status = 'Current'; |
As mentioned, the TABLE function on line 6 translates the UDT collection into a SQL result set, which acts as a temporary table. The alias a becomes the name of the temporary table. Lines 3, 4, 5, and 7 all reference the temporary table.
The query should return the following for the customer and their current address value:
First Last Name Name Street City State -------- -------- -------------------- -------------- ----- Oliver Queen 1 Dockland Street Starling City NY |
Oracle thought through the fact that you should be able to update UDT collections. The same TABLE function lets you update elements in the nested table. You can update the elements in nested UDT tables provided you create a unique key, such as a natural key or primary key. Oracle’s syntax doesn’t support constraints on nested tables, which means you need to implement it by design and protect by carefully controlling inserts and updates to the nested table.
You can update the state value of the current address with the following UPDATE statement:
SQL> UPDATE TABLE(SELECT c.address 2 FROM customer c 3 WHERE c.first_name = 'Oliver' 4 AND c.last_name = 'Queen') a 5 SET a.state = 'NJ' 6 WHERE a.status = 'Current'; |
Line 5 sets the current state value in the address_table UDT nested table. Line 6 filters the nested table to the current address element. You need to ensure that any UDT object type holds a member attribute or set of member attributes that holds a unique value. That’s because you need to ensure that there’s a way to find a unique element within a UDT collection. If you require the table, you should see the change inside the nested table.
Oracle does not provide equivalent syntax for such a change in an ADT collection type. The second article in this series show you how to implement PL/SQL functions to solve that problem.
Oracle 23c Free Ext Files
This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file inside Docker Oracle Database 23c Free. 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 the Docker Oracle Database 23c Free instance, you should put the code in subdirectories of the /opt/oracle file directory.
- Connect as the root user with the following Docker command:
docker exec -it --user root oracle23c bash
Issue the following commands as the oracle user inside the Docker container to create the necessary physical directories. You may need to refer to my earlier blog post if you haven’t setup the oracle user inside the Docker instance. While this blog post will only use the /opt/oracle/upload/text and /opt/oracle/upload/log directories, a subsequent post will demonstrate the preprocessing module for the external tables.
mkdir /opt/oracle/upload mkdir /opt/oracle/upload/text mkdir /opt/oracle/upload/log mkdir /opt/oracle/upload/preproc
- Connect to the Oracle Database 23c Free inside the container as the system user to create a c##studentrole, and do the following three things:
- Grant privileges to the c##studentrole, and grant the c##studentrole to the c##student user.
-- Create the role. CREATE ROLE c##studentrole; -- Grant privileges to the role. GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW TO c##studentrole; -- Grant privileges to the user. GRANT c##studentrole TO c##student;
- As the system user, create the necessary virtual directories that map to the physical directories inside the Docker container:
CREATE DIRECTORY upload AS '/opt/oracle/upload/text'; CREATE DIRECTORY preproc AS '/opt/oracle/upload/preproc'; CREATE DIRECTORY LOG AS '/opt/oracle/upload/log';
- As the system user, grant the necessary privileges on the virtual directories to the c##studentrole role:
GRANT read ON DIRECTORY upload TO c##studentrole; GRANT read, WRITE ON DIRECTORY LOG TO c##studentrole; GRANT read, EXECUTE ON DIRECTORY preproc TO c##studentrole;
- Grant privileges to the c##studentrole, and grant the c##studentrole to the c##student user.
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 /opt/oracle/upload/texgt 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','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1328','Prince Caspian', 'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635','The Once and Future King', 'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0686','The Once and Future King', |
Step #3 : Reconnect as the student user
Disconnect and connect as the c##student user, or reconnect as the c##student user. The reconnect syntax that protects your password is:
CONNECT c##student@free |
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. I use varchar as the data type because it’s an alias for varchar2 and highlights appropriately with the GeSHi formatting. 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 | -- Conditionally drop tables. DROP TABLE IF EXISTS kingdom; DROP TABLE IF EXISTS knight; DROP TABLE IF EXISTS kingdom_knight_import; -- Conditionally drop sequences. DROP SEQUENCE IF EXISTS kingdom_s1; DROP SEQUENCE IF EXISTS knight_s1; -- Create normalized kingdom table. CREATE TABLE kingdom ( kingdom_id NUMBER , kingdom_name VARCHAR(20) , population NUMBER , book VARCHAR(40)); -- Create a sequence for the kingdom table. CREATE SEQUENCE kingdom_s1; -- Create normalized knight table. CREATE TABLE knight ( knight_id NUMBER , knight_name VARCHAR(22) , kingdom_allegiance_id NUMBER , allegiance_start_date DATE , allegiance_end_date DATE , book VARCHAR(40)); -- Create a sequence for the knight table. CREATE SEQUENCE knight_s1; -- Create external import table. CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR(20) , population NUMBER , knight_name VARCHAR(22) , allegiance_start_date DATE , allegiance_end_date DATE , book VARCHAR(40)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'LOG':'kingdom_import.bad' DISCARDFILE 'LOG':'kingdom_import.dis' LOGFILE 'LOG':'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 10 11 12 | SET PAGESIZE 999 COL kingdom_name FORMAT A7 HEADING "Kingdom|Name" COL folks FORMAT 99999 HEADING "Folks" COL knight_name FORMAT A21 HEADING "Knight Name" COL dates FORMAT A11 HEADING "Start Date" COL source_book FORMAT A38 HEADING "Book" SELECT kingdom_name , knight_name , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') || TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS dates , book FROM kingdom_knight_import; |
Step #6 : Create the upload procedure
Copy the following into a create_upload_procedure.sql file within a virtual directory of your choice. As noted above in the external table definition writes only occur in the log virtual directory. This is important because there are articles out there on the Internet that could misdirect you when you get the following error message on the upload virtual directory.
ORA-06564: Object UPLOAD does not exist or is not accessible to the user. |
By the way, you’ll only see that error if you fail to:
- Designate the procedure as AUTH_ID CURRENT, and
- Enabled SERVEROUTPUT inside the SQL*Plus command-line interface (CLI) session or inside the glogin.sql file for the Oracle Database 23c Free Docker instance.
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 60 61 62 63 64 65 66 67 68 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_kingdom AUTHID CURRENT_USER 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 , kki.book FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population AND kki.book = k.book) 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 , SOURCE.book); -- 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 , kki.book 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 AND kki.book = kn.book) 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 , book = SOURCE.book WHEN NOT MATCHED THEN INSERT VALUES ( knight_s1.nextval , SOURCE.knight_name , SOURCE.kingdom_id , SOURCE.start_date , SOURCE.end_date , SOURCE.book); -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); ROLLBACK TO starting_point; RETURN; END; / |
Step #7 : Run the upload procedure
You can run the file by calling the script above. 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 19 20 21 22 23 24 | -- Format Oracle output. COLUMN kingdom_id FORMAT 999 HEADING "Kingdom|ID #" COLUMN kingdom_name FORMAT A14 HEADING "Kingdom|Name" COLUMN population FORMAT 999,999 HEADING "Population" COLUMN book FORMAT A40 HEADING "Source Book" -- Check the kingdom table. SELECT * FROM kingdom; -- Format Oracle output. SET PAGESIZE 999 COLUMN knight_id FORMAT 999 HEADING "Knight|ID #" COLUMN knight_name FORMAT A23 HEADING "Knight|Name" COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|ID #" COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date" COLUMN allegiance_end_date FORMAT A11 HEADING "Allegiance|End Date" -- 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 Kingdom
ID # Name Population Source Book
------- -------------- ---------- ----------------------------------------
1 Narnia 42,100 Prince Caspian
2 Narnia 77,600 The Lion, The Witch and The Wardrobe
3 Camelot 15,200 The Once and Future King
Knight Knight Kingdom Allegiance Allegiance
ID # 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-1328
6 Edmund the Just 1 12-APR-1531 31-MAY-1328
7 Susan the Gentle 1 12-APR-1531 31-MAY-1328
8 Lucy the Valiant 1 12-APR-1531 31-MAY-1328
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-0686 |
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.
Subscribe via RSS