Solution: for 04-5: Understand impact of error raised in NOCOPY program

CREATE OR REPLACE PACKAGE hoc_pkg
IS
   TYPE strings_aat IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
   PROCEDURE inout_collection ( strings_inout IN OUT strings_aat );
   PROCEDURE inout_nocopy ( strings_inout IN OUT NOCOPY strings_aat );
   PROCEDURE show_collection ( title_in IN VARCHAR2, strings_in IN strings_aat );
END hoc_pkg;
/
 
CREATE OR REPLACE PACKAGE BODY hoc_pkg
IS
   PROCEDURE show_collection ( title_in IN VARCHAR2, strings_in IN strings_aat )
   IS
      l_row PLS_INTEGER;
   BEGIN
      DBMS_OUTPUT.put_line(title_in);
      l_row := strings_in.FIRST;
 
      WHILE ( l_row IS NOT NULL )
      LOOP
         DBMS_OUTPUT.put_line( '   ' || strings_in(l_row));
         l_row := strings_in.NEXT(l_row);
      END LOOP;
   END show_collection;
 
   PROCEDURE inout_collection ( strings_inout IN OUT strings_aat )
   IS
      l_row PLS_INTEGER;
   BEGIN
      l_row := strings_inout.FIRST;
 
      WHILE ( l_row IS NOT NULL )
      LOOP
         IF strings_inout(l_row) = 'ABC'
         THEN
            RAISE PROGRAM_ERROR;
         END IF;
 
         strings_inout (l_row) := strings_inout(l_row) || strings_inout(l_row);
         l_row := strings_inout.NEXT(l_row);
      END LOOP;
   END inout_collection;
 
   PROCEDURE inout_nocopy ( strings_inout IN OUT NOCOPY strings_aat )
   IS
      l_row PLS_INTEGER;
   BEGIN
      l_row := strings_inout.FIRST;
 
      WHILE ( l_row IS NOT NULL )
      LOOP
         IF strings_inout(l_row) = 'ABC'
         THEN
            RAISE PROGRAM_ERROR;
         END IF;
 
         strings_inout(l_row) := strings_inout (l_row) || strings_inout(l_row);
         l_row := strings_inout.NEXT(l_row);
      END LOOP;
   END inout_nocopy;
END hoc_pkg;
/
 
DECLARE
   l_names1 hoc_pkg.strings_aat;
   l_names2 hoc_pkg.strings_aat;
BEGIN
   FOR indx IN 1 .. 20
   LOOP
      IF indx = 13 THEN
         l_names1(indx) := 'ABC';
      ELSE
         l_names1(indx) := TO_CHAR(indx);
      END IF;
   END LOOP;
 
   FOR indx IN 1 .. 20
   LOOP
      IF indx = 13 THEN
         l_names2(indx) := 'ABC';
      ELSE
         l_names2(indx) := TO_CHAR(indx);
      END IF;
   END LOOP;
 
   BEGIN
      hoc_pkg.inout_collection ( l_names1 );
   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;
 
   BEGIN
      hoc_pkg.inout_nocopy ( l_names2 );
   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;
 
   hoc_pkg.show_collection ( 'IN OUT', l_names1 );
   hoc_pkg.show_collection ( 'IN OUT NOCOPY', l_names2 );
END;
/
 

05. Modify contents of collections with collection methods

05-1: Delete elements from a collection.

Solution: for 05-1s: Delete elements from a collection.

DECLARE
   -- Define an associative array of numbers and populate index values 1 through 10 with values of your choice.
   TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   l_numbers numbers_aat;
BEGIN
   FOR indx IN 1 .. 10
   LOOP
      DBMS_OUTPUT.put_line(MOD(3.14 * indx * 176, 3));
      l_numbers (indx) := MOD(3.14 * indx * 176, 3);
   END LOOP;
 
   -- Then use the DELETE method to:
 
   -- 1. Remove just the 4th row,
   l_numbers.DELETE ( 4 );
   -- 2. Remove rows with index values between 6 and 9.
   l_numbers.DELETE ( 6, 9 );
   -- 3. Remove all the remaining rows.
   l_numbers.DELETE;
END;
/
 

05-2: Extend nested tables and varrays

Solution: for 05-2: Extend nested tables and varrays

DECLARE
   -- 1. Declare a nested table based on a locally-defined nested table type of numbers.
   TYPE numbers_ntt IS TABLE OF NUMBER;
 
   l_numbers numbers_ntt := numbers_ntt();
BEGIN
   -- 2. Write a numeric FOR loop that populates the nested table with 25 elements, extending one element at a time.
   FOR indx IN 1 .. 25
   LOOP
      l_numbers.EXTEND;
      l_numbers(indx) := indx;

      -- Or...
      -- l_numbers(l_numbers.COUNT) := indx;

   END LOOP;
END;
/
 
/*
   3. Rewrite the above code as follows:
      a. Define a schema-level nested table type.
      b. Create a function that accepts the total number of elements needed in the
             nested table, and then returns that nested table in the function RETURN
                 clause. Use a single call to the EXTEND method.
*/
 
CREATE OR REPLACE TYPE numbers_ntt IS TABLE OF NUMBER;
/
 
CREATE OR REPLACE FUNCTION all_my_numbers ( count_in IN PLS_INTEGER )
   RETURN numbers_ntt
IS
   l_numbers numbers_ntt := numbers_ntt();
BEGIN
   l_numbers.EXTEND(count_in);
 
   FOR indx IN 1 .. count_in
   LOOP
      l_numbers(indx) := indx;
   END LOOP;
   RETURN l_numbers;
END all_my_numbers;
/
 
-- 4. Change the above code to work with varrays with a maximum number of elements set to 100.
 
CREATE OR REPLACE TYPE numbers_vat IS VARRAY(100) OF NUMBER;
/
 
CREATE OR REPLACE FUNCTION all_my_numbers ( count_in IN PLS_INTEGER )
   RETURN numbers_vat
IS
   l_numbers numbers_vat := numbers_vat();
BEGIN
   l_numbers.EXTEND(count_in);
 
   FOR indx IN 1 .. count_in
   LOOP
      l_numbers(indx) := indx;
   END LOOP;
   RETURN l_numbers;
END all_my_numbers;
/
 

05-3: Trim elements from the end of a varray

Solution: for 05-3: Trim elements from the end of a varray

DECLARE
   -- Declare a varray of numbers with a maximum of 20 elements.
   TYPE numbers_vat IS VARRAY(20) OF NUMBER;
    l_numbers numbers_vat := numbers_vat();
BEGIN
   l_numbers.EXTEND(20);
 
   -- Populate it with 20 values of your choice.
   FOR indx IN 1 .. 20
   LOOP
      l_numbers(indx) := indx * ( 1958 + 09 + 23 );
   END LOOP;
 
   FOR indx IN 1 .. 5
   LOOP
      l_numbers.TRIM;
   END LOOP;
 
   /* Or...
   l_numbers.TRIM (5);
   */
 
   -- Trim 5 elements from the varray and display the remaining values,
   -- along with the count in the collection.
   DBMS_OUTPUT.put_line('Count in numbers varray = ' || l_numbers.COUNT);
 
   FOR indx IN 1 .. l_numbers.COUNT
   LOOP
      DBMS_OUTPUT.put_line(l_numbers(indx));
   END LOOP;
