FOR LOOP with WHERE clause

Question

This question has been posed before but not specifically for Oracle database.

Can a FOR LOOP be filtered with WHERE clause? For example I would like to do something like:

--LOG ERRORS
FOR err in c_errors WHERE trx_type='CYCLE_COUNT'
LOOP

    ...do some stuff

END LOOP; 

This code gives error:

PLS-00103: Encountered the symbol "WHERE" when expecting one of the following ...

Is there proper syntax for this?

Here is the cursor definition. It grabs cycle count and adjustment transaction types. But in the log errors section mentioned above, I only want to report on cycle count errors. Sure I could use separate cursors, but was trying to accomplish using one.

    CURSOR c_errors IS
        SELECT DISTINCT CC_ENTRY_INTERFACE_ID INTERFACE_ID
            ,ERROR_MESSAGE
            ,creation_date
            ,LAST_UPDATE_DATE
            ,'CYCLE_COUNT' TRX_TYPE
        FROM mtl_cc_interface_errors
        UNION
        SELECT DISTINCT TRANSACTION_INTERFACE_ID
            ,ERROR_EXPLANATION
            ,CREATION_DATE
            ,LAST_UPDATE_DATE
            ,'ADJUSTMENT'
        FROM mtl_transactions_interface 
        WHERE process_flag=3 
            AND error_code IS NOT NULL
        ORDER BY last_update_date DESC;

Answer 1

Nick's answer it's a nice explanation.

Here is another example of how to use it, by declaring the cursor inline with the FOR syntax:

BEGIN
  FOR r_product IN (
        SELECT 
            product_name, list_price 
        FROM 
            products
        WHERE list_price > 120
        ORDER BY list_price DESC
    )
  LOOP
     dbms_output.put_line( r_product.product_name ||
        ': $' || 
        r_product.list_price );
  END LOOP;
END;

Source: https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/

Answer 2

FOR err in c_errors WHERE trx_type='CYCLE_COUNT'

This is semantically incorrect.

What you can do, as one of the options, is to create a parameterized cursor. Here is an example:

Case #1: Parameter is null. All rows returned

set serveroutput on;
declare
  cursor l_cursor ( param1 varchar2) is
    select *
      from (
            select level  as c1
                 , 'cycle_count' as trx_type
              from dual
            connect by level < 3
            union all
            select level  as c1
                 , 'adjustemnt' as trc_type
              from dual
            connect by level < 3
           ) q
    where param1 is null
       or trx_type = param1;
begin
  -- param1 = null. No filter applied
  for r in l_cursor(null) loop
    dbms_output.put_line('C1: ' || to_char(r.c1) || '; ' ||
                         'TRX_TYPE: ' || r.trx_type);
  end loop;
end;

Result:

CNT: 1; TRX_TYPE: cycle_count
CNT: 2; TRX_TYPE: cycle_count
CNT: 1; TRX_TYPE: adjustemnt
CNT: 2; TRX_TYPE: adjustemnt

Case #1: Filtering by TRX_TYPE

set serveroutput on;
declare
  cursor l_cursor ( param1 varchar2) is
    select *
      from (
            select level  as c1
                 , 'cycle_count' as trx_type
              from dual
            connect by level < 3
            union all
            select level  as c1
                 , 'adjustemnt' as trc_type
              from dual
            connect by level < 3
           ) q
    where param1 is null
       or trx_type = param1;
begin
  -- param1 = 'cycle_count'
  for r in l_cursor('cycle_count') loop
    dbms_output.put_line('C1: ' || to_char(r.c1) || '; ' ||
                         'TRX_TYPE: ' || r.trx_type);
  end loop;
end;

Result:

C1: 1; TRX_TYPE: cycle_count
C1: 2; TRX_TYPE: cycle_count