Way back in the early 1980s, some very sharp people at Oracle HQ decided that they needed to provide their own programming language to build applications using SQL and Oracle Database. Up to that point, developers had been writing C programs. Which meant that among other things….you had to know how to write C to work with Oracle, and that code would not necessarily be portable across different operating systems.
Oracle wanted a language that could be written once and run anywhere….anywhere there was an Oracle Database anyway. Once that decision was made, these folks then showed their true brilliance by not saying:
“We write really great database software. Which means we can write really great everything–else software. So we will design our own very own, brand-new database programming language!”
That’s right; they did not say this. Instead, they said something like: “There’s been a lot of great work done in high-level programming languages. Let’s take a look at what’s out there, and pick one as the model for (what would become) PL/SQL.”
They chose Ada, and the rest is history: because of that solid foundation, Oracle PL/SQL was and continues to be the best database programming language around.
And one of the nice things PL/SQL “inherited” from Ada is the block, and with it, the exception section.
If you are not intimately familiar with exception handling in PL/SQL, I offer some links at the bottom of this post. Assuming for the duration that you know what I mean when I talk about exception propagation, unhandled exceptions, and so on, let’s continue!
A PL/SQL block has up to three sections:
- Declaration
- Execution
- Exception
Here’s a “skeleton” block with all three sections:
declare l_name varchar2(5); begin l_name := 'Steven'; exception when others then log_error (info_in => l_name); end;
In the block above, the exception handler traps and handles any exception that might be raised by logging the error. And that, dear friends, makes perfect sense.
Here’s another simple block of code with an exception handler:
begin
insert into favorite_games (name, game)
values ('Steven', 'Farkle');
exception
when dup_val_on_index
then
update favorite_games set game 'Farkle'
where name = 'Steven';
end;
But I feel really differently about this block. I feel like:
“No! Don’t do that!”
Don’t do what? Don’t:
- treat the raising of an exception as a conditional flow (“if I am unable to insert then do an update”)
- put application logic in the exception section.
Generally, the rule you might consider following is: only put exception handling code in an exception handler. In the small examples in this article, that may not seem like a big deal. But when you are writing “real world” code, the exception section might be hundreds of lines below the place where the exception is raised. It is very unintuitive to know that you should scroll way down to see the next “step” in the program flow.
What should you do instead? Well, certainly, that depends on what you actually need to do, and how well you can predict the possible exceptions that could be raised.
Certainly, for the above example, we could use a merge statement, as in:
begin
merge into favorite_games fg
using favorite_games h
on ( fg.name = 'Steven' )
when matched then
update
set fg.game = 'Farkle'
when not matched then
insert (name, game )
values ('Steven','Farkle');
end;
Of course, you can’t always simply switch to a different SQL statement to avoid putting application logic in your exception section. So let’s take a look at another example. Suppose I am using utl_file to read the contents of a file, and then process each line in that file. I use a simple loop to get a line and deposit it directly into an array. That loop looks kind of dangerous, right? It doesn’t have an exit statement! That’s ok, because it turns out that when you read past the end of a file, utl_file raises no_data_found! So, no problem. I handle the exception and then loop through the array, processing each line, and then I close the file.
procedure process_file ( dir_in in varchar2, file_in in varchar2 ) is type line_t is table of varchar2(32767) index by pls_integer; l_file UTL_FILE.file_type; l_lines line_t; begin l_file := UTL_FILE.fopen( location => dir_in, filename => file_in, open_mode => 'R', max_linesize => 32767 ); loop UTL_FILE.get_line(l_file, l_lines(l_lines.COUNT + 1)); end loop; exception when no_data_found then for indx in 1..l_lines.COUNT loop do_stuff_with_line(l_lines(indx)); end loop; UTL_FILE.fclose(l_file); end process_file;
How do I get around utl_file raising this exception? I don’t. That’s the way the package works. But I can change the way I call utl_file.get_line. First, though, let’s rewrite the above procedure so that it looks like the way we want it to work.
procedure process_file ( dir_in in varchar2, file_in in varchar2 ) is type line_t is table of varchar2(32767) index by pls_integer; l_file UTL_FILE.file_type; l_lines line_t; eof Boolean := false; begin l_file := UTL_FILE.fopen( location => dir_in, filename => file_in, open_mode => 'R', max_linesize => 32767 ); while (not eof) loop get_line_from_file (l_lines(l_lines.COUNT + 1), eof); end loop; for indx in 1..l_lines.COUNT loop do_stuff_with_line(l_lines(indx)); end loop; UTL_FILE.fclose(l_file); end process_file;
No more reliance on the exception section for application logic. No more alarming “infinite loop.” Let’s add get_line_from_file to the procedure.
procedure process_file ( dir_in in varchar2, file_in in varchar2 ) is type line_t is table of varchar2(32767) index by pls_integer; l_file UTL_FILE.file_type; l_lines line_t; eof boolean := false; procedure get_line_from_file ( line_out out varchar2, eof_out out boolean) is begin UTL_FILE.get_line(l_file, line_out); eof_out := FALSE; exception when NO_DATA_FOUND then line_out := null; eof_out := TRUE; end; begin l_file := UTL_FILE.fopen( location => dir_in, filename => file_in, open_mode => 'R', max_linesize => 32767 ); while ( not eof ) loop get_line_from_file(l_lines(l_lines.COUNT + 1), eof); end loop; for indx in 1..l_lines.COUNT loop do_stuff_with_line(l_lines(indx)); end loop; UTL_FILE.fclose(l_file); end process_file;
I’ve moved the exception handling to the nested subprogram, which allows me to implement this reality about utl_file.get_line: Reading past the end of the file is not an error. It’s what happens when you read the contents of a file. Always. So, convert the exception into a Boolean flag: did I reach end of file or not?
The specific steps you take to avoid application logic in your exception sections will vary according to the code you are dealing with (and what causes the exception). There is, however, always a way to restructure your code with an eye towards making it more readable and easier to maintain.
Time to Explore!
I wrote an article for Oracle Magazine some years ago that touches on this topic. Here’s the link:
https://blogs.oracle.com/oraclemagazine/on-exceptions-and-rules
I will apologize in advance for the very poor formatting of code – not that it’s my fault.