END all_my_numbers;
/
 

06. String-indexed collections

06-1: Populate a string-indexed collection

Solution: for 06-1: Populate a string-indexed collection

DECLARE
   SUBTYPE full_name_t IS VARCHAR2 ( 1000 );
 
   -- Declare two associative array types of records based on the employees table, indexed by the primary key and the name.
   TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   TYPE employees_by_name_aat IS TABLE OF employees%ROWTYPE INDEX BY full_name_t;
 
   -- Instance of table-based collection types
   l_temp_employees employees_aat;
   l_employees employees_by_name_aat;
   --
   l_index full_name_t;
 
   FUNCTION full_name ( employee_in IN employees%ROWTYPE )
      RETURN full_name_t
   IS
   BEGIN
      RETURN employee_in.last_name || '^' || employee_in.first_name;
   END full_name;
BEGIN
   SELECT * BULK COLLECT INTO l_temp_employees
     FROM employees;
 
   FOR indx IN 1 .. l_temp_employees.COUNT
   LOOP
      l_employees ( full_name ( l_temp_employees ( indx ))) := l_temp_employees ( indx );
   END LOOP;
 
   l_index := l_employees.FIRST;
 
   WHILE ( l_index IS NOT NULL )
   LOOP
      DBMS_OUTPUT.put_line( l_index || ' - ' || l_employees ( l_index ).employee_id);
      l_index := l_employees.NEXT ( l_index );
   END LOOP;
END;
/
 

06-2: Use string-indexed collection to keep track of usages

Solution: for 06-2: Use string-indexed collection to keep track of usages

CREATE OR REPLACE PACKAGE string_tracker
/*
Within a particular section of my application, I need to keep
track of whether a certain name has already been used.
 
Write a utility package that provides the following functionality:
 
* Record a name/string as being used.
* Tell me if a particular name is already in use.
* Reset my list.
* Display the list of used names.
*/
 
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 );
   PROCEDURE clear_used_list;
   FUNCTION string_in_use ( value_in IN maxvarchar2_t ) RETURN BOOLEAN;
   PROCEDURE mark_as_used ( value_in IN maxvarchar2_t );
END string_tracker;
/
 
CREATE OR REPLACE PACKAGE BODY string_tracker
IS
   TYPE used_aat IS TABLE OF BOOLEAN INDEX BY maxvarchar2_t;
    g_names_used used_aat;
 
   PROCEDURE clear_used_list
   IS
   BEGIN
      g_names_used.DELETE;
   END clear_used_list;
 
   FUNCTION string_in_use ( value_in IN maxvarchar2_t )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN g_names_used.EXISTS ( value_in );
   END string_in_use;
 
   PROCEDURE mark_as_used ( value_in IN maxvarchar2_t )
   IS
   BEGIN
      g_names_used ( value_in ) := TRUE;
   END mark_as_used;
END string_tracker;
/
 

07. Multi-level collections

07-1: Work with two dimensional arrays

Solution: for 07-1: Work with two dimensional arrays

DECLARE
/*
Define a two dimensional array of strings and perform the following operations:
 
a. Assign "abc" to the cell (100, 300).
b. Assign "def" to the cell (-15, 1000).
c. Write code to check to see if the cell at (5, 100) exists.
*/
-- First with direct access....
   TYPE dim1_aat IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
   TYPE dim2_aat IS TABLE OF dim1_aat INDEX BY PLS_INTEGER;
   twodim_array dim2_aat;
BEGIN
   twodim_array ( 300 ) ( 100 ) := 'abc';
 
   BEGIN
      DBMS_OUTPUT.put_line ( twodim_array ( 1000 ) ( -15 ));
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ( 'No cell defined at (-15,1000)' );
   END;
 
   twodim_array ( 1000 ) ( -15 ) := 'def';
END;
/
 
-- Now the API....
CREATE OR REPLACE PACKAGE twodim
IS
   TYPE dim1_aat IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
   TYPE dim2_aat IS TABLE OF dim1_aat INDEX BY PLS_INTEGER;
 
   PROCEDURE setcell (array_in   IN OUT   dim2_aat , 
                      dim1_in             PLS_INTEGER, 
                      dim2_in             PLS_INTEGER, 
                      value_in   IN       VARCHAR2);
 
   FUNCTION getcell (array_in   IN   dim2_aat, 
                     dim1_in         PLS_INTEGER,
                     dim2_in         PLS_INTEGER)  RETURN VARCHAR2;
 
   FUNCTION EXISTS (array_in   IN   dim2_aat, 
                    dim1_in         PLS_INTEGER, 
                    dim2_in         PLS_INTEGER)   RETURN BOOLEAN;
END twodim;
/
 
CREATE OR REPLACE PACKAGE BODY twodim
IS
   PROCEDURE setcell (array_in   IN OUT   dim2_aat , 
                      dim1_in             PLS_INTEGER, 
                      dim2_in             PLS_INTEGER, 
                      value_in   IN       VARCHAR2)
   IS
   BEGIN
      array_in ( dim2_in ) ( dim1_in ) := value_in;
   END;
 
   FUNCTION getcell (array_in   IN   dim2_aat, 
                     dim1_in         PLS_INTEGER,
                     dim2_in         PLS_INTEGER)  RETURN VARCHAR2
   IS
   BEGIN
      RETURN array_in ( dim2_in ) ( dim1_in );
   END;
 
   FUNCTION EXISTS (array_in   IN   dim2_aat, 
                    dim1_in         PLS_INTEGER, 
                    dim2_in         PLS_INTEGER)   RETURN BOOLEAN
   IS
      l_value VARCHAR2 ( 32767 );
   BEGIN
      l_value := array_in ( dim2_in ) ( dim1_in );
      RETURN TRUE;
   EXCEPTION
      WHEN NO_DATA_FOUND OR VALUE_ERROR
      THEN
         RETURN FALSE;
   END;
END twodim;
/
 
-- And a rewrite of the original anonymous block using the API...
DECLARE
   twodim_array twodim.dim2_aat;
BEGIN
   twodim.setcell ( twodim_array, 100, 300, 'abc' );
 
   IF twodim.EXISTS ( twodim_array, -15, 1000 )
   THEN
      DBMS_OUTPUT.put_line ( twodim.getcell ( twodim_array, -15, 1000 ));
   ELSE
      DBMS_OUTPUT.put_line ( 'No cell defined at (5,1000)' );
   END IF;
 
   twodim.setcell ( twodim_array, 5, 100, 'def' );
END;
/
 

07-2: Multi-level collections and string-based indexes

Solution: for 07-2: Multi-level collections and string-based indexes

CREATE OR REPLACE PACKAGE first_char
IS
   -- Record containing employee ID and last name.
   TYPE emp_info_rt IS RECORD (
      employee_id employees.employee_id%TYPE
    , last_name employees.last_name%TYPE
   );
 
   -- Collection/list of these records, indexed sequentially
   TYPE employees_by_letter_aat IS TABLE OF emp_info_rt INDEX BY PLS_INTEGER;
 
   -- A collection of employee-lastname lists, indexed by a single character string
   TYPE alpha_names_aat IS TABLE OF employees_by_letter_aat INDEX BY VARCHAR2(1);
 
   -- For a given department ID, return the collection of employee-lastname lists.
   FUNCTION emps_in_dept ( department_id_in IN employees.department_id%TYPE ) RETURN alpha_names_aat;
 
   -- Display the contents of such a list.
   PROCEDURE show_emps ( title_in IN VARCHAR2, list_in IN alpha_names_aat );
