Type tabArray IS TABLE OF TABLE%ROWTYPE;
tableArray tabArray ;
--fill array
SELECT *
BULK COLLECT INTO tableArray
FROM TABLE
WHERE TABLE.field = ....
--work
FOR pos IN 1..tableArray .count
LOOP
dbms_output.put_line(pos||' '||audArray(pos).field);
end loop;
--doesn't work
SELECT * TABLE2
WHERE TABLE2.field in (SELECT filed FROM FORALL tableArray );
Main question: how can I use my array in sql statement (in) ?
First you have to create a type in SQL then can use as given below
CREATE TYPE FRUIT_TT AS TABLE OF VARCHAR2(100)
SELECT column_value AS val
FROM TABLE(FRUIT_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
Here a type FRUIT_TT is created and using it in SQL query.
Here is an example, you just need to adjust your SQL statement.
CREATE TYPE col_ntt IS TABLE OF NUMBER;
CREATE TABLE num_tab
(
col NUMBER
);
INSERT INTO num_tab VALUES(1);
INSERT INTO num_tab VALUES(2);
INSERT INTO num_tab VALUES(4);
DECLARE
l_col1 col_ntt := col_ntt(1, 2, 3);
l_col2 col_ntt;
BEGIN
SELECT *
BULK COLLECT INTO l_col2
FROM num_tab
WHERE col IN (SELECT column_value FROM TABLE(l_col1));
FOR indx IN 1..l_col2.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_col2(indx));
END LOOP;
END;
/*
1
2
*/
