Search

Top 60 Oracle Blogs

Recent comments

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI 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. Let’s cause an obvious name clash…

The table DUAL has a column called DUMMY with a single row with the value ‘X’.

SQL> select * from dual;

D
-
X

1 row selected.

SQL>

Let’s create a procedure with a parameter called “dummy”, to cause a name clash. I want to use the parameter in the WHERE clause. I hope you can see where this is going. :)

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

Now if I run it passing a parameter value of ‘Y’ I get an “incorrect result”.

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

PL/SQL procedure successfully completed.

SQL>

Actually I’m getting a correct result, because the table column is being matched against itself and the parameter is not being used. To solve this, we might rename the parameter to “p_dummy”.

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

This gives us the expected result.

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

PL/SQL procedure successfully completed.

SQL>

Nothing new so far. Now comes the bit I didn’t know. :)

Alternatively, I could have prefixed the references to the parameter with the procedure name.

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>

This was just one of the points Bryn raised in Doing SQL from PL/SQL: Best and Worst Practices, which I’m sure I read, but obviously not well enough. :)

Why does this matter? Using a prefix like “p_” is an artificial way to avoid the situation. If someone were to add a column to the table called “p_dummy”, my code would be broken again. I’m relying on them knowing my naming standard and avoiding it when defining table columns.

By the way, this works for parameterised cursors and named anonymous blocks too.

set serveroutput on
begin
  <>
  declare
    dummy  varchar2(1) := 'Y';
    amount pls_integer;
  begin
    select count(*)
    into   my_block.amount
    from   dual a
    where  a.dummy = my_block.dummy;
    dbms_output.put_line('amount=' || my_block.amount);
  end my_my_block;
end;
/
amount=0

PL/SQL procedure successfully completed.

SQL>

You learn something new every day! :)

Cheers

Tim…


Preventing PL/SQL name clashes. You learn something new every day! was first posted on November 25, 2015 at 1:37 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.