END first_char;
/
 
CREATE OR REPLACE PACKAGE BODY first_char
IS
   FUNCTION emps_in_dept ( department_id_in IN employees.department_id%TYPE )
      RETURN alpha_names_aat
   IS
      l_temp_emps employees_by_letter_aat;
      l_sorted_emps alpha_names_aat;
      l_letter VARCHAR2(1);
      l_new_row PLS_INTEGER;
   BEGIN
      -- Move all of the employee IDs and last names into a temporary collection.
      SELECT employee_id, last_name BULK COLLECT INTO l_temp_emps
          FROM employees
          WHERE department_id = department_id_in 
             OR department_id_in IS NULL;
 
      -- Sort by the first letter. If we haven't yet assigned the first row for
      -- a given letter, then assign it to index 1. This will avoid a NO_DATA_FOUND exception.
      FOR indx IN 1 .. l_temp_emps.COUNT
      LOOP
         l_letter := SUBSTR(l_temp_emps(indx).last_name, 1, 1 );
 
         IF l_sorted_emps.EXISTS(l_letter) THEN
            -- Add to the end of the list. I assign the expression to a local variable to improve readability.
            l_new_row := l_sorted_emps(l_letter).COUNT + 1;
            l_sorted_emps(l_letter)(l_new_row) := l_temp_emps(indx);
         ELSE
            l_sorted_emps(l_letter)(1) := l_temp_emps(indx);
         END IF;
      END LOOP;
 
      RETURN l_sorted_emps;
   END emps_in_dept;
 
   PROCEDURE show_emps ( title_in IN VARCHAR2, list_in IN alpha_names_aat )
   IS
      -- Display the contents, iterating by each of the letters in alphabetical order.
      l_letter VARCHAR2 ( 1 );
   BEGIN
      DBMS_OUTPUT.put_line ( title_in );
          --
          -- My outer loop goes through a string-indexed collection, so I cannot use a FOR loop. 
          -- My inner loop is a sequentially filled, integer-indexed list so I *CAN* in this case use a FOR loop.
      l_letter := list_in.FIRST;
 
      WHILE (l_letter IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line( ' Employees whose last names start with "' || l_letter || '"' );
 
         -- Sequentially filled, so we can use a FOR loop.
         FOR indx IN 1 .. list_in ( l_letter ).COUNT
         LOOP
            DBMS_OUTPUT.put_line ( '      ' || list_in(l_letter)(indx).employee_id || ', ' || list_in(l_letter)(indx).last_name );
         END LOOP;
 
         -- Move on to the next letter that has last names in the employees table.
         l_letter := list_in.NEXT(l_letter);
      END LOOP;
 
      DBMS_OUTPUT.put_line ( '' );
   END show_emps;
END first_char;
/
 
DECLARE
   l_emps first_char.alpha_names_aat;
BEGIN
   l_emps := first_char.emps_in_dept ( 100 );
   first_char.show_emps ( '*** Employees in department 100', l_emps );
   l_emps := first_char.emps_in_dept ( 50 );
   first_char.show_emps ( '*** Employees in department 50', l_emps );
   l_emps := first_char.emps_in_dept ( NULL );
   first_char.show_emps ( '*** All Employees', l_emps );
END;
/
 

07-3: Extend string usage package to maintain multiple lists

Solution: for 07-3: Extend string usage package to maintain multiple lists

CREATE OR REPLACE PACKAGE string_tracker2
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2(32767);
   PROCEDURE clear_all_lists;
   PROCEDURE clear_used_list ( list_in IN maxvarchar2_t );
   FUNCTION string_in_use ( list_in IN maxvarchar2_t, value_in IN maxvarchar2_t )      RETURN BOOLEAN;
    PROCEDURE mark_as_used ( list_in IN maxvarchar2_t, value_in IN maxvarchar2_t );
END string_tracker2;
/
 
CREATE OR REPLACE PACKAGE BODY string_tracker2
IS
   TYPE used_list_aat IS TABLE OF BOOLEAN INDEX BY maxvarchar2_t;
   TYPE list_of_lists_aat IS TABLE OF used_list_aat INDEX BY maxvarchar2_t;
   g_list_of_lists list_of_lists_aat;
 
   PROCEDURE clear_all_lists
   IS
   BEGIN
      g_list_of_lists.DELETE;
   END clear_all_lists;
 
   PROCEDURE clear_used_list ( list_in IN maxvarchar2_t )
   IS
   BEGIN
      -- Remove the list like it never existed...
      g_list_of_lists.DELETE(list_in);
          -- Or remove all the elements in the list.
          -- g_list_of_lists( list_in ).DELETE;
   END clear_used_list;
 
   FUNCTION string_in_use ( list_in IN maxvarchar2_t, value_in IN maxvarchar2_t )
      RETURN BOOLEAN
   IS
   BEGIN
      -- Assumes that mark_as_used always set row to TRUE.
      RETURN g_list_of_lists(list_in)(value_in);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN FALSE;
   END string_in_use;
 
   PROCEDURE mark_as_used ( list_in IN maxvarchar2_t, value_in IN maxvarchar2_t )
   IS
   BEGIN
      g_list_of_lists(list_in) (value_in) := TRUE;
   END mark_as_used;
END string_tracker2;
/
 

08. Working with collections in SQL statements

08-1: Query contents of PL/SQL variable nested tables and varrays

Solution: for 08-1: Query contents of PL/SQL variable nested tables and varrays

/* A. Create a schema level nested table type that holds strings of up to 100 characters in length. */
 
CREATE OR REPLACE TYPE string_ntt IS TABLE OF VARCHAR2(100);
/
 
/* B. Create a schema level varray type (maximum 500 elements) based
      on an object type that has the same structure (one attribute for each column) as the JOBS table:
 
CREATE TABLE jobs (
  job_id      VARCHAR2(10),
  job_title   VARCHAR2(35),
  min_salary  NUMBER(6),
  max_salary  NUMBER(6)
);
*/
 
CREATE TYPE jobs_ot IS OBJECT (
   job_id VARCHAR2 ( 10 )
 , job_title VARCHAR2 ( 35 )
 , min_salary NUMBER ( 6 )
 , max_salary NUMBER ( 6 )
)
/
 
CREATE OR REPLACE TYPE jobs_vat IS VARRAY(500) OF jobs_ot;
/
 
/* C. Write an anonymous block that declares a local variable based on
      each of the above types. Populate the nested table with your choice
      (and number) of strings. Populate the varray with at least 3 objects.*/
 
DECLARE
   l_strings string_ntt := string_ntt();
   l_jobs jobs_vat := jobs_vat();
BEGIN
   l_strings.EXTEND(100);
 
   FOR indx IN 1 .. 100
   LOOP
      l_strings(indx) := 'PL/SQL is ' || indx || 'X better than Java!';
   END LOOP;
 
   l_jobs.EXTEND ( 3 );
--
   -- Assign values in constructor...
   l_jobs(1) := jobs_ot( '100', 'Toothbrush Scrubber', 1000, 10000 );
--
   -- Assign values explicitly after constructor call...
   l_jobs(2) := jobs_ot ( NULL, NULL, NULL, NULL );
   l_jobs(2).job_id := '200';
   l_jobs(2).job_title := 'Salamander Companion';
   l_jobs(2).min_salary := 54000;
   l_jobs(2).min_salary := 70990;
--
   l_jobs(3) := jobs_ot ( '300', 'Spoon Concavity QA', 120000, 475000 );
 
/*
   D. After populating the collections, write cursor FOR loops based on
      SELECT statements that retrieve the information from these collections
      and display them using DBMS_OUTPUT.PUT_LINE. */
 
   FOR rec IN ( SELECT COLUMN_VALUE FROM TABLE(l_strings)
                  ORDER BY COLUMN_VALUE )
   LOOP
      DBMS_OUTPUT.put_line(rec.COLUMN_VALUE);
   END LOOP;
 
   --
   FOR rec IN ( SELECT  * FROM TABLE(l_jobs)
                  ORDER BY min_salary)
   LOOP
      DBMS_OUTPUT.put_line(rec.job_title);
   END LOOP;   
END;
/
 

08-2: Query contents of nested table and varray columns

Solution: for 08-2s: Query contents of nested table and varray columns

-- A. Write a query that displays the department ID and each of the
--    upper-cased, full names of the employees in that department,
--    querying only from the department_denorms table.
 
SELECT department_id, COLUMN_VALUE
  FROM department_denorms, TABLE(employee_names)
/
 
-- B. Show only those employees whose names contain an "E".
 
SELECT dd.department_id, nms.COLUMN_VALUE
  FROM department_denorms dd, TABLE(dd.employee_names) nms
 WHERE nms.COLUMN_VALUE LIKE '%E%'
/
 
 

08-3: Update nested table and varray columns

Solution: for 08-3: Update nested table and varray columns

-- A. Set all the employee IDs in department_denorms table for department 50 to their negative (that is, 500 -> -500).
UPDATE TABLE (SELECT employee_ids
                 FROM department_denorms
                 WHERE department_id = 50 )
   SET COLUMN_VALUE = -1 *  COLUMN_VALUE
/
 
-- B. Show the IDs only of those employees in department 50.
SELECT ids.COLUMN_VALUE
  FROM TABLE ( SELECT employee_ids
                 FROM department_denorms
                 WHERE department_id = 50 ) ids
/

08-4: Populate collection columns with FORALL

Solution: for 08-4: Populate collection columns with FORALL

DECLARE
   TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   l_employees employees_aat;
 
   TYPE denorms_aat IS TABLE OF department_denorms%ROWTYPE INDEX BY PLS_INTEGER;
   l_denorms denorms_aat;
   l_index department_denorms.department_id%TYPE;
BEGIN
   SELECT * BULK COLLECT INTO l_employees
     FROM employees;
 
   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      -- Initialize the row if necessary.
      l_index := l_employees(indx).department_id;
 
      IF NOT l_denorms.EXISTS() THEN
         l_denorms(l_index).department_id := l_index;
         l_denorms(l_index).max_salary := 0;
         l_denorms(l_index).employee_ids := employee_ids_ntt();
         l_denorms(l_index).employee_names := uc_employee_names_vat();
      END IF;
 
      -- Set the maximum salary.
      l_denorms ( l_index ).max_salary := GREATEST(l_denorms(l_index).max_salary, l_employees(indx).salary);

      -- Add the upper cased name.
      l_denorms(l_index).employee_names.EXTEND;
      l_denorms(l_index).employee_names( l_denorms(l_index).employee_names.COUNT) := UPPER(l_employees(indx).first_name|| ' ' || l_employees(indx).last_name);

      -- Add the employee id.
      l_denorms(l_index).employee_ids.EXTEND;
      l_denorms(l_index).employee_ids(l_denorms(l_index).employee_ids.COUNT) := l_employees(indx).employee_id;
   END LOOP;
 
   -- Push the data into the denorm table.
   FORALL indx IN INDICES OF l_denorms
      INSERT INTO department_denorms
           VALUES l_denorms(indx);
   COMMIT;
END;
/
 

09. BULK COLLECT for high performance querying

09-1: Use BULK COLLECT to query limited number of rows from table

Solution: for 09-1: Use BULK COLLECT to query limited number of rows from table

DECLARE
   CURSOR allrows_cur IS SELECT *
                            FROM employees;
   TYPE employees_ntt IS TABLE OF employees%ROWTYPE;
 
   l_employees employees_ntt;
   l_row PLS_INTEGER;
BEGIN
   OPEN allrows_cur;
 
   LOOP
      FETCH allrows_cur
      BULK COLLECT INTO l_employees LIMIT 10;
 
      -- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows are queried. 
      -- Instead, check the contents of the collection to see if you have anything left to process.
      EXIT WHEN l_employees.COUNT = 0;
      -- Process the data, if any.
      l_row := l_employees.FIRST;
      DBMS_OUTPUT.put_line ( '--------------------' );
 
      WHILE ( l_row IS NOT NULL )
      LOOP
         DBMS_OUTPUT.put_line ( l_employees ( l_row ).last_name );
         l_row := l_employees.NEXT ( l_row );
      END LOOP;
   END LOOP;
 
   -- Clean up when done: close the cursor and delete everything
   -- in the collection.
   CLOSE allrows_cur;
 
   l_employees.DELETE;
END;
/
 

09-2: Use BULK COLLECT With EXECUTE IMMEDIATE

Solution: for 09-2: Use BULK COLLECT With EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE show_string_values (
   table_in        IN   VARCHAR2
 , column_in       IN   VARCHAR2
 , after_from_in   IN   VARCHAR2 DEFAULT NULL
)
/*
Write a procedure that accepts as its arguments the following:
 
a. the name of the table to be queried
b. the name of the string column to be queried
c. optional "after from" clause: SQL text that can follow the
   FROM clause of a query
 
Then use BULK COLLECT with EXECUTE IMMEDIATE to retrieve the values
from the specified column.
 
You can use the DBMS_SQL.vARCHAR2s collection type, or define your own.
 
After querying the data, write a loop that iterates through the
contents of the collection and displays the string values.
*/
IS
   l_string_values DBMS_SQL.varchar2s;
BEGIN
   EXECUTE IMMEDIATE    'select '
                     || column_in
                     || ' FROM '
                     || table_in
                     || ' '
                     || after_from_in
   BULK COLLECT INTO l_string_values;
 
   FOR indx IN 1 .. l_string_values.COUNT
   LOOP
      DBMS_OUTPUT.put_line(l_string_values(indx ));
   END LOOP;
END show_string_values;
/
 
BEGIN
   show_string_values ( 'employees', 'last_name', 'where department_id = 50' );
   DBMS_OUTPUT.put_line ( '' );
   show_string_values ( 'employees'
                      , 'first_name'
                      , 'where department_id = 100'
                      );
END;
/
 

10. FORALL for high performance DML

10-1: Working with SAVE EXCEPTIONS

Solution: for 10-1: Working with SAVE EXCEPTIONS

CREATE OR REPLACE PROCEDURE BULK_EXCEPTIONS
IS
   TYPE namelist_t IS TABLE OF VARCHAR2 ( 1000 );
 
   -- employee.last_name%TYPE;
   enames_with_errors namelist_t := namelist_t ( 'ABC', 'DEF', NULL, 'LITTLE', RPAD ( 'BIGBIGGERBIGGEST', 250, 'ABC' ), 'SMITHIE');
   l_name employees.last_name%TYPE;
   e_bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT ( e_bulk_errors, -24381 );
BEGIN
   FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST SAVE EXCEPTIONS
      UPDATE employees SET last_name = enames_with_errors(indx);
   ROLLBACK;         
EXCEPTION
   /* You really DON'T want to be writing code like this....
     WHEN OTHERS THEN
         IF SQLCODE = -24381 */
   WHEN e_bulk_errors THEN
      DBMS_OUTPUT.put_line ( 'Updated ' || SQL%ROWCOUNT || ' rows.' );
 
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line
             (    'Error '
               || indx
               || ' occurred during '
               || 'iteration '
               || SQL%BULK_EXCEPTIONS ( indx ).ERROR_INDEX
               || ' updating name to '
               || enames_with_errors
                                  ( SQL%BULK_EXCEPTIONS ( indx ).ERROR_INDEX )
             );
         DBMS_OUTPUT.put_line('Oracle error is ' || SQLERRM(-1 * SQL%BULK_EXCEPTIONS ( indx ).ERROR_CODE));
 
         IF SQL%BULK_EXCEPTIONS(indx).ERROR_CODE = 12899 THEN
            DBMS_OUTPUT.put_line('Correcting for too long name of "' || enames_with_errors(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX) || '"');
            l_name :=SUBSTR(enames_with_errors(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ), 1, 25);
 
            UPDATE employees SET last_name = l_name;
         END IF;
      END LOOP;
      
      -- Avoid saving all those last names!
      ROLLBACK;

   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ( 'Updated ' || SQL%ROWCOUNT || ' rows.' );
      DBMS_OUTPUT.put_line ( DBMS_UTILITY.format_error_stack );
      -- Avoid saving all those last names!
      ROLLBACK;
END;
/
 

10-2: Use FORALL with a RETURNING clause

Solution: for 10-2: Use FORALL with a RETURNING clause

DECLARE
   -- 1. Declare a collection of employee last names.
   TYPE names_aat IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
   l_filters names_aat;
   l_lucky_few names_aat;
 
   --
   TYPE ids_aat IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
   l_employee_ids ids_aat;
BEGIN
   -- 2. Populate that collection with these three values:
   l_filters ( 1 ) := 'S%';
   l_filters ( 2 ) := 'E%';
   l_filters ( 3 ) := '%A%';
/*
3. Use FORALL to double the salaries of all employees whose last names
   are LIKE the values in each of the rows in that collection. As a part
   of that FORALL, retrieve the employee ID and last name of each person
   who received the salary increase.
*/
   FORALL indx IN 1 .. l_filters.COUNT
      UPDATE employees SET salary = salary * 2
          WHERE last_name LIKE l_filters ( indx )
      RETURNING employee_id, last_name BULK COLLECT INTO l_employee_ids, l_lucky_few;
 
-- 4. Display those employee IDs.
   FOR indx IN 1 .. l_employee_ids.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_employee_ids(indx) || ', ' || l_lucky_few(indx));
   END LOOP;
 
   ROLLBACK;
