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.

It depends on how Oracle evaluates variables, table names, .... In your query, `foo.id` is treated like "the column `id` of the table `foo`" and not "the field `id` of the variable `foo`", thus the unexpected behaviour. You should be able to change this behaviour by changing the variable name or even by using an alias (different from `foo`) for the table `foo` in the query. There is a great answer about this somewhere here, just trying to find it — Aleksej, Jun 06 '17 at 21:26
Scope and precedence rules apply to all variables, not just cursor records as in your example. Think about what should happen when you `select * from foo where fid = 23` and `fid` is both a column name in `foo` and a local variable. — William Robertson, Jun 07 '17 at 07:09
Thank you for your reply. Unfortunately I still don't fully understand why it behaved that way due to my lack of understanding and experience in this subject matter. However based on the observation from executing the code I reconfirm to myself using the cursor var as a lower case table name produces negative consequences. I will keep reading upon related articles. I appreciate your time for the explanation. — DaeYoung, Jun 07 '17 at 14:00

score 0 · Accepted Answer · answered Jun 09 '17 at 06:44

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.

What restricition imposed on cursor variable name in plsql?

1 Answers1