DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: multilevel Collections

  1. #1
    Join Date
    Jul 2000
    Hi Everybody,.
    Does Multilevel Collections and Bulk Sql is supported by 8.1.7...i know it is supported by 9i...if anybody have any idea pls give the suggestions.

    for example i tried the follo......

    SQL> create type t1 is varray(10) of integer;
    2 /

    Type created.

    SQL> create table tab1(c1 t1);

    Table created.

    SQL> insert into tab1 values(t1(1234,1235,1236));

    1 row created.

    SQL> insert into tab1 values(t1(2,3,4));

    1 row created.

    SQL> declare
    2 type t2 is table of t1;
    3 v2 t2;
    4 begin
    5 select c1 bulk collect into v2 from tab1;
    6 dbms_output.put_line(v2.count) ;
    7 end;
    8 /
    type t2 is table of t1;
    ERROR at line 2:
    ORA-06550: line 2, column 12:
    PLS-00534: A Table type may not contain a nested table type or VARRAY.
    ORA-06550: line 2, column 1:
    PL/SQL: Item ignored

    thanks in advance

  2. #2
    Join Date
    May 2000

    A multilevel collection :release 9.0.1 onward

    A multilevel collection is any PL/SQL datatype composed of collections in which tables and records are mingled. In general, it may take several subscripts to specify a particular element of a multilevel collection. For example:


    would specify the k element of the table which is at the (j+2) position of the collection which is at the i element of the collection toplevel. Two rules apply to these multiple subscript expressions.

    1) The subscript expressions may be evaluated in any order.

    2) If the evaluation of any subscript expression causes the modification of the top level collection or any of its sub-collections, the effect of the entire expression is undefined and the value developed by the expression is undetermined.

    In practice, it is unlikely that subscripts will modify the collection being subscripted. However, the problem to worry about is something like this. As above, the collection being subscript is called "toplevel". Assume that this is the subscripted collection


    where f is a local function in which the declaration of the collection toplevel is visible. The declaration of f might be something like this:

    function f(i binary_integer) return binary_integer is
    toplevel.delete(i); -- Rule 2 prohibits this.
    return i;
    end f;

    When f(i) is used as part of the subscript, the deletion of the element of toplevel violates Rule 2. Such side-effects of subscripting on the dynamically allocated storage provided by PL/SQL are very difficult to catch during execution without very expensive implementations of the subscripting operation.

    Please note that the compiler cannot and will not diagnose, in general, attempts to break Rule 2. Also note that this is a simple and obvious way to break Rule 2. There are undoubtedly more complicated ways. However, it seems likely that all such ways are the outcome of bad programming practices and should and can be rewritten.

    Also note that these examples use collections made up only of tables. However, the rules apply even when the multilevel collections are built from a combination of tables and records; the use of tables without records was only to keep the examples simple.

    These rules apply, from Oracle9i release 9.0.1 onward, to subscripting operations applied to ADTs in PL/SQL as well as to PL/SQL native multilevel collections.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width