Section 12.5.  Maintaining Schema-Level Collections

12.5. 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. 12.5.1. Necessary Privileges When they live in the database, collection datatypes can be shared by more than one Oracle user (schema). As you can imagine, privileges are involved. Fortunately, it's not complicated; only one Oracle privilegeEXECUTEapplies 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 modulespackages, procedures, functionsare 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, UDPATE, 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 collection column, that user must have the EXECUTE privilege on the type because that is the only way to use the default constructor. 12.5.2. Collections and the Data Dictionary There are a few new entries in the data dictionary that will be very helpful in managing your nested table and VARRAY collection types (see Table 12-4). The shorthand dictionary term for user-defined types is simply TYPE. Collection type definitions are found in the USER_SOURCE view (or DBA_SOURCE, or ALL_SOURCE). Table 12-4. Data dictionary entries for collection types To answer the question ... Use this view As in What collection types have I created? USER_TYPES SELECT type_name FROM user_types WHERE typecode ='COLLECTION'; What was the original type definition of collection Foo_t? USER_SOURCE SELECT text FROM user_source WHERE name = 'FOO_T' AND type = 'TYPE' ORDER BY line; What columns implement Foo_t? USER_TAB_ COLUMNS SELECT table_name,column_name FROM user_tab_columns WHERE data_type = 'FOO_T'; What database objects are dependent on Foo_t? USER_DEPENDENCIES SELECT name, type FROM user_dependencies WHERE referenced_name='FOO_T';