Varray

[swakoo]

hmm..
is it possible to delcare a dynamic array in PL/SQL?
something like arraylist in java.
thanks

 

[swakoo]

for eg: i have a unknown records of string to be stored. eg:
Tom
Mary
Harry
...
...
...

i do not know how many record will be retrieved.

 

[William Robertson]

Yes PL/SQL has a selection of array types (although I would not recommend VARRAY).

Did you want something like

SELECT name BULK COLLECT INTO my_array FROM sometab;

Not being a Java programmer I don't know what dynamic arrays or arraylists are.

 

[swakoo]

hmm...
dynamic array means that it can expand according to the to number of records going to insert.
in varray, i normally do this:
type Record_type is varray(50) of varchar2(500);
Record_varray Record_type := Record_type();
as u notice i have to declare the size of the varray.
is there any other way that do not need to declare the size instead, expand when u are going to add data in.
thanks

 

[William Robertson]

I've never seen the point of VARRAYs in PL/SQL. I think they are mostly for database tables/views.

How about just:

TYPE VARCHAR2_TT IS TABLE OF VARCHAR2(500);

or even, from the SQL prompt:

CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/

and while you are at it,

CREATE TYPE INTEGER_TT AS TABLE OF INTEGER
/

CREATE TYPE NUMBER_TT AS TABLE OF NUMBER
/

GRANT EXECUTE ON VARCHAR2_TT TO PUBLIC;
GRANT EXECUTE ON INTEGER_TT TO PUBLIC;
GRANT EXECUTE ON NUMBER_TT TO PUBLIC;

 

[Marco van der Linden]

Try:

declare
  type
    my_tab_type is table of varchar2(500)
                     index by binary_integer;
  my_tab        my_tab_type;
  v_tab_index   number := 0;
begin
  --
  for <loop condition>
  loop
    v_tab_index := v_tab_index + 1;
    my_tab(v_tab_index) := 'whatever goes here';
  end loop;
end;

 

[William Robertson]

Unless you are still using 8i, Oracle recommends PLS_INTEGER in place of the old BINARY_INTEGER.

I think I read somewhere that the 9i compiler actually substitutes PLS_INTEGER anyway, but all the same I get a feeling of confidence when I'm reading code that uses the recommended type

 

[Marco van der Linden]

@William,

Thanks, seems I have to some catching up to do on reading Oracle docs