Multidimensional Arrays In Oracle PL/SQL

Multidimensional arrays in Oracle PL/SQL can solve some tricky situations you cannot do with nested tables or it will be a bit complex. A multidimensional array is an array of arrays 🙂 In this article, I will only discuss two-dimensional arrays because three or more dimensions are extremely hard to manage and maintain (in any language).

Feel free to explore this script below that shows you how to initialize, populate, loop through, manipulate multidimensional arrays and some other basic usage.

DECLARE 
  -- define the dimension
  TYPE t_user_rec_type IS RECORD (id_user INTEGER
                                   , user_name VARCHAR2(255)
                                   , email_address VARCHAR2(255));
                                   
  -- store dimensions in an index-by table                                 
  TYPE t_user_tab_type IS TABLE OF t_user_rec_type INDEX BY PLS_INTEGER;
  
  -- init table
  user_tab t_user_tab_type; 
  admin_tab t_user_tab_type; 
  
  -- test variable
  v_test_string VARCHAR2(255);
  v_test_id INTEGER;
 
BEGIN
 
   -- populate table   
   user_tab(1).id_user := 10001;
   user_tab(1).user_name := 'John Doe'; 
   user_tab(1).email_address := 'john@doe.com';   
   
   user_tab(2).id_user := 10002;
   user_tab(2).user_name := 'Jimmy Chue'; 
   user_tab(2).email_address := 'jimmy@chue.com';  
   
   -- populate another table
   admin_tab(1).id_user := 90001;
   admin_tab(1).user_name := 'Jane Foo';
   admin_tab(1).email_address := 'jane@foo.com';
      
   admin_tab(2).id_user := 10002;
   admin_tab(2).user_name := 'Jimmy Chue'; 
   admin_tab(2).email_address := 'jimmy@chue.com'; 
  
   
   /***
      EXAMPLES OF WORKING WITH TABLES 
                                    ***/
   
   -- print out everything 
   FOR i IN user_tab.first .. user_tab.last 
   LOOP 
       dbms_output.put_line(user_tab(i).id_user||': '||user_tab(i).user_name||' has the email '||user_tab(i).email_address);
   END LOOP;
  
   --print a specific value only:
   dbms_output.put_line(user_tab(2).email_address);
   
   -- check number of records
   dbms_output.put_line(user_tab.count);
   
   -- get id for the first member in a table
   v_test_id := user_tab.first;
   dbms_output.put_line('First User: '||user_tab(v_test_id).user_name);      
      
   -- get last member in the table
   v_test_id := admin_tab.last; -- last
   dbms_output.put_line('Last Admin: '||admin_tab(v_test_id).user_name);

   

   -- assign a value from one array to another
   dbms_output.put_line(user_tab(1).user_name); -- print user_name for the first element in USERs
   user_tab(1).user_name := admin_tab(1).user_name; -- assign user_name of the first element in ADMINs to the first element in USERs (update the value)
   dbms_output.put_line(user_tab(1).user_name); -- print NEWLY ASSIGNED username
   
END;