Lab 18.1 Exercises

18.1.1 Use Index-By Tables

In this exercise, you will learn more about index-by tables discussed earlier in the chapter.

Create the following PL/SQL script:

 -- ch18_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    CURSOR course_cur IS       SELECT description         FROM course;    TYPE course_type IS TABLE OF course.description%TYPE       INDEX BY BINARY_INTEGER;    course_tab course_type;    v_counter INTEGER := 0; BEGIN    FOR course_rec IN course_cur LOOP       v_counter := v_counter + 1;       course_tab(v_counter) := course_rec.description;    END LOOP; END; 

Answer the following questions:

a)	Explain the script ch18_1a.sql.
b)	Modify the script so that rows of the index-by table are displayed on the screen.
c)	Modify the script so that only first and last rows of the index-by table are displayed on the screen.
d)	Modify the script by adding the following statements and explain the output produced: Display the total number of elements in the index-by table after it has been populated on the screen. Delete the last element, and display the total number of elements of the index-by table again. Delete the fifth element, and display the total number of elements and the subscript of the last element of the index-by table again.

18.1.2 Use Nested Tables

In this exercise, you will learn more about nested tables discussed earlier in this chapter.

Answer the following questions:

a)	Modify the script 18_1a.sql used in Exercise 18.1.1. Instead of using an index-by table, use a nested table.
b)	Modify the script by adding the following statements and explain the output produced: Delete the last element of the nested table, and then reassign a new value to it. Execute the script. Trim the last element of the nested table, and then reassign a new value to it. Execute the script.
c)	How would you modify the script created, so that there is no error generated when a new value is assigned to the trimmed element?