Re: PL/SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 08 Jun 1999 12:52:50 GMT
Message-ID: <375f11b9.3231787@newshost.us.oracle.com>

A copy of this was sent to "Paul Davies" <cobalt_at_dircon.co.uk> (if that email address didn't require changing) On Tue, 8 Jun 1999 11:37:45 +0100, you wrote:

>OK. I got round the problem of initialisation by initialising the empty
>array. And I'm now using table collections. But when I run the following
>code
>
>cursor c1 is select * from TABLE_A;
>type myarray is table of TABLE_A.COL_A%type;
>thearray myarray := myarray();
>
>i int := 1;
>
>begin
> for c1_rec in c1 loop
> thearray(i) = c1_rec.COL_A;
> i := i + 1;
> end loop;
> end;
>
>I get a "Subscript beyong count" error (TABLE_A contains about 20 rows)
>
>Please help!!
>
>

you have to call EXTEND on nested table types (your type) or varrays -- it is only PLSQL table types that autoextend themselves. Your example could be coded as follows:

declare

    cursor c1 is select * from emp;
    type myArray is table of emp.ename%type;     theArray myArray := myArray();
begin

    for c1_rec in c1 loop

        theArray.extend;
        theArray( theArray.count ) := c1_rec.ename;

    end loop;
end;
/

If you create the myArray type as a SQL type, you can do this 'cooler' (and it works a little faster as it does an array fetch). For example:

create or replace type myArray as table of varchar2(30); /

declare

    theArray myArray;
begin

    select cast( multiset( select ename from emp ) as myArray )

      into theArray
      from dual;

    for i in 1 .. theArray.count loop
        dbms_output.put_line( theArray(i) );

    end loop;
end;
/

Does the same thing....
>
>
>
>
>Paul Davies wrote in message <375ce91c_at_newsread3.dircon.co.uk>...
>>How do I insert the values returned by a select into a varray.
>>
>>So, I have a table, TABLE and I want to select COL_A and insert into the
>>varray:
>>
>>cursor c1 is select * from TABLE
>>type myarray is varray(100) of TABLE.COL_A%TYPE;
>>
>>thearray myarray;
>> i int := 1;
>>
>>begin
>> for c1_rec in c1 loop
>> thearray(i) = c1_rec.COL_A /*this is what I want to do, but it
>>does not compile*/
>> i := i + 1;
>> end loop;
>>end;
>>
>>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jun 08 1999 - 07:52:50 CDT