END;
/
 

10-3: Use FORALL with EXECUTE IMMEDIATE

Solution: for 10-3: Use FORALL with EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE change_string_values (
   table_in          IN   VARCHAR2
 , column_in         IN   VARCHAR2
 , name_filters_in   IN   DBMS_SQL.varchar2s
 , max_length_in     IN   PLS_INTEGER
)
/*
Write a procedure that accepts as its arguments the following:
 
a. the name of the table to be queried
b. the name of the string column to be updated
c. a collection of type DBMS_SQL.VARCHAR2s containing filters (eg, "%S%")
d. maximum length of new string value to be placed back in the specified column
 
Then use FORALL with EXECUTE IMMEDIATE to update all the rows specified in the table,
setting the string column value to a concatenation of the string to itself.
 
You can use the DBMS_SQL.VARCHAR2s collection type, or define your own.
 
After performing the update, issue a ROLLBACK so that the table's data is not changed.
 
Then write an anonymous block to exercise this procedure.
*/
IS
BEGIN
   -- That's a PRETTY CRAZY dynamic SQL statement!
   FORALL indx IN 1 .. name_filters_in.COUNT
      EXECUTE IMMEDIATE    'UPDATE '
                        || table_in
                        || ' SET '
                        || column_in
                        || ' = SUBSTR ('
                        || column_in
                        || ' || '
                        || column_in
                        || ', 1, '
                        || max_length_in
                        || ') WHERE '
                        || column_in
                        || ' LIKE :colval '
                  USING name_filters_in ( indx );
   DBMS_OUTPUT.put_line ( SQL%ROWCOUNT );
 
   FOR indx IN 1 .. name_filters_in.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('Filter ' || name_filters_in(indx) || ' affected ' || SQL%BULK_ROWCOUNT(indx) || ' rows');
   END LOOP;
 
   ROLLBACK;
