Oracle Database 11g: Advanced PL/SQL — Question 2

Examine the commands:
CREATE TYPE typ_course_tab IS VARRAY(5) OF VARCHAR2(20) /
CREATE TYPE typ_course_nst AS TABLE OF typ_course_tab /
CREATE TABLE faculty (faculty_id NUMBER(5), faculty_name VARCHAR2(30), courses typ_course_nst) NESTED TABLE courses STORE AS course_stor_tab /
INSERT INTO faculty VALUES (101, 'Jones', NULL);
UPDATE (SELECT courses FROM faculty WHERE faculty_id=101) SET courses = typ_course_nst(11,'Oracle');
Which statement is true about the execution of these commands?

Answer options

• A. All the commands execute successfully.
• B. Only the first two commands execute successfully.
• C. Only the first four commands execute successfully.
• D. Only the first three commands execute successfully.

Correct answer: C

Explanation

The first four commands execute successfully; the creation of types and the table is valid. However, the last command fails because you cannot insert a value into a nested table when it is initialized to NULL. Thus, the correct answer is C, as the first four commands are successful.