Pavel Kaplya Pavel Kaplya - 1 month ago 9
SQL Question

select multiple columns of single row as elements of array

I have a table with 100 columns called like col_1, col_2, .. col_100
is there a way to select values of that columns of single into an array of 100 elements?

(Oracle 10.2)

Answer

Here's a brute force method. There's probably a more elegant way, or at least one that will cut down on typing. The example uses five columns rather than 100.

DECLARE
  -- Change VARCHAR2(10) in the next line to your col_1 .. col_100 type
  TYPE My100Array IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  myVals My100Array;
  indx NUMBER;
BEGIN
   SELECT 'These', 'are', 'the', 'column', 'values'
     INTO myVals(1), myVals(2), myVals(3), myVals(4), myVals(5)
     FROM DUAL;
   FOR INDX IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(indx || ': ' || myVals(indx));
   END LOOP;
END;

Here's the output when I run this:

1: These
2: are
3: the
4: column
5: values

Of course, this will be a bit tough with 100 columns, but once you get the query out of the way you'll have the array as you want it.