How to store a column of result of select query in an array?

Question

If we have a column in a table of type number, how can we store the result of select query on that column in an array ?

Answer 1

This sample uses a list (table of numbers) to achieve this, because i find those lists much more handy:

CREATE OR REPLACE TYPE numberlist AS TABLE OF NUMBER;

DECLARE 
   v_numberlist numberlist;
BEGIN
   SELECT intval numbercolumn
     BULK COLLECT INTO v_numberlist
     FROM lookup;
   FOR i IN 1..v_numberlist.count
   LOOP
      dbms_output.put_line( v_numberlist(i) );
   END LOOP;     
END;

Answer 2

Create a type which store number:-

CREATE OR REPLACE TYPE varray is table of number;

--write your select query inside for loop () where i am extracting through level

 declare
      p  varray := varray();
    BEGIN
      for i in (select level from dual connect by level <= 10) loop
        p.extend;
        p(p.count) := i.level;
      end loop;
      for xarr in (select column_value from table(cast(p as varray))) loop
         dbms_output.put_line(xarr.column_value);
     end loop;
  END;

output:-

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

Answer 3

Just an option to use some native SQL datatype. Hope it helps.

SET SERVEROUTPUT ON;
DECLARE
  lv_num_tab DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT LEVEL BULK COLLECT INTO lv_num_tab FROM DUAL CONNECT BY LEVEL < 10;
  FOR I IN lv_num_tab.FIRST..lv_num_tab.LAST
  LOOP
    dbms_output.put_line(lv_num_tab(i));
  END LOOP;
END;

Answer 4

You may also want to put the whole select in a table. You can use a BULK COLLECT to an array:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);    

CREATE OR REPLACE 
PROCEDURE get_tables(p_owner in varchar2)
as
  v_res   t_my_list;
  v_qry   varchar2(4000) := '';
begin

    v_qry :=  ' SELECT table_name from all_tables where owner='''||p_owner||'''';            
    dbms_output.put_line(v_qry);    
    -- all at once in the table
    execute immediate v_qry bulk collect into v_res;
    FOR I in 1..v_res.count 
    loop
        dbms_output.put_line(v_res(i));
    end loop;

exception
when others then
    raise;
end get_tables;
/

begin
    get_tables('E') ;
end;
/