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

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 12 Sep 2006 02:45:13 -0700
Message-ID: <1158054313.786605.188360@e63g2000cwd.googlegroups.com>

Maxim Demenko wrote:
> 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>

>

Thanks a lot! (Another case of doc blindness on my side, I actually was already somewhere in that chapter, but somehow got lost somewhere :)

cheers. Received on Tue Sep 12 2006 - 04:45:13 CDT