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…
Wow! Good catch, Tim!
Very useful.
But can you alias the procedure or function name? If I’ve got a 30 character proc name, I don’t want to repeat it throughout the rest of the procedure; I don’t do that with table names in SQL statements as it makes things harder to read!
Boneist: No. And this is exactly what I asked when Bryn mentioned it. I’m guessing most people will not use it for this very reason.
Normal blocks can be labelled using <> and that can become a reference for any variables defined in their declaration section, but you can’t alias a procedure or cursor.
Cheers
Tim…
That is indeed my reason for not using it! The example in your post is a little disingenuous, because who uses a single letter when defining a function or procedure in real life? (If there are such people who do that with production code, well, my guess is that they won’t live long! *{;-) )
Boneist: Note sure what you are reading. My example doesn’t use a single letter. The procedure is called “my_proc”, which is what I use when I alias the variables. 🙂 Are you referring to the contents of Bryn’s papers?
If you are talking about aliasing blocks using labels (not procedure names), then that can be anything you like really. I think of it the same way I think of aliasing tables. I often use a single letter if no small acronym springs to mind.
Cheers
Tim…
@oraclebase @Boneist
Dawn (@Boneist) wrote “can you alias the procedure or function name?”. Tim (@oraclebase) wrote “this is exactly what I asked when Bryn mentioned it.” And Jonathan Lewis (@JLOracle) wrote “There goes your last chance of getting people to use meaningful function and procedure names”. Time added “I’m guessing most people will not use it for this very reason”.
Well… when seat belts were first introduced in private cars, most people refused to use them because the were too much effort. It took a fair time, and some laws, before we saw widespread use.
Do you want to subvert the benefit of fine-grained dependency tracking? And do you want to risk silent name capture and bugs? B.t.w., Tim didn’t choose the best title. He used “Preventing PL/SQL name clashes”. It should have been “Preventing silent name capture”.
Anyway… back to the question about aliasing. Take this as the starting point:
procedure Some_Or_Other_Long_Spelling(Dummy varchar2) is
Amount Integer;
begin
select Count(*)
into Amount
from Yet_Another_Long_Spelling a
where a.Dummy = Some_Or_Other_Long_Spelling.Dummy;
…
end Some_Or_Other_Long_Spelling;
Notice that Amount doesn’t need qualification because this bit of static SQL syntax is stripped off before the the SQL compiler starts to parse it.
The alias would have to have a defined scope. So that implies a syntax along the lines of this sketch:
alias (Some_Or_Other_Long_Spelling as p, …)
begin
select Count(*)
into Amount
from Yet_Another_Long_Spelling a
where a.Dummy = p.Dummy;
…
end alias;
There is no such syntax. Yet. But I shall file an enhancement request and tell you. Meanwhile, you just have to type a bit more up front. But you can spell the static SQL statement as you want.
procedure Some_Or_Other_Long_Spelling(Dummy varchar2) authid Definer is
Amount Integer;
begin
<>declare
Dummy constant varchar2(32767) not null :=
Some_Or_Other_Long_Spelling.Dummy;
begin
select Count(*)
into Amount
from Yet_Another_Long_Spelling a
where a.Dummy = p.Dummy;
end p;
DBMS_Output.Put_Line(‘Amount=’||Amount);
end Some_Or_Other_Long_Spelling;
Notice that with this verbose spelling, I do get the benefit that I can assert that the input parameter is not null — or check anything else that I wanted to.
I couldn’t see any way to format the code. And the blog ate up all leading spaces. Am I missing something? Anyway, wait for my own blog post on this topic. I’ll be able to format as I wish.
I just realized that the chevron characters are treated specially when you use them in comment to this blog. Using square brackets here instead of chevrons, typing in this:
[[Block_Name]]declare
shows up, after submit, like this:
[]declare
That’s a pain when I want to show labeled PL/SQL statements.
Bryn: I suggest you write your blog post. When you’ve done that you can link it here and if you want I can remove your other comments, if you think they will confuse. 🙂
Cheers
Tim…
Bryn, I’m not a fan of adding extra code just for the hell of it, especially when it serves no (apparent) extra functionality (eg extra declares/begins) – mostly for maintenance reasons (cluttered code is less easy to read, etc) so whilst I understand the logic behind labelling the proc variables correspondingly, I more than probably won’t start using this right now (after all, I quite often don’t bother aliasing tables in a SQL statement if there’s only one table involved!).
However, an easy way to alias the proc name would go a long way to help me make the mental adjustments needed to start aliasing the proc variables! (It took me a long while to make the switch over to ANSI-style joins, so…!)
Boneist: I already explained it. If you qualify every identifier in a static SQL statement, then you’re guaranteed to be immune to name capture. But if you rely on a human convention of naming, then you’re only as good as the dev. shop’s diligence in adhering to these conventions. A simple test to observe the effect of adding a column to a statically referenced table upon the referring unit’s validity, like I described, proves this. But I do agree with you. You are absolutely free to choose between scientific analysis and emotional bias — in other words, between a little extra effort to guarantee correctness, or faith that it’ll probably work out OK.
I totally get what you’re saying, and I agree that being able to qualify the scope of the variables is a good thing. I do, I promise!
Make it easier to alias the variables and continue to write simple and elegant PL/SQL, and I’ll be much more likely to incorporate it into my code!
In fact, I was thinking about it last night, and I’d probably stick to the same aliases for the top level scope – pkg for package level, prc for procedures and fn for function. Perhaps they could be made as the standard default aliases, with the ability to override as necessary?
Also, regarding human intervention – whilst I agree aliasing the scope would reduce name capture, if you think people don’t/won’t abuse aliases, you’re being overly optimistic! *{;-)