Array in PLSQL [message #298547] |
Wed, 06 February 2008 05:55  |
saharookiedba
Messages: 56 Registered: September 2007 Location: PUNE
|
Member |
|
|
Hi,
I am writing a procedure which requires me to use arrays.
Please help me out for the same.
Please tell the methid by which arrays are declared in plsql.
currently i am using
/* ARRAY DECLARATION */
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER ;
NAME_TABLE NAME_TABLE_TYPE ;
I got this from internet, here i am not clear about the size of array..
for E.g s_count[20], here the size of array would be 20.
Thak you in advance
|
|
|
|
Re: Array in PLSQL [message #298551 is a reply to message #298549] |
Wed, 06 February 2008 06:11   |
saharookiedba
Messages: 56 Registered: September 2007 Location: PUNE
|
Member |
|
|
Thank you
Michel
Since i have declared Array like below:-
/* ARRAY DECLARATION */
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER ;
NAME_TABLE NAME_TABLE_TYPE ;
Am i right in sayin that i"ll be able to access the array
as NAME_TABLE(i) in the begin ... end block.
if not please tell me how can i access the array.
|
|
|
Re: Array in PLSQL [message #298558 is a reply to message #298551] |
Wed, 06 February 2008 06:42   |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
Yes.you can access the values using the index of the collections.
declare
type test_tbl is table of varchar2(20) index by binary_integer;
test_tab test_tbl;
begin
test_tab(1) :='name1';
test_tab(2) :='name2';
for i in 1..test_tab.last
loop
dbms_output.put_line(test_tab(i));
end loop;
end;
regards,
[edited the code]
[Updated on: Wed, 06 February 2008 06:44] Report message to a moderator
|
|
|
Re: Array in PLSQL [message #298562 is a reply to message #298558] |
Wed, 06 February 2008 07:29  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Although a problem arises if your collection becomes sparse:
declare
type test_tbl is table of varchar2(20) index by binary_integer;
test_tab test_tbl;
begin
test_tab(1) :='name1';
test_tab(4) :='name2';
for i in 1..test_tab.last
loop
dbms_output.put_line(test_tab(i));
end loop;
end;
Error report:
ORA-01403: no data found
ORA-06512: at line 9
01403. 00000 - "no data found"
*Cause:
*Action:
name1
You can overcome such things with something like:
declare
type test_tbl is table of varchar2(20) index by binary_integer;
test_tab test_tbl;
i VARCHAR2(20);
begin
test_tab(1) :='name1';
test_tab(4) :='name2';
i := test_tab.FIRST;
while i is not null
loop
dbms_output.put_line(test_tab(i));
i := test_tab.next(i);
end loop;
end;
Or you could look at the INDICES OF syntax when using it with FORALL.
|
|
|