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.
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).
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:
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!
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.