Lazy can be a good characteristic of a developer, and it can be a terrible one – depending on how and when it’s applied. In our second FeuerTip (March 17, 2021), we looked at an example of each.
Watch the full episode here:
When it comes to programming, the most important thing to do is meet user requirements and expectations. Which mostly has to do with correctness (does it do what they asked for?) and performance (is it fast enough?). But we also need to think about developer productivity (can you meet your deadlines?) and code maintainability (how easy is it to fix or enhance the code in the future?)
One way to improve your productivity is to be lazy: you don’t do something you should do, or you let someone else do it for you, or you take a short-cut to get something done.
I don’t think that being lazy is necessarily a bad trait in a developer, but you need to be very careful about when and how you are lazy. In other words, there is Bad Lazy, and there is Good Lazy.
Bad Lazy
Let’s start with Bad Lazy. Before we get to the code, please keep in mind these “early warning signals” that the lazy step you are about to take is a bad one. Basically, you find yourself thinking things like:
- “I know I shouldn’t really do this, but….”
- “This will be OK because XYZ will never happen.”
- “This will be OK because XYZ will always happen.”
- “Sure, My-Expert-Friend says this is a bad idea, but I don’t their concern applies here.”
And now for an example of Bad Lazy:
A cursor for loop that fetches at most one row
Have you ever done this? I am tempted to say “I certainly have”, because I have violated over the years just about every suggestion, guideline, and “best practice” I’ve ever pushed on anybody else. But in this case, I think not. It’s an “anti-pattern” I’ve never felt attracted to.
But.
There is no question that many others have taken this approach, and I can understand why.
Suppose I need to fetch one row and display a value. In addition, if the row doesn’t exist, just display null. Utilizing a cursor for loop (and being lazy in every other possible way as well), I end up with this:
procedure show_last_name (p_id number)
is
begin
for rec in (select last_name
from employees
where employee_id = p_id)
loop
dbms_output.put_line (rec.last_name);
end loop;
end;
Let me count the lazy ways…
- Use base datatypes (no %type)
- Don’t even bother specifying the parameter mode
- No need to declare local variables
- No need to open, fetch, close the cursor
- No need to handle no_data_found or too_many_rows exceptions
And now the not-at-all-lazy implementation:
procedure show_last_name (p_id in employees.employee_id%type) authid definer
is
l_name employees.last_name%type;
begin
select last_name
into l_name
from employees
where employee_id = p_id;
dbms_output.put_line(rec.last_name);
exception
when no_data_found then null;
when too_many_rows then
log_error('Data integrity problem!');
raise;
end;
It’s more code. It requires an error logging procedure. It behaves the same way. So why is the first example with the cursor for loop so bad? Why should you take the time to write more code?
The bottom line for me is that using a cursor for loop for a single row fetch is misleading. Or to put it another way: it raises more questions than it answers.
When I look at a cursor for loop, I think to myself: “OK, so we are fetching multiple rows and for each row fetched, I execute this code.”
Then I look more closely and realize the where clause identifies at most one row, and maybe not even that.
So then I have to ask: Was this intentional? Is the where clause wrong? Do I really not care if no rows are found? Do I really not care if more than one row is found?
But of course, the author of the code is long gone (or it’s me, and I have totally forgotten). So I end up shrugging my shoulders and hoping for the best (and not touching the code unless absolutely necessary).
That’s not good.
Good Lazy
Let’s now shift our focus to a good, no an excellent, way to be lazy. This activity falls into the category of “Let PL/SQL do the heavy lifting.” As in PL/SQL, the compiler. It’s not easy to write great code. It takes knowledge, experience, an openness to learning new things, and dedication to the craft.
And it certainly isn’t enough to get your code to merely compile. That just means you can run it. It doesn’t mean it’s correct or fast or anything but a big mess of statements strung together.
How can you make your code great, or even just not terrible? You can study up on lots of recommendations, best practices, etc., and then read your program line by line and ask yourself: Is this line OK? Does this statement make sense? That will almost certainly improve your code – but it will take a lot of time.
What if you could have an expert – somebody or something who knows PL/SQL way better than any of us ever will – look over your code and make a bunch of recommendations for you to follow?
Sounds good, right? Well, that expert is available to you 24×7 and is built right into the PL/SQL compiler! It’s called, your friend and mine, Compile Time Warnings.
Compile Time Warnings were added to the PL/SQL compiler in Oracle Database 10g, and the PL/SQL team has been improving upon it ever since (primarily by adding more and more warnings).
Here’s the basic idea: once the compiler confirms that your code is runnable (i.e., it compiles), it checks the code to see if there are any ways the code could be improved. There are three categories of warnings:
SEVERE – Condition might cause unexpected action or wrong results.
PERFORMANCE – Condition might cause performance problems.
INFORMATIONAL – Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable.
You can enable all warnings, just for a single category or even for specific warnings. You can tell the compiler “I hate that warning so much, I want you to act like it is a compile error.” You can request warnings for your session as a whole or a particular program unit.
Let’s take a look at an example that demonstrates the “good lazy” aspect of Compile Time Warnings.
alter session set plsql_warnings = 'enable:all'
/
1 create or replace procedure proc
2 is
3 n number := 1 / 0;
4 b constant boolean := false;
5 begin
6 if b then
7 DBMS_OUTPUT.put_line('b true');
8 end if;
9 DBMS_OUTPUT.put_line('done' || n);
10 end proc;
/
show errors
LINE/COL ERROR
--------- -------------------------------------------------------------
1/1 PLW-05018: unit PROC omitted optional AUTHID clause; default value DEFINER used
3/4 PLW-06017: an operation will raise an exception
4/4 PLW-06002: Unreachable code
7/7 PLW-06002: Unreachable code
The first warning about AUTHID is helpful but not too critical.
The rest of the warnings demonstrate the power of “good lazy.” The compiler is telling us:
“We are certain that when line 3 is executed, an exception will be raised. Maybe you should do something about that now.”
Catching bugs and errors at compile-time is a much more productive use of a developer’s time than leaving it to run-time.
“It is impossible for lines 3 and 4 to ever be executed. Surely that wasn’t what you had in mind?”
Imagine trying to “manually” verify or identify unreachable code. Sure, sometimes it is pretty obvious, but most of the time…well, we have actually no idea how much of our code is unreachable – without warnings.
There are many nuances to working with and making the most of Compile Time Warnings. We can’t cover them all here. So I provide some tips and links below.
Turn on Warnings in SQL Developer
You can have SQL Developer automatically turn on warnings whenever you connect to a schema. That way you don’t have to remember each time. And each time you write a program unit, you can aim for zero warnings. A 100% clean compile. I strongly recommend this. You can do it two different ways:
1. Use the Preferences / Compile window to set general preferences for warnings.
2. Run a more granular alter session statement in a login script that is executed on connection.
This post by That Jeff Smith gives you all the details:
https://www.thatjeffsmith.com/archive/2012/05/plsql-warning-messages-in-oracle-sql-developer/
About Those Warnings
To familiarize yourself with the warnings that PL/SQL offers (and generally give you some ideas about how to improve your code even if you never turn on warnings), check out the Error Messages manual.
https://docs.oracle.com/en/database/oracle/oracle-database/21/errmg/index.html
This manual contains all database errors, including the “classic” “ORA-0NNNN” errors. Look for the “PLS” errors for compile errors. The “PLW” prefix contains the list of all the warnings.
I also suggest you check out the PL/SQL User Guide for lots more details on compile-time warnings:
I and others have created a number of Oracle LiveSQL scripts that explore warnings:
https://livesql.oracle.com/apex/f?p=590:49:::NO:RP,49:P49_SEARCH:warning
And for those who prefer to learn from videos:
https://www.youtube.com/watch?v=OqqoQ-0Bums
https://www.youtube.com/watch?v=81tYBuKcs-o
https://www.youtube.com/watch?v=TEnfeK0PyxQ
Bad Lazy, Good Lazy
I hope you now have a better sense of when it’s helpful to your career, your code, and your users to be lazy – and when everyone is better off with you not taking shortcuts that might help you in the short term, but cause problems in the long run.