Tag: variables

I mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns, but you could just qualify each use of a PL/SQL identifier with the identifier for the name of the block in which the item is declared in this case the procedure name. In the following example, “dummy” is both a parameter name and a table column, but Oracle knows exactly what you mean because you’ve fully qualified it.

create or replace procedure my_proc(dummy varchar2)
as
  amount pls_integer;
begin
  select count(*)
  into   my_proc.amount
  from   dual a
  where  a.dummy = my_proc.dummy;
  dbms_output.put_line('amount=' || my_proc.amount);
end my_proc;
/

set serveroutput on
exec my_proc('Y');
amount=0

PL/SQL procedure successfully completed.

SQL>

You can also do this for parameterised cursors and named/labelled blocks. You learn something new every day! 🙂

This was just one of the points Bryn discussed in his whitepaper called Doing SQL from PL/SQL: Best and Worst Practices.

Bryn: Look for this section: “Name capture, fine grained dependency tracking, and defensive programming”. This discussion leads to this:

Principle 1: In embedded SQL, dot-qualify each column name with the from list item alias. Dot-qualify each PL/SQL identifier with the name of the name of the block that declares it.

It is also mentioned in his paper Why use PL/SQL?

Cheers

Tim…