END change_string_values;
/
 
DECLARE
   l_filters DBMS_SQL.varchar2s;
BEGIN
   l_filters(1) := '%E';
   l_filters(2) := '%S%';
   change_string_values('employees', 'last_name', l_filters , 25);
END;
/
 

10-4: Use FORALL with a collection of records

Solution: for 10-4: Use FORALL with a collection of records

DECLARE
/*
Write an anonymous block that:
 
1. Use BULK COLLECT to populate a collection of records based on the
   employees table with all the employees in department 50.
 
2. Iterate through all the rows, and double the salaries.
 
3. Use FORALL to update those same rows back in the database, using
   the SET ROW = syntax available in Oracle Database 10g.
 
4. Rollback your changes.
*/
   TYPE employees_ntt IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   TYPE employee_ids_ntt IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
   l_employees employees_ntt;
   l_employee_ids employee_ids_ntt;
BEGIN
   SELECT * BULK COLLECT INTO l_employees
      FROM employees
      WHERE department_id = 50;
 
   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      l_employees(indx).salary := l_employees(indx).salary * 2;
      l_employee_ids(indx) := l_employees(indx).employee_id;
   END LOOP;
 
   FORALL indx IN 1 .. l_employees.COUNT
      UPDATE employees SET ROW = l_employees(indx)
       WHERE employee_id = l_employee_ids(indx);
   ROLLBACK;
END;
/
 

10-5: Incremental commit processing with FORALL

Solution: for 10-5: Incremental commit processing with FORALL

CREATE OR REPLACE PROCEDURE raise_across_dept (
   dept_in           IN   employees.department_id%TYPE
 , raise_in          IN   employees.salary%TYPE
 , commit_after_in   IN   PLS_INTEGER
)
IS
   l_last PLS_INTEGER;
   l_start PLS_INTEGER;
   l_end PLS_INTEGER;
 
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
   employee_ids employee_aat;
   TYPE salary_aat IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;
   salaries salary_aat;
