Re: Arrays iin PL/SQL

In article <N.121996.215044.24_at_ppp442.enterprise.net>, kengaul_at_enterprise.net says...
>
>PL/SQL table type, Cant give you more info I'm at home but its in the
 manual.
>They must be indexed by binary Integers and can only have one dimension.
>
>Ken.
>
>>
>> Hi,
>>
>> I'm writing a PL/SQL program and would like to know if there's anyway of
>> using an array concept in PL/SQL just like in other lang.
>> If yes, how to do that? Can I also do such thing in PL/SQL, e.g.
>>
>> variable1, variable2, variable3 , ...etc is variables defined as
>> varchar.
>>
>> in my PL/SQL pgm, can I have such code?
>>
>> For T in 1 .. 3 loop
>>
>> tmp := 'variable' || T;
>> test := tmp;
>>
>> end loop;
>>
>> would like tmp to refer to the variable, i.e. variable1, variable2,etc.
>> How can I do that in PL/SQL?
>>
>>
>> Appreciate your help.
>>
>>
>> TIA ....
>>
>>
>> Mun Kong
>
>

Hi,

Yes you declare ARRAYS in PL/SQL just as in 3-GL Languages. Syntax is as below.

declare TYPE <TYPENAME> IS TABLE OF <DATATYPE> INDEX BY BINARY_INTEGER;

        <VARIABLE NAME> <TYPENAME>;

For Example :

TYPE T1 is TABLE of CHAR(10) INDEX BY BINARY INTEGER; ARRAY1 T1; You can then access ARRAY1(1), ARRAY(2) etc as you would access ARRAYS in 3-GLs.

You can use this only in PL/SQL version 2.x, and not in earlier versions.

Just a few comments

1. If you want to select COLUMN1, COLUMN2, COLUMN3 from say TABLE1 and store the results in an array you will have to declare 3 TYPE declarations as below

declare TYPE T1 IS TABLE OF TABLE1.COLUMN1%TYPE INDEX BY BINARY_INTEGER;
        TYPE T2 IS TABLE OF TABLE1.COLUMN2%TYPE INDEX BY BINARY_INTEGER;
        TYPE T3 IS TABLE OF TABLE1.COLUMN3%TYPE INDEX BY BINARY_INTEGER;
        ARRAY1 T1;
        ARRAY2 T2;
        ARRAY3 T3;

You can select from a CURSOR and assign the fetched values to a PL/SQL table using a COUNTER VARIABLE.

If you a filled ARRAY1(1) to ARRAY(10) say and try and access ARRAY1(20) later in the script, then a "ORA-1403 No data found error is raised".

The advantage of using PL/SQL arrays is that they are DYNAMICALLY SIZED and hence you need not know specify the SIZE while declaring the PL/SQL table/array.

Hope this helps.

L.S.Venkatasubramanian (subramanian.venkata_at_sg.eds.com) Received on Mon Dec 23 1996 - 00:00:00 CST