Saturday, February 10, 2007

Separating PL/SQL Declarations and Assignments

For variables with default or derived values, I'm normally a big fan of declaring and assigning it in one go. In PL/SQL, that looks like:

create or replace procedure foo as
v1 varchar2(16) := 'hello';
n1 integer := 7;
cond1 varchar2(32) := case
when n1 > 3 then 'greater than 3'
when n1 < 3 then 'less than 3'
else 'exactly 3'

(That last technique with CASE is quite a powerful one by the way, you can encode a whole function's worth of logic into a single statement.)

However, I've found through the school of hard knocks that in many cases, it's better to declare the variable but not assign it until the code starts after BEGIN.

If the assignment inside a declaration causes an exception, your EXCEPTION block inside the same procedure or function won't catch it. That is, you'll get different behaviour from:

v1 varchar2(8) := function_that_returns_a_long_string();


v1 varchar2(8);
v1 := function_that_returns_a_long_string();

The second example can trap the exception itself. In the first example, the exception could only be trapped by whatever called the procedure.

Another case is when you make an assignment that ends up inside a PL/SQL package. One of the good points of packages is that you can recompile the body, and none of the procedures or functions that calls the package is invalidated. However, if you put a declaration like:

v1 varchar2(32) := 'some string literal';

in the package spec, you can't change the value without recompiling the spec. So if you're writing code in a procedure that might one day evolve into a package, you might as well split up the declaration and the assignment now.


Michael said...


Thanks for the easy to read BLOG articles. I picked up a couple of things I didn't know or never thought about. Keep up the postings.


Fahd Mirza said...

yes useful blog indeed.

DAVE said...

Hi John,
It as been the most interesting oracle blog i have ever read. Your knowledge of
earlier programming languages
as enable you to question 'the
leap of faith' higher level languages require you to have
your practical information you
present in your blog is extremely
useful. You should write a book!!