Is there a way to loop through selected columns in plsql

Question

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?

This is a weird requirement. You need to be executing the max query dynamically as you don’t know the column until execution time, for this you would use execute immediate usually. That rownum filter looks very out of place, what do you want it to achieve? — Andrew Sayer
– Andrew Sayer, Commented Dec 28, 2020 at 13:19
Ah that rownum filter was from a previous test to just return 1 row, shouldn't be there so will remove from the code above. — Vince
– Vince, Commented Dec 28, 2020 at 13:22

Justin Cave · Accepted Answer · 2020-12-28 13:26:18Z

1

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.

answered Dec 28, 2020 at 13:26

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there a way to loop through selected columns in plsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related