Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Help
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;
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;
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
![]() |
![]() |