CODEWITHSUNDEEP
oracle-databaseplsql
Pointer
Pointer

Reputation: 2186

PLSQL Encountered the symbol "IF" in forall

Is it posssible use if statments in forall, when try get error PLS-00103: Encountered the symbol "IF" when expecting one of the following: . ( * @ % & - + / at mod remainder rem select update with

 FORALL i IN 1 .. P_DAYS_IDS.COUNT
         if (P_DAYS_IDS(i) = 1) then
            Update test set col_1 = 'Y' where id = 1;
         elsif (P_DAYS_IDS(i) = 2) then
            Update test set col_2 = 'Y' where id = 2;
         end if;

Upvotes: 0

Views: 308

Answers (2)

Belayer
Belayer

Reputation: 14934

You cannot use IF within FORALL as it requires exactly 1 DML, and there is no IF in SQL. However the conditions tested migrate to a CASE expression or the WHERE clause. For this you can combine the IF predicate and the WHERE clauses into a CASE expression for each column that may be updated.

forall i in 1 .. p_days_ids.count         
    update test 
       set col_1 =  case when p_days_ids(i) = 1 and id = 1 then 'Y' else col_1 end   
         , col_2 =  case when p_days_ids(i) = 2 and id = 2 then 'Y' else col_2 end;

Notice when a column is reference in SET assigning a value is required. Thus if the when condition is not meet the column is just set to its current value.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231791

No. forall exists to eliminate context shifts between SQL and PL/SQL so it only works if you are doing a single SQL operation using each element of the collection.

You can use a regular for loop if you want to execute PL/SQL code in a loop.

FOR i IN 1 .. P_DAYS_IDS.COUNT
LOOP
     if (P_DAYS_IDS(i) = 1) then
        Update test set col_1 = 'Y' where id = 1;
     elsif (P_DAYS_IDS(i) = 2) then
        Update test set col_2 = 'Y' where id = 2;
     end if;
end loop;

Upvotes: 1

Related Questions