How to use an Oracle Associative Array in a SQL query
I would create a database type like this:
create type v2t as table of varchar2(30);
/
And then in the procedure:
FOR i IN 1..associativeArray.COUNT LOOP
databaseArray.extend(1);
databaseArray(i) := associativeArray(i);
END LOOP;
OPEN refCursor FOR
SELECT T.*
FROM SOME_TABLE T,
( SELECT COLUMN_VALUE V
FROM TABLE( databaseArray )
) T2
WHERE T.NAME = T2.V;
(where databaseArray is declared to be of type v2t.)
How does select column into associative array from table work in Oracle database
If i = 100, then array element (i * 3) just means element 300. Your code puts the value for employee 100 into emp_table(300), it doesn't create three entries.
first and last are collection methods that return the first and last index values respectively.
emp_table.first..emp_table.last is part of FOR loop syntax and defines a loop that will step through all integers from emp_table.first to emp_table.last inclusive. This only works if the collection index consists of consecutive integers. It looks like your loop will print values of i from 300 to 315, which will work, but it will fail with "ORA-01403: no data found" if you try to access emp_table(i) for i = 301. Up to Oracle 19c, you have to use the next collection method to find the next value within a loop indexed by non-consecutive integers, or strings.
From Oracle 21c you can use the more convenient iterator syntax:
declare
type aa is table of number index by pls_integer;
a aa;
n pls_integer := 1;
begin
a(n) := 123;
a(n*3) := 456;
dbms_output.put_line('"myArray" contains '||a.count||' elements, from '||a.first||' to '||a.last);
dbms_output.new_line();
dbms_output.put_line('Indices:');
for i in indices of a loop
dbms_output.put_line(i);
end loop;
dbms_output.new_line();
dbms_output.put_line('Values:');
for v in values of a loop
dbms_output.put_line(v);
end loop;
dbms_output.new_line();
dbms_output.put_line('Pairs:');
for i, v in pairs of a loop
dbms_output.put_line(i||': '||v);
end loop;
end;
"myArray" contains 2 elements, from 1 to 3
Indices:
1
3
Values:
123
456
Pairs:
1: 123
3: 456
Oracle Associative arrays, how to define?
You are creating a schema type; those can be nested tables or varying arrays (varrays), but not associative arrays.
From the documentation for the create type statement:
A standalone collection type that you create with the
CREATE TYPEstatement differs from a collection type that you define with the keywordTYPEin a PL/SQL block or package. For information about the latter, see "Collection Variable Declaration".With the
CREATE TYPEstatement, you can create nested table andVARRAYtypes, but not associative arrays. In a PL/SQL block or package, you can define all three collection types.
PL/SQL: Selecting from a table into an assoc array
Just read your comment on APC's answer, it sounds like you figured this out on your own. But I figured I'd put the answer in anyway for future searchers.
This is simpler code, but does not have the speed advantage of using BULK COLLECT. Just loop through the rows returned by the query and set the elements in the associative array individually.
DECLARE
TYPE VarAssoc IS TABLE OF varchar2(200) INDEX BY varchar2(30);
vars VarAssoc;
BEGIN
FOR r IN (SELECT table_name,tablespace_name FROM user_tables) LOOP
vars(r.table_name) := r.tablespace_name;
END LOOP;
dbms_output.put_line( vars('JAVA$OPTIONS') );
END;
Calling Oracle Associative Array from UPDATE subquery gives PLS-00201 error
An associative array collection is a PL/SQL only data type; it cannot be used in SQL statements.
From the Oracle documentation:
Table 5-1 PL/SQL: Collection Types
Collection Type Number of Elements Index Type Dense or Sparse Uninitialized Status Where Defined Can Be ADT Attribute Data Type Associative array (or index-by table) Unspecified String or PLS_INTEGER Either Empty In PL/SQL block or package No VARRAY (variable-size array) Specified Integer Always dense Null In PL/SQL block or package or at schema level Only if defined at schema level Nested table Unspecified Integer Starts dense, can become sparse Null In PL/SQL block or package or at schema level Only if defined at schema level Oracle pl/sql - Use associative array with user created record
You don't need a
RECORDdeclaration here, although you could use it if you know it. It is better to useCURSOR%ROWTYPE.syntax as shown. There is also no need of associative array, as your indexes are going to be numbers anyway.Also, instead of looping through the
CURSOR, you could useBULK COLLECT INTO.You cannot directly pass
vars(ind)to.PUT_LINE(). It should refer to specific column names.SET serveroutput ON
DECLARE
CURSOR studentcursor IS
SELECT student.student_id,
student.first_name,
student.last_name,
AVG(grade.numeric_grade) AS GRADE
FROM studentdb.student
inner join studentdb.grade
ON studentdb.student.student_id =
studentdb.grade.student_id
GROUP BY student.student_id,
student.first_name,
student.last_name;
TYPE studentarray
IS TABLE OF studentcursor%ROWTYPE;
vars STUDENTARRAY;
BEGIN
OPEN studentcursor;
FETCH studentcursor BULK COLLECT INTO vars;
FOR ind IN vars.first .. vars.last LOOP
dbms_output.put_line(vars(ind).student_id
||','
|| vars(ind).first_name
||','
||vars(ind).last_name
||','
|| vars(ind).grade);
END LOOP;
END;Issue selecting from Associative Array using a Table Collection Expression
It appears that when you use:
SELECT ...
BULK COLLECT INTO array
FROM ...Then the first thing that happens is that the
arrayyouBULK COLLECT INTOis re-initialised to an empty array.Therefore, when you want to use it in the table collection expression it is already empty and no rows are generated.
Instead, you could use a non-associative array and use the
MULTISEToperators in PL/SQL:CREATE OR REPLACE PACKAGE table_test
IS
TYPE range IS RECORD
(
StartDate DATE,
EndDate DATE
);
TYPE range_table IS TABLE OF range
--INDEX BY PLS_INTEGER
;
END;
/
DECLARE
l_ranges table_test.range_table := table_test.range_table();
l_ranges2 table_test.range_table := table_test.range_table();
PROCEDURE output_ranges(
range_array table_test.range_table
)
IS
idx PLS_INTEGER;
BEGIN
dbms_output.put_line('Table Size: '||range_array.COUNT);
idx := range_array.FIRST;
LOOP
EXIT WHEN idx IS NULL;
dbms_output.put_line(
idx||': '||range_array(idx).StartDate||' - '||range_array(idx).EndDate
);
idx := range_array.NEXT(idx);
END LOOP;
END output_ranges;
BEGIN
l_ranges.EXTEND(2);
l_ranges(1) := table_test.range(DATE '2000-01-01', DATE '2001-01-01');
l_ranges(2) := table_test.range(DATE '2001-01-01', DATE '2002-01-01');
l_ranges2.EXTEND(2);
l_ranges2(1) := table_test.range(DATE '2002-01-01', DATE '2003-01-01');
l_ranges2(2) := table_test.range(DATE '2003-01-01', DATE '2004-01-01');
output_ranges(l_ranges);
output_ranges(l_ranges2);
l_ranges := l_ranges MULTISET UNION ALL l_ranges2;
output_ranges(l_ranges);
END;
/Which outputs:
Table Size: 2
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
Table Size: 2
1: 01-JAN-02 - 01-JAN-03
2: 01-JAN-03 - 01-JAN-04
Table Size: 4
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
3: 01-JAN-02 - 01-JAN-03
4: 01-JAN-03 - 01-JAN-04
Related Topics
How to Get Previous Business Day in a Week with That of Current Business Day Using SQL Server
Keep Only N Last Records in SQLite Database, Sorted by Date
SQL - Best Practice for a Friendship Table
Between Clause Versus <= and >=
Combination of 'Like' and 'In' Using T-Sql
SQL Delete Rows Based on Another Table
Return Only One Row from the Right-Most Table for Every Row in the Left-Most Table
How to Count the Number of Columns in a Table Using SQL
How to Split the Results of a Select Query into Two Equal Halfs
How to Search All Text Fields in a Db for Some Substring with T-Sql
Mysql: Which to Use When: Drop Table, Truncate Table, Delete from Table
Column Conflicts with the Type of Other Columns in the Unpivot List
Postgresql: Not in Versus Except Performance Difference (Edited #2)
Determine Row That Caused "Unexpected End of File" Error in Bulk Insert
How to Bulk Insert a File into a *Temporary* Table Where the Filename Is a Variable
Postgresql - Group by Clause or Be Used in an Aggregate Function