Insert the possible combinations of two arrays in to another array in PLSQL

by Zubair
(Bangalore, India)

I have two arrays Course and time. I am trying to create another array with all the possible combinations of these two arrays. For eg:


DECLARE type course_array IS table of number;

type time_array IS table of number;

type combination_array is table of number;

Course_pref course_array;

Time_pref time_array;

combination combination_array;

BEGIN

Course_pref := course_array(1, 2, 4);

Time_pref:= time_array(1, 2, 3, 5);

I want the an array which would look like

combination:= combination_array((1,1),(1,2),(1,4),(2,1),(2,2),(2,4)......)

Comments for Insert the possible combinations of two arrays in to another array in PLSQL

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 24, 2015
Rating
starstarstarstarstar
Combining PL/SQL arrays
by: Anonymous

WHat you're describing is a two dimensional PL/SQL collection, i.e. a collection of collections which is very easy to create although it can be a little confusing initially. The top level (first dimension) of this PL/SQL collection would be time and the inner level (second dimension would be course.

Therefore instead of defining combination_array as a table of number we define it as a table of course_array which is defined as a table of number. This means that every element of the array, instead of being a single number is in fact a table of numbers.

The new declaration of combination_array then replaces "number" with "course_array". That automatically makes it a two dimensional collection.

The only question is whether we want the outer array to be a PL/SQL associative array or a nested table.

If the elelements in the PL/SQL collection will be densely packed then a nested table would be a better choice but this would involve some extra coding as we would have to extend the table every time we wanted to add an element.

By making the outer most PL/SQL collection an associative array (indexed by pls_integer) we gain more flexibility and eliminate a little coding which is always good.

Having decided on an array of tables of numbers we just have to figure out how to access each element of each dimesnion and how to populate it. Fortunately both tasks are easy.

As each element of the array is of type course_array we can just assign Course_pref to each element of the associative array - as they are the same type - like this:

combination(1):= Course_pref;

For only a few elements we could write an assignmnet statement for each one individually. However it's bette rto use a loop to access each element of Time_pref and use the number in each element as the index into the fist dimension of our PL/SQL collection.

Putting this all together we end up with PL/SQL code like the following:


DECLARE

type course_array IS table of number;

type time_array IS table of number;

type combination_array is table of course_array index by pls_integer;

Course_pref course_array;

Time_pref time_array;

combination combination_array;

BEGIN

  Course_pref := course_array(1, 2, 4);
  Time_pref:= time_array(1, 2, 3, 5);

  for i in Time_pref.first..Time_pref.last loop
     combination(Time_pref(i)):= Course_pref;
  end loop;

END;


Note that when adding the courses to each element of combination, the element we want is not the index of time_pref but the number contained in each element of time_perf. Otherwise the elements populated in combination would be 1-4 instead of 1-3 and 5. In other words we're using the time value as the index to the outermost collection.

To access the elements in the combined PL/SQL collection we just need to loop through each element in the outer collection and each element in the inner collection in turn.


j := combination.first;

while J <= combination.last loop
  for k in combination(j).first..combination(j).last loop
     dbms_output.put_line(combination(j)(k));
  end loop;
  j := combination.next(j);
end loop;


As the outer PL/SQL collection is sparsely populated (element #4 is missing) we have to be careful that we don't attempt to access non-existant elememts. One way of avoiding this is to use the "next" property of the array which skips missing elements, the other is to use the "exists" property which returns true if the spepcifed element in the PL/SQL collection exists.


FOR j IN combination.first..combination.last LOOP
IF combination.EXISTS(j) THEN
  for k in combination(j).first..combination(j).last loop
     dbms_output.put_line(combination(j)(k));
  END LOOP;
  END IF;
END LOOP;

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.