Copying one associative array to another.

If you have two associative arrays (PL/SQL tables), and want to copy the contents of one to another, rather than using inefficient loops, you can do it using BULK COLLECT.

The example below shows array arr1 first being populated with a singe value. Then arr2 is populated with a list of values. In order to copy arr2 back to arr1, I use the BULK COLLECT method, which then overwrites whatever was in arr1.

[sourcecode language=”sql”]
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
arr1 t_tab;
arr2 sys.Odcivarchar2list;
idx PLS_INTEGER;
BEGIN

SELECT ‘hello’ BULK COLLECT INTO arr1 FROM dual;
dbms_output.put_line(‘ arr1: ‘);
dbms_output.put_line(arr1(1));
dbms_output.put_line(‘————-‘);

SELECT table_name BULK COLLECT INTO arr2 FROM user_tables;

SELECT COLUMN_VALUE BULK COLLECT INTO arr1 FROM TABLE(arr2);

dbms_output.put_line(‘ arr2: ‘);
FOR idx IN 1 .. arr1.count LOOP
dbms_output.put_line(arr1(idx));
END LOOP;

END;
[/sourcecode]

Output:

SQL> /
arr1:
hello
-------------
arr2:
DEPT
EMP
BONUS
SALGRADE
EMP_COPY

PL/SQL procedure successfully completed.

SQL>

Leave a Reply

Your email address will not be published. Required fields are marked *