I recently received this email:
Hi Steven,
I was reading your article on 2 dimensional arrays. I was wondering if I could apply this technique to solve the following problem.
I have a table with 48 columns that are named as follows: no_1, no_2.......no_48.
I need to do a calculation on all columns no_1 then no_2 ... no_48 and l would like to do it without having to repeat the code 48 times. Could I use a two dimensional array to hold this data and then run my calculations?
Here is the answer I sent to him:
I don't think that a two-dimensional array is going to help in this case, though I do encourage you to keep the technique in mind for the future. Why won't it help? Well, you need to come up with a way to extract the value of a specific, numbered column from within a query. You can fetch a whole row easily enough, but you will not be able to use dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL) to work with that record. Records are PL/SQL structures and cannot be bound into a dynamic PL/SQL block.
Instead, however, you can use EXECUTE IMMEDIATE to obtain a specific column value, and then execute your function on that value. The following statements create a table and function, and then a driver PL/SQL block to demonstrate the technique.
DROP TABLE five_columns
/
CREATE TABLE five_columns
(
id NUMBER
, val1 NUMBER
, val2 NUMBER
, val3 NUMBER
, val4 NUMBER
, val5 NUMBER
)
/
BEGIN
INSERT INTO five_columns
VALUES (1, 1, 2, 3, 4, 5
);
INSERT INTO five_columns
VALUES (2, 10, 20, 30, 40, 50
);
COMMIT;
END;
/
CREATE OR REPLACE FUNCTION double_value (val_in IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN val_in * 2;
END;
/
DECLARE
TYPE results_tt IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
l_results results_tt;
BEGIN
FOR col_num IN 1 .. 5
LOOP
EXECUTE IMMEDIATE
'DECLARE l_curval NUMBER;
BEGIN SELECT val' || col_num
|| ' INTO l_curval FROM five_columns WHERE id = :id;'
|| ' :newval := double_value (l_curval);
END;'
USING IN 1 /* The ID value */
, OUT l_results (col_num);
END LOOP;
FOR indx IN 1 .. l_results.COUNT
LOOP
DBMS_OUTPUT.put_line (l_results (indx));
END LOOP;
END;
/
Now, one problem I can see with this approach is that you execute N queries for each row of data, where N is the number of columns in the table.
Perhaps there is also something you can do with a pivot of the SELECT statement (for an entire row), but pivoting is outside my area of expertise. So I will stop here. Hope this helps!