What restricition imposed on cursor variable name in plsql?

Question

I have executed following simple anonymous block in sql developer by expecting x number of rows to be deleted from "FOO" table however I ended up with unexpected outcome which in turn deleting entire rows.

DECLARE
  type pkarray IS VARRAY(3) OF RAW(16);
  ids pkarray;      
BEGIN  
  ids := pkarray('guid_value1','guid_value2','guid_value3');          
  FOR i in 1 .. 3 LOOP  
    FOR foo IN (SELECT FOO_ID FROM FOO WHERE BAR_ID = UPPER(ids(i))) LOOP                  
           DELETE FROM FOO WHERE FOO_ID = foo.FOO_ID;                  
    END LOOP;        
  END LOOP;      
END;

However when I changed the cursor variable 'foo" to something else like "abc", the program worked correctly by deleting x number of rows. The number x I knew in ahead of time.

Answer 1

Since PL/SQL is case insensitive for identifiers, foo and FOO are equvalent. Let's reproduce part of your code, setting off variable names in lower case for clarity:

 FOR foo /*1*/ IN (SELECT foo_id FROM foo WHERE bar_id = UPPER(ids(i))) LOOP                  
     DELETE FROM foo /*2*/ WHERE foo_id = foo.foo_id;                  
 END LOOP;

We are dealing here with name shadowing. When compiler evaluates this expression

DELETE FROM foo WHERE foo_id = foo.foo_id;

it sees that needed identifiers foo and foo_id are known (in context of table definition). So there's no need to seek higher in syntax tree for definition of the utilized names. In other words the first foo (loop variable) is shadowed by table name and not used in compilation of the delete query, which is the same as

DELETE FROM foo WHERE foo_id = foo_id;

and its filter condition is true for all foo_id except NULL, what leads to deleting entire rows.

---

Luckily this problem can be solved once and for all with naming conventions: in PL/SQL blocks use names with special prefixes etc., protecting your code from accidental intersection with schema objects names.