Oracle PL/SQL Programming, 5th Edition

Maintaining Schema-Level Collections

Here are some not-so-obvious bits of information that will assist you in using nested tables and VARRAYS. This kind of housekeeping is not necessary or relevant when working with associative arrays.

Necessary Privileges

When they live in the database, collection datatypes can be shared by more than one database user (schema). As you can imagine, privileges are involved. Fortunately, it’s not complicated; only one Oracle privilege—EXECUTE—applies to collection types.

If you are Scott, and you want to grant Joe permission to use color_tab_t in his programs, all you need to do is grant the EXECUTE privilege to him:

GRANT EXECUTE on color_tab_t TO JOE;

Joe can then refer to the type using schema.type notation. For example:

CREATE TABLE my_stuff_to_paint (
   which_stuff VARCHAR2(512),
   paint_mixture SCOTT.color_tab_t
)
NESTED TABLE paint_mixture STORE AS paint_mixture_st;

EXECUTE privileges are also required by users who need to run PL/SQL anonymous blocks that use the object type. That’s one of several reasons that named PL/SQL modules—packages, procedures, functions—are generally preferred. Granting EXECUTE on the module confers the grantor’s privileges to the grantee while executing the module.

For tables that include collection columns, the traditional SELECT, INSERT, UPDATE, and DELETE privileges still have meaning, as long as there is no requirement to build a collection for any columns. However, if a user is going to INSERT or UPDATE the contents of a ...