Cursor in Oracle - after passing parameters, select does not filter result rows-CodePudding

I am facing for me strange issue with following parametrical cursor.

I have defined cursor in this way:

CURSOR cur_action ( product_code VARCHAR2(100) , action_master_list VARCHAR2(100)) 
IS 
        SELECT
        act.ACTION_DETAIL_KEY,
        act.ACTION_MASTER_KEY,
        act.PRODUCT_CODE,
        act.REF_ACTION_DETAIL_KEY
        FROM   XMLTABLE(action_master_list) x
        JOIN   ETDW.MFE_AR_ACTION_DETAILS act ON TO_NUMBER(x.COLUMN_VALUE) = act.ACTION_MASTER_KEY
        WHERE  1=1
        AND    act.LAST_FLAG = 'Y'
        AND    act.PRODUCT_CODE = product_code;

Then I am using it in following way:

OPEN cur_action ( iFromProductCode ,  iActionMasterKeyList);
  LOOP      
  FETCH cur_action BULK COLLECT INTO vActionDetailKey, vActionMasterKey, vProductCode, vRefActionDetailKey LIMIT 100;
            
  FOR j IN 1..cur_action%ROWCOUNT
  LOOP
    dbms_output.put_line('vActionDetailKey: ' || vActionDetailKey (j) ||'  vActionMasterKey: '|| vActionMasterKey (j) || ' vProductCode: ' || vProductCode (j));
  END LOOP;

END LOOP;

Result seems to be unfilterd. It doesnt return 3 rows as expected result (this result is returned in with cusor query, when i run in outside procedure/pl block), but it returns all rows for actions in list. So it seems, that WHERE condition "act.PRODUCT_CODE = product_code" was not applied. Why?

Thank you

CodePudding user response：

Why? Because you named parameter the same as column, so Oracle reads it as if it was where 1 = 1, i.e. no filtering at all.

Rename parameters to e.g.

CURSOR cur_action ( par_product_code V
                    ----
                    this

and, later,

AND    act.PRODUCT_CODE = par_product_code;
                          ----
                          this