BEGIN
   SELECT employee_id, salary BULK COLLECT INTO employee_ids, salaries
     FROM employees
     WHERE department_id = dept_in;
 
   l_last := employee_ids.LAST;
   l_start := employee_ids.FIRST;
 
   LOOP
      EXIT WHEN l_start > l_last;
      l_end := LEAST(l_start + commit_after_in, l_last);
      --
      FORALL indx IN l_start .. l_end
         UPDATE employees SET salary = salaries(indx)
          WHERE employee_id = employee_ids(indx);
      --
      -- Do the COMMIT; here, but we won't to preserve the info in the table....
      -- COMMIT;
      l_start := l_end + 1;
   END LOOP;
 
   COMMIT;
END raise_across_dept;
/
 

10-6: Use FORALL with sparsely-filled collections

Solution: for 10-6s: Use FORALL with sparsely-filled collections

-- A. Use of INDICES OF to leverage sparse collection
 
DECLARE
   TYPE employees_ntt IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   TYPE employee_ids_ntt IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
   l_employees employees_ntt;
   l_employee_ids employee_ids_ntt;
BEGIN
   SELECT * BULK COLLECT INTO l_employees
     FROM employees
     WHERE department_id = 50;
 
   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      IF l_employees(indx).salary > 3500 THEN
         l_employees.DELETE(indx);
      ELSE
         -- Change the salary and set the employee Id for use in the update.
         l_employees(indx).salary := l_employees(indx).salary + 1000;
         l_employee_ids(indx) := l_employees(indx).employee_id;
      END IF;
   END LOOP;
 
   FORALL indx IN INDICES OF l_employees
      UPDATE employees SET ROW = l_employees(indx)
        WHERE employee_id = l_employee_ids(indx);
   ROLLBACK;
END;
/
 
-- B. Use of VALUES OF to leverage an "indirect" collection
 
DECLARE
/*
Write an anonymous block that:
 
1. Use BULK COLLECT to populate a collection of records based on the
   employees table with all the employees in department 50.
 
2. Iterate through all the rows, and double the salaries.
 
3. Use FORALL to update those same rows back in the database, using
   the SET ROW = syntax available in Oracle Database 10g.
 
4. Rollback your changes.
*/
   TYPE employee_ids_aat IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
   TYPE salaries_aat IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;
   TYPE update_indexes_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
 
   l_employee_ids employee_ids_aat;
   l_salaries salaries_aat;
   l_selected update_indexes_aat;
BEGIN
   SELECT employee_id, salary BULK COLLECT INTO l_employee_ids, l_salaries
     FROM employees
     WHERE department_id = 50;
 
   FOR indx IN 1 .. l_employee_ids.COUNT
   LOOP
      IF l_salaries(indx) <= 3500 THEN
         l_selected(l_selected.COUNT + 1) := indx;
         l_salaries(indx) := l_salaries(indx) + 1000;
      END IF;
   END LOOP;
 
   FORALL indx IN VALUES OF l_selected
      UPDATE employees SET salary = l_salaries(indx)
       WHERE employee_id = l_employee_ids(indx);
   ROLLBACK;
END;
/
 
 
 
 

11. Table Functions

11-1: Simple table functions returning scalars

Solution: for 11-1: Simple table functions returning scalars

DROP TYPE names_nt;
 
CREATE TYPE names_nt IS TABLE OF NUMBER;
/
 
CREATE OR REPLACE FUNCTION lotsa_numbers (count_in IN INTEGER)
   RETURN names_nt
IS
   retval   names_nt := names_nt();
BEGIN
   retval.EXTEND(count_in);
 
   FOR indx IN 1 .. count_in
   LOOP
      retval(indx) := indx;
   END LOOP;
 
   RETURN retval;
END lotsa_numbers;
/
 
REM Call the table function within a SELECT staement.
 
SELECT column_value
  FROM TABLE (lotsa_numbers (100)) names;
 

11-2: Streaming table functions

Solution: for 11-2: Streaming table functions

CREATE TABLE  stocktable (
  ticker VARCHAR2(20),
  trade_date DATE,
  open_price NUMBER,
  close_price NUMBER
);
 
BEGIN
   -- Populate the table.
   INSERT INTO stocktable VALUES ( 'ORCL', SYSDATE, 12.5, 12 );
   INSERT INTO stocktable VALUES ( 'QSFT', SYSDATE, 13.2, 13.8 );
   INSERT INTO stocktable VALUES ( 'MSFT', SYSDATE, 27, 27.04 );
 
   FOR indx IN 1 .. 100000
   LOOP
      -- Might as well be optimistic!
      INSERT INTO stocktable VALUES ( 'STK' || indx, SYSDATE, indx, indx + 15 );
   END LOOP;
   COMMIT;
END;
/
 
CREATE TABLE tickertable
(
  ticker VARCHAR2(20),
  pricedate DATE,
  pricetype VARCHAR2(1),
  price NUMBER
)
/
/*
   Note: Must use a nested table or varray of objects
   for the return type of a pipelined function
*/
 
CREATE TYPE tickertype AS OBJECT (
   ticker VARCHAR2 ( 20 )
 , pricedate DATE
 , pricetype VARCHAR2 ( 1 )
 , price NUMBER
);
/
 
CREATE TYPE tickertypeset AS TABLE OF tickertype;
/
 
CREATE OR REPLACE PACKAGE refcur_pkg
IS
   TYPE refcur_t IS REF CURSOR RETURN stocktable%ROWTYPE;
END refcur_pkg;
/
 
CREATE OR REPLACE FUNCTION stockpivot ( dataset refcur_pkg.refcur_t )
   RETURN tickertypeset
IS
   out_obj tickertype := tickertype ( NULL, NULL, NULL, NULL );
 
   --
   TYPE dataset_tt IS TABLE OF stocktable%ROWTYPE INDEX BY PLS_INTEGER;
   l_dataset dataset_tt;
   retval tickertypeset := tickertypeset();
   l_row PLS_INTEGER;
BEGIN
   FETCH dataset BULK COLLECT INTO l_dataset;
   l_row := l_dataset.FIRST;
 
   WHILE ( l_row IS NOT NULL )
   LOOP
      out_obj.ticker := l_dataset(l_row).ticker;
      out_obj.pricetype := 'O';
      out_obj.price := l_dataset(l_row).open_price;
      out_obj.pricedate := l_dataset(l_row).trade_date;
      retval.EXTEND;
      retval(retval.LAST) := out_obj;
      --
      out_obj.pricetype := 'C';
      out_obj.price := l_dataset(l_row).close_price;
      out_obj.pricedate := l_dataset(l_row).trade_date;
      retval.EXTEND;
      retval(retval.LAST) := out_obj;
      --
      l_row := l_dataset.NEXT(l_row);
   END LOOP;
 
   CLOSE dataset;
 
   RETURN retval;
END;
/
 
BEGIN
   INSERT INTO tickertable
      SELECT *
        FROM TABLE (stockpivot(CURSOR(SELECT *
                                        FROM stocktable )));
