[Chapter 19] 19.8 Collections Housekeeping

19.8 Collections Housekeeping

Here are some not-so-obvious bits of information that will assist you in using nested tables and VARRAYS.

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 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
);

EXECUTE privileges are also required by users who need to run PL/SQL anonymous blocks that uses 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, 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, the user must have the EXECUTE privilege on the type, because that is the only way to use the default constructor.

19.8.2 Data Dictionary

There are a few new entries in the data dictionary (shown in Table 19.3) that will be very helpful in managing your collection types. 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 19.3: 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 type_code = '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';

19.8.3 Call by Reference or Call by Value

Under certain circumstances that are beyond the control of the programmer, PL/SQL will pass collection arguments by reference rather than by value. The rationale is that since collections can be large, it is more efficient to pass only a pointer (call by reference) than to make a copy of the collection (call by value). Usually, the compiler's choice of parameter passing approach is invisible to the application programmer.

Not knowing whether the compiler will pass arguments by reference or by value can lead to unexpected results if all of the following conditions are met:

You have created a procedure or function "in line" in another module's declaration section (these are known as nested or local program units and are explained in Chapter 15, Procedures and Functions).
The inline module refers to a "global" collection variable declared outside its definition.
In the body of the outer module, this collection variable is passed as an actual parameter to the inline module.

This is a rather uncommon combination of affairs in most PL/SQL programs. If you are in the habit of using "in line" module definitions, it's probably not a good idea to rely on the value of global variables anyway!


19.7 Example: PL/SQL-to-Server Integration		19.9 Which Collection Type Should I Use?

19.8 Collections Housekeeping

19.8.1 Privileges

19.8.2 Data Dictionary

19.8.3 Call by Reference or Call by Value