PL/SQL Naming

Struggling as I am to find time to write items for the blog, I was pleased to get an email yesterday from someone who had discovered an interesting little feature of pl/sql programming and wanted to pass it on to me.

I thought that other people (PL/SQL developers in particular) might find it useful, so I asked if I could reproduce the email verbatim on my blog.  The answer was yes – so here’s my first “guest blog” item, courtesy of Jason Bucata.

PL/SQL Naming Discovery

I’ve been going through some of the PL/SQL code from Oracle Applications to see how a few things work. While digging around, I discovered a very useful feature of PL/SQL that, as far as I know, isn’t documented anywhere.

Suppose you’ve got some variables, and a query against a table. The table happens to have columns with the same names as some of your variables. You want to reference the variables in the query.

It’s easy enough to specify that you want the columns: Use a table alias. (Or leave it off, since that’s the default behavior anyway, though I think it’s clearer to be explicit.) But if you want to use the variables, I’ve always either renamed the variables or bundled them into a record (or occasionally an object type).

But it turns out that you can qualify the variable name by using the name of the procedure or function!

The following code works on 9.2.0.8, at least:

set serveroutput on

declare
	procedure p is
		dummy char(1) := 'X';
		cnt pls_integer;
	begin
		select count(*)
		into cnt
		from dual
		where dual.dummy = p.dummy;

		if cnt = 1 then
			dbms_output.put_line('It worked');
		else
			dbms_output.put_line('It failed');
		end if;
	end p;
begin
	p;
end;
/

Unfortunately the examples in the PL/SQL User’s Guide and Reference, Appendix D, only show cases where the variable is a public package variable, and so is visible to the outside. I always expected that to work — we all know to reference public package variables with <package_name>.<variable_name> — but I didn’t expect that to work with local variables in a procedure. Until today, I’ve never seen code referencing <procedure_name>.<variable_name> in any context.

It also turns out (this from looking at Appendix D) that block labels can be used, too. See the following example. Even though this is documented, it’s very underpublicized, IMHO.

set serveroutput on

begin
	<<p>>
	declare
		dummy char(1) := 'X';
		cnt pls_integer;
	begin
		select count(*)
		into cnt
		from dual
		where dual.dummy = p.dummy;

		if cnt = 1 then
			dbms_output.put_line('It worked');
		else
			dbms_output.put_line('It failed');
		end if;
	end;
end;

It’s also useful to get at variables in an outer scope that are masked by variables of the same name in an inner scope, but in practice I don’t think I’ve ever seen anybody a) reuse variable names in nested scopes and b) want to use both variables in the inner scope. That just seems to be a good programming practice, to avoid choosing names that way. I think that’s why this is so obscure: Good code doesn’t need that feature, at least in pure PL/SQL.

You might want to spread the word. This can be very useful to those of us who frequently embed SQL in PL/SQL code.

Jason B.