Re: PL/SQL type table index by binary_integer -- auto initialization??

Martin T. schrieb:
> Hi all.
> (Oracle 9.2.0.1.0, Windows XP)
>
> All documentation I've found states that you have to initialize a
> "record" in a PL/SQL type table (via extend) to use it.
>
> However consider the following script:
> ---
> DECLARE
> TYPE char_table IS TABLE OF VARCHAR2(1000);
> TYPE char_idx_table IS TABLE OF VARCHAR2(1000) INDEX BY
> BINARY_INTEGER;
> v_t1 char_table;
> v_t2 char_idx_table;
> v_t1_2 char_table := char_table();
> BEGIN
> FOR rec IN (
> SELECT rownum row_number, object_id, object_name FROM ALL_OBJECTS
> WHERE rownum < 20 )
> LOOP
> /* This works! */
> v_t2(rec.object_id) := rec.object_name;
> /* Correct output: */
> dbms_output.put_line('Set '||rec.object_id||' to
> '||v_t2(rec.object_id)||' ...');
>
> /* ORA-06531: Reference to uninitialized collection: */
> --v_t1(rec.object_id) := rec.object_name;
>
> /* ORA-06533: Subscript beyond count */
> -- v_t1_2(rec.object_id) := rec.object_name;
>
> v_t1_2.extend;
> /* ORA-06533: Subscript beyond count */
> -- v_t1_2(rec.object_id) := rec.object_name;
>
> /* This works (in combination with the extend above) */
> v_t1_2(rec.row_number) := rec.object_name;
> END LOOP;
> END;
> /
> ---
>
> Can someone tell me where this behaviour is documented?
>
> thanks!
>
> best,
> Martin
>

For Associative Arrays:

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#34012 <quote>
Associative Arrays

For associative arrays (also known as index-by tables), use the syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL]

    INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];     INDEX BY key_type;

The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760). The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.

An initialization clause is not required (or allowed). </quote>

For other collections:
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#19834 <quote>
Initializing and Referencing Collections

Until you initialize it, a nested table or varray is atomically null: the collection itself is null, not its elements. To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function "constructs" collections from the elements passed to it.

You must explicitly call a constructor for each varray and nested table variable. (Associative arrays, the third kind of collection, do not use constructors.) Constructor calls are allowed wherever function calls are allowed.
</quote>

Best regards

Maxim Received on Tue Sep 12 2006 - 04:38:24 CDT