ForALL construct: Collection range can be initialized to a constant?

Question

I want to use ForAll construct in procedure:

Existing For loop is:

      max_versions constant number := 100;
      FOR i IN 1 ..max_vers 
        LOOP
           l_sql := 'update test_table set p' || i ||'= NULL WHERE p' ||i||'=0 AND           condition1=' ||n_input1 ||' AND  condition3 = ' || n_condition3;
         EXECUTE immediate l_sql;

           l_sql := 'update test_table set h' || i ||'= NULL WHERE h ||i||'=0 AND                condition1=' ||n_input1 ||' AND  condition3 = ' || n_condition3;
         EXECUTE immediate l_sql;
        END LOOP;

Here max_vers is a constant 100 and p1...p100 and h1...h100 are columns in a table. In above query null is being set if columns' value is 0.

SO is it possible to use ForAll with a constant rather than collection?

I have tried to do below: First, I tried to use direct constant with 'Count' method but since it is a method of collect

PROCEDURE Test
IS 

TYPE MaxVersTab IS TABLE OF number;
maxvers MaxVersTab := 100; 
                             -- Error1
BEGIN

    FORALL i IN 1 .. maxvers .count
    EXECUTE IMMEDIATE -- Error2 and Error3
    'update test_table set p' || i ||'= NULL WHERE p' ||i||'=0 AND condition1=' ||n_input1 || ' AND condition3 =n_input3' USING maxvers(i);

    FORALL i IN 1 ..  maxversions.count
    EXECUTE IMMEDIATE
    'update test_table set p' || i ||'= NULL WHERE p' ||i||'=0 AND condition1=' ||n_input1 || ' AND condition3=n_input3'  USING maxvers(i);

I am getting different errors as below:

• Error 1) Expression is of wrong type
• Error 2) Statement ignored
• Error 3) the declaration of the type of this expression is incomplete or malformed

My question is , can we assign a range to a collection(like 100) to be used in ForAll. Please let me know.

Regards

Answer 1

Instead of using For loop , I am using static query which would update h1..h100 and p1...p100 columns in one go.

l_sql := 'UPDATE sh_rpt_temp_peer_final t SET p1= NULLIF(p1, 0),--...NULLIF(p100,0),
h1= NULLIF(h1, 0)  --...NULLIF(h100,0),
where condition1=' ||n_input1 || ' AND condition3 =n_input3';
EXECUTE immediate l_sql;

This will reduce the number of query execution from 200 to 1

Regards