Copying one associative array to another.

May 9, 2012 at 11:21 am | Posted in Oracle Developement, PL/SQL | Leave a comment
Tags: , ,

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.

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;

Output:

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

PL/SQL procedure successfully completed.

SQL>

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: