Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 19.7 Example: PL/SQL-to-Server IntegrationChapter 19
Nested Tables and VARRAYs
Next: 19.9 Which Collection Type Should I Use?

19.8 Collections Housekeeping

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

19.8.1 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 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?


SELECT type_name
  FROM user_types
 WHERE type_code = 'COLLECTION';

What was the original type definition of collection Foo_t?


  FROM user_source
 WHERE name = 'FOO_T'
   AND type = 'TYPE'
ORDER BY line;

What columns implement Foo_t?


SELECT table_name, column_name
  FROM user_tab_columns
 WHERE data_type = 'FOO_T';

What database objects are dependent on Foo_t?


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:

  1. 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).

  2. The inline module refers to a "global" collection variable declared outside its definition.

  3. 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!

Previous: 19.7 Example: PL/SQL-to-Server IntegrationOracle PL/SQL Programming, 2nd EditionNext: 19.9 Which Collection Type Should I Use?
19.7 Example: PL/SQL-to-Server IntegrationBook Index19.9 Which Collection Type Should I Use?

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.