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 ?
1
-
possible duplicate of stackoverflow.com/questions/7012625/…Wolfgang– Wolfgang2016-06-16 13:47:13 +00:00Commented Jun 16, 2016 at 13:47
Add a comment
|
4 Answers
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;
Comments
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
Comments
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;
Comments
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;
/
