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 l_arr1 first being populated with a singe value. Then l_arr2 is populated with a list of values. In order to copy l_arr2 back to l_arr1, I use the BULK COLLECT method, which then overwrites whatever was in l_arr1.

DECLARE
    TYPE t_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
    l_arr1 t_tab;
    l_arr2 sys.Odcivarchar2list;
    l_idx  PLS_INTEGER;
BEGIN

    -- Create an array with a single element.
    SELECT 'hello' BULK COLLECT 
      INTO l_arr1 
      FROM dual;

    -- Display contents of l_arr1, which is just one row saying hello
    dbms_output.put_line('l_arr1: ');
    dbms_output.put_line(l_arr1(1));
    dbms_output.put_line('-------------');
    dbms_output.put_line('');
    
    -- Create an array with 4 user tables
    SELECT table_name BULK COLLECT 
      INTO l_arr2 
      FROM user_tables 
     WHERE rownum < 5;
    
    -- Copy table names from l_arr1 to l_arr2
    SELECT COLUMN_VALUE BULK COLLECT 
      INTO l_arr1 
      FROM TABLE(l_arr2);
    
    -- Display contents of l_arr2, which is now a list of table names
    dbms_output.put_line('l_arr2: ');
    FOR l_idx IN 1 .. l_arr1.count LOOP
        dbms_output.put_line(l_arr1(l_idx));
    END LOOP;

END;
/