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?
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.