END;
/
 

11-3: Pipelined table functions

Solution: for 11-3: Pipelined table functions

CREATE OR REPLACE FUNCTION stockpivot_pl (dataset refcur_pkg.refcur_t)
   RETURN tickertypeset PIPELINED
IS
   l_row_as_object tickertype := tickertype (NULL, NULL, NULL, NULL);
 
   TYPE dataset_tt IS TABLE OF dataset%ROWTYPE INDEX BY PLS_INTEGER;
   l_dataset dataset_tt;
   retval tickertypeset := tickertypeset();
   l_row pls_integer;
BEGIN
   FETCH dataset BULK COLLECT INTO l_dataset;
   CLOSE dataset;
 
   l_row := l_dataset.FIRST;
 
   WHILE (l_row IS NOT NULL)
   LOOP
      -- first row
      l_row_as_object.ticker := l_dataset(l_row).ticker;
      l_row_as_object.pricetype := 'O';
      l_row_as_object.price := l_dataset(l_row).open_price;
      l_row_as_object.pricedate := l_dataset(l_row).trade_date;
 
      PIPE ROW (l_row_as_object);
 
      -- second row
      l_row_as_object.pricetype := 'C';
      l_row_as_object.price := l_dataset(l_row).close_price;
      l_row_as_object.pricedate := l_dataset(l_row).trade_date;
 
      PIPE ROW (l_row_as_object);
      l_row := l_dataset.NEXT (l_row);
   END LOOP;
 
   RETURN;
END;
/

12. Multiset operations on nested tables

12-1: Check for equality between nested tables

Solution: for 12-1: Check for equality between nested tables

DECLARE
   TYPE name_ntt IS TABLE OF employees.last_name%TYPE;
 
   -- I initialize these two lists to demonstrate some odd
   -- behavior regarding NULL values in these structures.
   ln50d name_ntt := name_ntt('a', 'b', NULL );
   ln50a name_ntt := name_ntt('b', 'a', NULL );
   ln100 name_ntt := name_ntt();
BEGIN
   IF ln50a = ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are equal');
   ELSIF ln50a != ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are not equal');
   ELSE
      DBMS_OUTPUT.put_line('NULL');
   END IF;
 
   SELECT last_name BULK COLLECT INTO ln50d
       FROM employees
       WHERE department_id = 50
       ORDER BY last_name DESC;
 
   SELECT last_name BULK COLLECT INTO ln50a
       FROM employees
       WHERE department_id = 50
       ORDER BY last_name ASC;
 
   SELECT last_name BULK COLLECT INTO ln100
     FROM employees
     WHERE department_id = 100;
 
   IF ln50a = ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are equal');
   ELSIF ln50a != ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are not equal');
   ELSE
      DBMS_OUTPUT.put_line('NULL');
   END IF;
 
   IF ln100 = ln50d THEN
      DBMS_OUTPUT.put_line('LN100 and LN50D are equal');
   ELSIF ln100 != ln50d THEN
      DBMS_OUTPUT.put_line('LN100 and LN50D are not equal');
   ELSE
      DBMS_OUTPUT.put_line('NULL');
   END IF;
 
   ln50d.EXTEND;
   ln50d(ln50d.LAST) := NULL;
   ln50a.EXTEND;
   ln50a(ln50a.LAST) := 'X';
 
   IF ln50a = ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are equal');
   ELSIF ln50a != ln50d THEN
      DBMS_OUTPUT.put_line('LN50A and LN50D are not equal');
   ELSE
      DBMS_OUTPUT.put_line('NULL');
   END IF;
 
   IF ln100 = ln50d THEN
      DBMS_OUTPUT.put_line('LN100 and LN50D are equal');
   ELSIF ln100 != ln50d THEN
      DBMS_OUTPUT.put_line('LN100 and LN50D are not equal');
   ELSE
      DBMS_OUTPUT.put_line('NULL');
   END IF;
END;
/
 

12-2: Use set operators with nested tables

Solution: for 12-2: Use set operators with nested tables

DECLARE
/*
Write an anonymous block to populate three different nested tables of strings as follows:
 
1, the last names of all employees in department 50 (I will refer to this as LN50)
 
2. the last names of all employees in department 100 (I will refer to this as LN100)
 
3. the last names of all employees whose last names contain an "e" (LNE)
 
Write code to show the contents of the collections that result from the following operations:
 
LN50 unioned with LN100
LN50 unioned with LNE, including duplicates
LN50 unioned with LNE, excluding duplicates
LNE minus LN50
LNE minus LN100
*/
   TYPE name_ntt IS TABLE OF employees.last_name%TYPE;
 
   -- I initialize these two lists to demonstrate some odd
   -- behavior regarding NULL values in these structures.
   ln50 name_ntt := name_ntt();
   ln100 name_ntt := name_ntt();
   lne name_ntt := name_ntt();
 
   PROCEDURE show_name_ntt ( title_in IN VARCHAR2, list_in IN name_ntt )
   IS
   BEGIN
      DBMS_OUTPUT.put_line (title_in || ' has ' || list_in.COUNT || ' elements' );
 
      FOR indx IN 1 .. list_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line( '   ' || list_in(indx));
      END LOOP;
   END show_name_ntt;
BEGIN
   SELECT last_name BULK COLLECT INTO ln50
     FROM employees
     WHERE department_id = 50;
 
   SELECT last_name BULK COLLECT INTO ln100
     FROM employees
     WHERE department_id = 100;
 
   SELECT last_name BULK COLLECT INTO lne
     FROM employees
     WHERE UPPER ( last_name ) LIKE '%E%';
 
   show_name_ntt('LN50', ln50);
   show_name_ntt('LN100', ln100);
   show_name_ntt('LNE', lne);
   show_name_ntt('LN50 unioned with LN100', lne multiset union ln100);
   show_name_ntt('LN50 unioned with LNE with duplicates', lne multiset union lne);
   show_name_ntt('LN50 unioned with LNE without duplicates', lne multiset union distinct lne);
   show_name_ntt('LNe minus LN50', lne multiset except ln50);
   show_name_ntt('LNe minus LN100', lne multiset except ln100);
END;
/
 

A. Collection utilities and applications

A-1e: Is the value found in the collection?

Solution: for A-1: Is the value found in the collection?

CREATE OR REPLACE FUNCTION element_found (
   -- Replace with your own type here, as needed.
   collection_in   IN   DBMS_SQL.VARCHAR2S
  ,value_in        IN   VARCHAR2
  ,start_index_in  IN   BINARY_INTEGER DEFAULT NULL
  ,end_index_in    IN   BINARY_INTEGER DEFAULT NULL
  ,nulls_eq_in     IN   BOOLEAN DEFAULT TRUE
)
   RETURN BOOLEAN
