Lab 18.1 PL/SQL Tables Lab Objectives After this Lab, you will be able to: Use Index-By Tables Use Nested Tables
A PL/SQL table is similar to one-column database table. The rows of a PL/SQL table are not stored in any predefined order, yet when they are retrieved in a variable each row is assigned a consecutive subscript starting at 1, as shown in the in Figure 18.1. Figure 18.1. PL/SQL Table  Figure 18.1 shows a PL/SQL table consisting of integer numbers. Each number is assigned a unique subscript that corresponds to its position in the table. For example, number 3 has subscript 5 assigned to it because it is stored in the fifth row of the PL/SQL table. There are two types of PL/SQL tables: index-by tables and nested tables. They have the same structure, and their rows are accessed in the same way via subscript notation as shown in Figure 18.1. The main difference between these two types is that nested tables can be stored in a database column, and the index-by tables cannot. Index-By Tables The general syntax for creating an index-by table is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER; table_name TYPE_NAME; Notice that there are two steps in the declaration of an index-by table. First, a table structure is defined using the TYPE statement, where TYPE_NAME is the name of the type that is used in the second step to declare an actual table. An ELEMENT_TYPE is any PL/SQL datatype, such as NUMBER, VARCHAR2, or DATE, with some restrictions. The majority of restricted datatypes are beyond the scope of this book and are not mentioned in this chapter. However, you can find the complete list in Oracle help available online. Second, the actual table is declared based on the type specified in the previous step. Consider the following code fragment.  FOR EXAMPLE DECLARE TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER; last_name_tab last_name_type; In this example, type last_name_type is declared based on the column LAST_NAME of the STUDENT table. Next, the actual index-by table last_name_tab is declared as LAST_NAME_TYPE. As mentioned earlier, the individual elements of a PL/SQL table are referenced via subscript notation as follows: table_name(subscript) This technique is demonstrated in the following example. FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER; last_name_tab last_name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END; In this example, the index-by table last_name_tab is populated with last names from the STUDENT table. Notice that the variable v_counter is used as a subscript to reference individual table elements. This example produces the following output: last_name(1): Crocitto last_name(2): Landry last_name(3): Enison last_name(4): Moskowitz last_name(5): Olvsade last_name(6): Mierzwa last_name(7): Sethi last_name(8): Walter last_name(9): Martin last_name(10): Noviello PL/SQL procedure successfully completed.  | It is important to note that referencing a nonexistent row raises the NO_DATA_FOUND exception as follows: |
DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER; last_name_tab last_name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter|| '): '||last_name_tab(v_counter)); END LOOP; DBMS_OUTPUT.PUT_LINE ('last_name(11): '|| last_name_tab(11)); END; This example produces the output shown below: last_name(1): Crocitto last_name(2): Landry last_name(3): Enison last_name(4): Moskowitz last_name(5): Olvsade last_name(6): Mierzwa last_name(7): Sethi last_name(8): Walter last_name(9): Martin last_name(10): Noviello DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 19 Notice that the DBMS_OUTPUT.PUT_LINE statement shown in bold letters raises the NO_DATA_FOUND exception because it references the eleventh row of the table, even though the table contains only ten rows.
Nested Tables The general syntax for creating a nested table is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name TYPE_NAME; Notice that this declaration is very similar to the declaration of an index-by table except that there is no INDEX BY BINARY_INTEGER clause. Just like in the case of an index-by table, there are restrictions that apply to an ELEMENT_TYPE of a nested table. These restrictions are listed in Oracle help available online. It is important to note that a nested table must be initialized before its individual elements can be referenced. Consider the modified version of the example used earlier in this lab. Notice that the last_name_type is defined as a nested table (there is no INDEX BY clause). FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE; last_name_tab last_name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END; This example causes the following error: DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 14 The example causes an error because a nested table is automatically NULL when it is declared. In other words, there are no individual elements yet because the nested table itself is NULL. In order to reference the individual elements of the nested table, it must be initialized with the help of a system-defined function called constructor. The constructor has the same name as the nested table type. For example, last_name_tab := last_name_type('Rosenzweig', 'Silvestrova'); This statement initializes the last_name_tab table to two elements. Note that most of the time, it is not known in advance what values should constitute a particular nested table. So, the following statement produces an empty but non-null nested table. last_name_tab := last_name_type(); Notice that there are no arguments passed to a constructor. Consider a modified version of the example shown previously. FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE; last_name_tab last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab.EXTEND; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END; In this version, the nested table is initialized at the time of the declaration. This means that it is empty, but non-null. In the cursor loop, there is a statement with one of the collection methods, EXTEND. This method allows you to increase the size of the collection. Note that the EXTEND method cannot be used with index-by tables. You will see detailed explanation of various collection methods later in this chapter. Next, the nested table is assigned values just like the index-by table in the original version of the example. When run, the script produces the following output: last_name(1): Crocitto last_name(2): Landry last_name(3): Enison last_name(4): Moskowitz last_name(5): Olvsade last_name(6): Mierzwa last_name(7): Sethi last_name(8): Walter last_name(9): Martin last_name(10): Noviello PL/SQL procedure successfully completed. | | It is important to note the difference between NULL collection and empty collection. If a collection has not been initialized, referencing its individual elements causes the following error: |
DECLARE TYPE integer_type IS TABLE OF INTEGER; integer_tab integer_type; v_counter integer := 1; BEGIN DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter)); END; DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 7 If a collection has been initialized so that it is empty, referencing its individual elements causes a different error: DECLARE TYPE integer_type IS TABLE OF INTEGER; integer_tab integer_type := integer_type(); v_counter integer := 1; BEGIN DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter)); END; DECLARE * ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 7
Collection Methods In the previous examples, you have seen one of the collection methods, EXTEND. A collection method is a built-in function that is called using a dot notation as follows: collection_name.method_date The following list explains collection methods that allow you to manipulate or gain information about a particular collection: EXISTS Returns TRUE if a specified element exists in a collection. This method can be used to avoid raising SUBSCRIPT_OUTSIDE_ LIMIT exceptions. COUNT Returns the total number of elements in a collection. EXTEND Increases the size of a collection. DELETE Deletes either all elements, elements in the specified range, or a particular element from a collection. Note that PL/SQL keeps placeholders of the deleted elements. FIRST and LAST Return subscripts of the first and last elements of a collection. Note that if first elements of a nested table are deleted, the FIRST method returns a value greater than one. If elements have been deleted from the middle of a nested table, the LAST method returns a value greater than the COUNT method. PRIOR and NEXT Return subscripts that precede and succeed a specified collection subscript. TRIM Removes either one or a specified number of elements from the end of a collection. Note that PL/SQL does not keep placeholders for the trimmed elements.
| | Note that DELETE and TRIM methods cannot be used with index-by tables. |
Consider the following example, which illustrates the use of various collection methods. FOR EXAMPLE DECLARE TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; index_by_table index_by_type; TYPE nested_type IS TABLE OF NUMBER; nested_table nested_type := nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10); BEGIN -- Populate index by table FOR i IN 1..10 LOOP index_by_table(i) := i; END LOOP; IF index_by_table.EXISTS(3) THEN DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = '|| index_by_table(3)); END IF; -- delete 10th element from a collection nested_table.DELETE(10); -- delete elements 1 through 3 from a collection nested_table.DELETE(1,3); index_by_table.DELETE(10); DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT = '|| nested_table.COUNT); DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '|| index_by_table.COUNT); DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST = '|| nested_table.FIRST); DBMS_OUTPUT.PUT_LINE ('nested_table.LAST = '|| nested_table.LAST); DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = '|| index_by_table.FIRST); DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST = '|| index_by_table.LAST); DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2) = '|| nested_table. PRIOR(2)); DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2) = '|| nested_table.NEXT(2)); DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = '|| index_by_table.PRIOR(2)); DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2) = '|| index_by_table.NEXT(2)); -- Trim last two elements nested_table.TRIM(2); -- Trim last element nested_table.TRIM; DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '|| nested_table.LAST); END; Consider the output returned by the example: index_by_table(3) = 3 nested_table.COUNT = 6 index_by_table.COUNT = 9 nested_table.FIRST = 4 nested_table.LAST = 9 index_by_table.FIRST = 1 index_by_table.LAST = 9 nested_table.PRIOR(2) = nested_table.NEXT(2) = 4 index_by_table.PRIOR(2) = 1 index_by_table.NEXT(2) = 3 nested_table.LAST = 7 PL/SQL procedure successfully completed. The first line of the output index_by_table(3) = 3 is produced because the EXISTS method returns TRUE, and as a result, the IF statement IF index_by_table.EXISTS(3) THEN DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = '|| index_by_table(3)); END IF; evaluates to TRUE as well. The second and third lines of the output nested_table.COUNT = 6 index_by_table.COUNT = 9 show the results of method COUNT after some elements were deleted from the index-by and nested tables. Next, lines four through seven of the output nested_table.FIRST = 4 nested_table.LAST = 9 index_by_table.FIRST = 1 index_by_table.LAST = 9 show the results of FIRST and LAST methods. Notice that the FIRST method applied to the nested table returns 4 because the first three elements were deleted earlier. Next, lines eight through eleven of the output nested_table.PRIOR(2) = nested_table.NEXT(2) = 4 index_by_table.PRIOR(2) = 1 index_by_table.NEXT(2) = 3 show the results of PRIOR and NEXT methods. Notice that the PRIOR method applied to the nested table returns NULL because the first element was deleted earlier. Finally, the last line of the output nested_table.LAST = 7 shows the value of the last subscript after the last three elements were removed. As mentioned earlier, once the DELETE method is issued, the PL/SQL keeps placeholders of the deleted elements. Therefore, the first call of the TRIM method removed ninth and tenth elements from the nested table, and the second call of the TRIM method removed eighth element of the nested table. As a result, the LAST method returned value 7 as the last subscript of the nested table. 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? |
Lab 18.1 Exercise Answers This section gives you some suggested answers to the questions in Lab 18.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 18.1.1 Answers| a) | Explain the script ch18_1a.sql. | | A1: | Answer: The declaration section of the script contains definition of the index-by table type, course_type. This type is based on the column DESCRIPTION of the table COURSE. Next, the actual index-by table is declared as course_tab. The executable section of the script populates the course_tab table in the cursor FOR loop. Each element of the index-by table is referenced by its subscript, v_counter. For each iteration of the loop, the value of v_counter is incremented by 1 so that each new description value is stored in the new row of the index-by table. | | b) | Modify the script so that rows of the index-by table are displayed on the screen. | | A2: | Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_1b.sql, version 2.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; DBMS_OUTPUT.PUT_LINE('course('||v_counter||'): '|| course_tab(v_counter)); END LOOP; END; Consider another version of the same script. -- ch18_1c.sql, version 3.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; FOR i IN 1..v_counter LOOP DBMS_OUTPUT.PUT_LINE('course('||i||'): '|| course_tab(i)); END LOOP; END; When run, both versions produce the same output: course(1): DP Overview course(2): Intro to Computers course(3): Intro to Programming course(4): Structured Programming Techniques course(5): Hands-On Windows course(6): Intro to Java Programming course(7): Intermediate Java Programming course(8): Advanced Java Programming course(9): JDeveloper course(10): Intro to Unix course(11): Basics of Unix Admin course(12): Advanced Unix Admin course(13): Unix Tips and Techniques course(14): Structured Analysis course(15): Project Management course(16): Database Design course(17): Internet Protocols course(18): Java for C/C++ Programmers course(19): GUI Programming course(20): Intro to SQL course(21): Oracle Tools course(22): PL/SQL Programming course(23): Intro to Internet course(24): Intro to the Basic Language course(25): Operating Systems course(26): Network Administration course(27): JDeveloper Lab course(28): Database System Principles course(29): JDeveloper Techniques course(30): DB Programming in Java PL/SQL procedure successfully completed. | | c) | Modify the script so that only first and last rows of the index-by table are displayed on the screen. | | A3: | Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_1d.sql, version 4.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; DBMS_OUTPUT.PUT_LINE('course('||course_tab.FIRST||'): '|| course_tab(course_tab.FIRST)); DBMS_OUTPUT.PUT_LINE('course('||course_tab.LAST||'): '|| course_tab(course_tab.LAST)); END; Consider the statements course_tab(course_tab.FIRST) and course_tab(course_tab.LAST) used in this example. While these statements look somewhat different from the statements that you have seen so far, they produce the same effect as course_tab(1) and course_tab(30) statements because, as mentioned earlier, the FIRST and LAST methods return the subscripts of the first and last elements of a collection, respectively. In this example, the index-by table contains 30 elements, where the first element has subscript of 1, and the last element has subscript of 30. This version of the script produces the following output: course(1): DP Overview course(30): DB Programming in Java PL/SQL procedure successfully completed. | | 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.
| | A4: | Answer: Your script should look similar to the following script. All changes are shown in bold letters. -- ch18_1e.sql, version 5.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; -- Display the total number of elements in the index-by -- table DBMS_OUTPUT.PUT_LINE ('1. Total number of elements: '|| course_tab.COUNT); -- Delete the last element of the index-by table -- Display the total number of elements in the index-by -- table course_tab.DELETE(course_tab.LAST); DBMS_OUTPUT.PUT_LINE ('2. Total number of elements: '|| course_tab.COUNT); -- Delete the fifth element of the index-by table -- Display the total number of elements in the index-by -- table -- Display the subscript of the last element of the -- index-by table course_tab.DELETE(5); DBMS_OUTPUT.PUT_LINE ('3. Total number of elements: '|| course_tab.COUNT); DBMS_OUTPUT.PUT_LINE ('3. The subscript of the last '|| 'element: '||course_tab.LAST); END; When run, this example produces the following output: 1. Total number of elements: 30 2. Total number of elements: 29 3. Total number of elements: 28 3. The subscript of the last element: 29 PL/SQL procedure successfully completed. First, the total number of the elements in the index-by table is calculated via the COUNT method and displayed on the screen. Second, the last element is deleted via DELETE and LAST methods, and the total number of the elements in the index-by table is displayed on the screen again. Third, the fifth element is deleted, and the total number of the elements in the index-by table and the subscript of the last element are displayed on the screen. Consider the last two lines on the output. After the fifth element of the index-by table is deleted, the COUNT method returns value 28, and the LAST method returns the value 29. Usually, the values returned by the COUNT and LAST methods are equal. However, when an element is deleted from the middle of the index-by table, the value returned by the LAST method is greater than the value returned by the COUNT method because the COUNT method ignores deleted elements. |
18.1.2 Answers| a) | Modify the script 18_1a.sql used in Exercise 18.1.1. Instead of using an index-by table, use a nested table. | | A1: | Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_2a.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; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP; END; | | 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.
| | A2: | Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT description FROM course; TYPE course_type IS TABLE OF course.description%TYPE; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP; course_tab.DELETE(30); course_tab(30) := 'New Course'; END; When run, this version of the script produces the following error: DECLARE * ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 18 In the previous version of the script, the last element of the nested table is removed via the DELETE method. As mentioned earlier, when the DELETE method is used, the PL/SQL keeps a placeholder of the deleted element. Therefore, the statement course_tab(30) := 'New Course'; does not cause any errors. In the current version of the script, the last element of the nested table is removed via the TRIM method. In this case, the PL/SQL does not keep placeholder of the trimmed element because the TRIM method manipulates the internal size of a collection. As a result, the reference to the trimmed elements causes 'Subscript beyond count' error. | | 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? | | A3: | Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_2d.sql, version 4.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT description FROM course; TYPE course_type IS TABLE OF course.description%TYPE; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP; course_tab.TRIM; course_tab.EXTEND; course_tab(30) := 'New Course'; END; In order to reference the trimmed element, the EXTEND method is use to increase the size on the collection. As a result, the assignment statement course_tab(30) := 'New Course'; does not cause any errors. |
Lab 18.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 18.1. | 1) | The main difference between the index-by and nested tables is that _____ nested tables can be stored in a database column, and index-by tables cannot. _____ index-by tables can be stored in a database column, and nested tables cannot.
| | 2) | An index-by table is indexed by what datatype? _____ NUMBER _____ INTEGER _____ BINARY_INTEGER _____ PLS_INTEGER
| | 3) | A nested table must be initialized prior to its use. _____ True _____ False
| | 4) | If a PL/SQL table contains one element _____ the FIRST method returns value of one, and the LAST method returns NULL. _____ the FIRST method returns NULL, and the LAST method returns value of one. _____ the FIRST and LAST methods return a value of one. _____ referring to these methods causes an error.
| | 5) | If a PL/SQL table has eight elements, the DELETE (3, 7) method deletes _____ the third and seventh elements of the collection. _____ the third element and ignores the seventh element of the collection. _____ elements three to seven.
|
| 
