View Exhibit1 and examine the structure of the EMPLOYEES table.
View Exhibit2 and examine the code in the PL/SQL block.
The PL/SQL block fails to execute.
What could be the reason?
A.
Nested tables cannot be returned by a function.
B.
The NEWNAMES nested table has not been initialized.
C.
The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2.
D.
The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types.
E.
LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.
The actual issue is with the SELECT statement not the type being local. Answer E is partly correct. “local collection types are not allowed in SQL statements.”
If you use BULK COLLECT INTO in place of INTO, it works with the local collection. You would alway use BULK COLLECT INTO with collection and simple INTO with scalar variables. The collection does not even need to be initialized /:=Roster()/
The satement about using local PLSQL defined collections in SQL is true but the truth is that SELECT INTO is a combination of a SELECT statement (SQL) and a PLSQL construct hidden by the compiler, where the usage of the collection type is not in the SQL part therefore _YOU_CAN_USE_ SELECT INTO and BULK COLLECT INTO to select into local variables using for example local collection type.
E
E,
Agree with Biga
1.We could use ‘bulk collect into collection’
2.Or we could use into collection(index);
since here the nested table is initialized with empty, not able to use the 2nd way.