I have a table with numerical values and I want to fetch that values into an array. Can I do that? Is there a function that allows me to do this? I am relatively new to pl/sql and I don't know. Thanks!
3 Answers
set serveroutput on;
DECLARE
TYPE v_arr IS VARRAY(100) OF NUMBER;
var v_arr;
return_value number;
BEGIN
var:=v_arr();
FOR c1 IN (SELECT ID FROM table_name WHERE ROWNUM<100)
loop
var.EXTEND;
var(var.last):=c1.id;
end loop;
FOR i IN var.FIRST..var.LAST
loop
return_value:=var(i);
dbms_output.put_line(return_value);
end loop;
end;
Comments
Yes, you can use the LOOP-approach, but I would like to prefer the BULK COLLECT INTO.
Below my little example:
SET SERVEROUTPUT ON
SET FEEDBACK OFF
CLEAR
DECLARE
TYPE TT_ARR IS TABLE OF NUMBER;
V_NUM_ARR TT_ARR;
BEGIN
V_NUM_ARR := TT_ARR(); --<-- explicit initializing
-- LOOP approach:
FOR L_I IN (SELECT 1 + ROUND(DBMS_RANDOM.VALUE() * 10) COL_1
FROM DUAL
CONNECT BY LEVEL < 20) LOOP
V_NUM_ARR.EXTEND;
V_NUM_ARR(V_NUM_ARR.LAST) := L_I.COL_1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Collection size : ' || TO_CHAR(V_NUM_ARR.COUNT));
-- BULK COLLECT INTO approach (preferred)
SELECT 1 + ROUND(DBMS_RANDOM.VALUE() * 10) COL_1 BULK COLLECT
INTO V_NUM_ARR
FROM DUAL
CONNECT BY LEVEL < 20;
DBMS_OUTPUT.PUT_LINE('Collection size : ' || TO_CHAR(V_NUM_ARR.COUNT));
END;
/
Comments
Assume your table is YOUR_TABLE, and the column is YOUR_COLUMN, you can use VARRAY arrays as follows:
DECLARE
TYPE t_ARR IS VARRRAY(1000000) OF NUMBER;
varr_col T_ARR;
BEGIN
varr_col := t_ARR();
FOR i IN (SELECT YOUR_COLUMN
FROM YOUR_TABLE)
LOOP
varr_col.EXTEND;
varr_col (i) := i.YOUR_COLUMN;
END LOOP;
EXCEPTION
---
END;
This will create variable size array. With every iteration of FOR LOOP, it will extend the array by one and insert the value. Hence, it is flexible in terms of the number of the rows for that particular column.
4 Comments
katy
I have one more question. I tried to use this code on a program that I have and i get an error that varr_col is uninitialized. How do i initialize it?
Hawk
@katy Yes, there was initialization line missing. I added it.
Asif
@Hawk didn't you meant
varr_col (i) := i.YOUR_COLUMN; rather than varr_col (1) := i.YOUR_COLUMN; ?Hawk
@Asif yes, thank you for the correction ... updated