Skip to content
Advertisement

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?

Advertisement

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.

10 People found this is helpful
Advertisement