Home » SQL & PL/SQL » SQL & PL/SQL » Array in PLSQL
Array in PLSQL [message #298547] Wed, 06 February 2008 05:55 Go to next message
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 #298549 is a reply to message #298547] Wed, 06 February 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no predefined size.
The limit is your memory.

Regards
Michel
Re: Array in PLSQL [message #298551 is a reply to message #298549] Wed, 06 February 2008 06:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: find recurring balance
Next Topic: Not able to compile wrapped package bodies.
Goto Forum:
  


Current Time: Thu Feb 06 16:12:28 CST 2025