/*
   Program name: matching_row
 
   Overview:
 
   Returns TRUE if the value is found in the collection between
   the specified index values.
 
   Parameters:
 
      collection_in
         The collection to be searched for the specified value.
 
      value_in
         The value to be checked for in the collection.
 
      start_index_in - starting index for search; default is first index in
                       collection as returned by the FIRST method.
 
      end_index_in - ending index for search; default is first index in
                       collection as returned by the FIRST method.
 
      nulls_eq_in - if TRUE, then if you pass NULL for value_in and
                    an element in the collection is NULL, this function
                    will return TRUE. That is, NULL = NULL in this case.
 
   Dependencies/Restrictions:
 
   It checks for equality of collection elements with an = operator.
   The datatype of the collection must, therefore, support that
   syntax. This will be true for scalars, for example, but not
   for records, object types, etc.
 
   Exceptions raised:
 
   Modification History:
 
*/
IS
   -- Replace with your own type here, as needed.
   /* Set start and end indexes of search. Do not all these values to fall
      outside the first and last indexes in the collection. */
   l_start BINARY_INTEGER := GREATEST (NVL (start_index_in, collection_in.FIRST),collection_in.FIRST);
   l_end BINARY_INTEGER := LEAST (NVL (end_index_in, collection_in.LAST), collection_in.LAST);
   --
   l_index BINARY_INTEGER := l_start;
   l_element_found  BOOLEAN DEFAULT FALSE;
BEGIN
   -- If value is NULL, then return NULL unless NULL=NULL.
   IF value_in IS NULL AND NOT (NVL (nulls_eq_in, FALSE)) THEN
      l_element_found := NULL;
 
   -- If nothing in collection, the element cannot be found.
   ELSIF l_start IS NULL OR l_end IS NULL THEN
      l_element_found := FALSE;
 
   ELSE
      -- Scan through the contents of the collection until we are out
      -- of elements or we found a match.
      WHILE ( l_index IS NOT NULL
             AND l_index BETWEEN l_start AND l_end
             AND NOT l_element_found)
      LOOP
         /* Do we have a match?
                    Note: we do not assign this expression directly to the
                    Boolean variable, because it might evaluate to NULL, and
                    we don't want that outcome passed on to l_element_found. */
         IF collection_in(l_index) = value_in
            OR (nulls_eq_in AND collection_in (l_index) IS NULL AND value_in IS NULL) THEN
            l_element_found := TRUE;
         END IF;
 
         IF NOT l_element_found THEN
            -- Go to the next element.
            l_index := collection_in.NEXT (l_index);
         END IF;
      END LOOP;
   END IF;
 
   RETURN l_element_found;
END element_found;
/

A-2: Move sequentially filled collection to a primary key-indexed collection

Solution: for A-2: Move sequentially filled collection to a primary key-indexed collection

DECLARE
   SUBTYPE full_name_t IS VARCHAR2(32767);
   TYPE employee_tt IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   TYPE employees_by_name_tt IS TABLE OF employees%ROWTYPE INDEX BY full_name_t;
 
   temp_employee_cache employee_tt;
   employee_cache employee_tt;
   employee_by_names_cache employees_by_name_tt;
BEGIN
   SELECT * BULK COLLECT INTO temp_employee_cache
     FROM employees;
 
   FOR indx IN 1 .. temp_employee_cache.FIRST
   LOOP
      employee_cache(temp_employee_cache(indx).employee_id) := temp_employee_cache(indx);
      -- Ensure uniqueness with a delimiter.
      employee_by_names_cache(temp_employee_cache(indx).last_name || '^' || temp_employee_cache(indx).first_name) := temp_employee_cache(indx);
   END LOOP;
 
   temp_employee_cache.DELETE;
END;
 

A-3: Remove gaps from a sparsely-filled collection (make it dense).

Solution: for A-3: Remove gaps from a sparsely-filled collection (make it dense).

CREATE OR REPLACE PROCEDURE densify (
   collection_inout IN OUT DBMS_SQL.VARCHAR2S
  ,same_start_in IN BOOLEAN DEFAULT TRUE
  ,start_at_in IN PLS_INTEGER DEFAULT 1
)
/*
   Program name: densify
 
   Overview:
 
   Remove any gaps from the collection (applies only to associative arrays
   and nested tables, since varrays cannot have gaps).
 
   Parameters:
 
      collection_inout
         The collection from which gaps are to be removed.
 
      same_start_in
         TRUE if the densified collection should have the same starting row.
 
      start_at_in
         The starting row of the densified collection. Only used if the
         same_start_in argument is not set to TRUE.
 
   Dependencies/Restrictions:
 
   Exceptions raised:
 
   Modification History:
 
*/
IS
   l_start PLS_INTEGER;
   l_collection DBMS_SQL.VARCHAR2S;
   l_row PLS_INTEGER;
BEGIN
   -- Determine starting row for compressed collection.
   IF same_start_in THEN
      l_start := collection_inout.FIRST;
   ELSE
      l_start := NVL (start_at_in, 1);
   END IF;
 
   l_row := collection_inout.FIRST;
 
   -- For each element in the original collection, copy it to the
   -- sequentially allocated position in the local version.
   WHILE (l_row IS NOT NULL)
   LOOP
      -- Go to last defined row and add the start position to it.
      l_collection (l_start + l_collection.COUNT) := collection_inout (l_row);
      l_row := collection_inout.NEXT (l_row);
   END LOOP;
 
   -- Now copy the local, dense collection back to the IN OUT argument.
   collection_inout := l_collection;
END densify;
/

A-4: Return the COUNT of elements in a collection between the specified start and end indices.

Solution: for A-4: Return the COUNT of elements in a collection between the specified start and end indices.

CREATE OR REPLACE FUNCTION count_between (
   -- Replace with your own type here, as needed.
   collection_in    IN   DBMS_SQL.VARCHAR2S
  ,start_index_in   IN   BINARY_INTEGER DEFAULT NULL
  ,end_index_in     IN   BINARY_INTEGER DEFAULT NULL
  ,inclusive_in     IN   BOOLEAN DEFAULT TRUE
)
   RETURN PLS_INTEGER
/*
   Program name: count_between
 
   Overview:
 
   Return the number of elements defined within the specified index range.
 
   Parameters:
 
      collection_in - the collection to be counted
 
      start_index_in - starting index for count; default is first index in
                       collection as returned by the FIRST method.
 
      end_index_in - ending index for count; default is first index in
                       collection as returned by the FIRST method.
 
      inclusive_in - pass TRUE if you want the endpoints to be counted
                     in the result (only if the elements exist).
 
   Dependencies/Restrictions:
 
   Exceptions raised:
 
   Modification History:
 
*/
IS
   l_start   BINARY_INTEGER := start_index_in;
   l_end     BINARY_INTEGER := end_index_in;
   l_index   BINARY_INTEGER;
   l_count   PLS_INTEGER DEFAULT 0;
BEGIN
   IF start_index_in IS NULL THEN
      l_start := collection_in.FIRST;
   END IF;
 
   IF end_index_in IS NULL THEN
      l_end := collection_in.LAST;
   END IF;
 
   l_index := l_start;
 
   WHILE (l_index <= l_end)
   LOOP
      IF l_index = l_end AND NOT inclusive_in THEN
         NULL;
      ELSIF l_index = l_start AND NOT inclusive_in THEN
         NULL;
      ELSIF collection_in.EXISTS(l_index) THEN
         l_count := l_count + 1;
      END IF;
 
      l_index := collection_in.NEXT(l_index);
   END LOOP;
 
   RETURN l_count;
END count_between;
/