Is there a way to loop through selected columns in plsql

I have a table TestTable with columns of col_test1, col_test2, col_test3 … and I want to create a loop that accesses each of these columns individually and find the max value and place it in the variable made in the declare block and simply dbms.out.put it.

Declare 
    my_array sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('col_test1','col_test2','col_test2');
    v_test number(8,0);
Begin
    for r in my_array.first..my_array.last 
    loop
        select max(my_array(r)) into v_test from TestTable;
        dbms_output.put_line(v_test);
    end loop;
End;
/

The output I get is just the string ‘col_test1’which should be 50. This is done through oracle SQL. Is there any way to achieve this?

Answer

You could use dynamic SQL for this

Declare 
    my_array sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('col_test1','col_test2','col_test2');
    v_test number(8,0);
Begin
    for r in my_array.first..my_array.last 
    loop
        execute immediate 'select max(' || my_array(r) || ') from TestTable'
                     into v_test;
        dbms_output.put_line(v_test);
    end loop;
End;

If you’re going to resort to dynamic SQL, however, it would generally make more sense to build a single SQL statement that took that max of all three columns in one pass rather than potentially doing three separate table scans on the same table.