Here’s a fun little question for those of you who aren’t experts at plsql…
Given this plsql code
procedure helper_method(
iChange IN boolean,
oValue OUT number
) IS
BEGIN
IF iChange THEN
oValue := 2;
END IF;
END helper_method;
procedure do_test(
oValue OUT number
) IS
BEGIN
oValue := 1;
--Checkpoint 0
helper_method(true, oValue);
--Checkpoint 1
helper_method(false, oValue);
--Checkpoint 2
END do_test;
and this java code (assuming the right frameworks for calling into plsql)
public void doTest() {
IntOutParam out = new IntOutParam();
sqlCall("do_test", out);
System.out.println(out.getInt());
}
what is printed out when you replace the checkpoint comments with a RETURN; statement?
The answer surprised me… I actually stumbled across this at work today and it took me a while to figure out.
Replacing checkpoint 0 with RETURN; prints out 1, as expected.
Replacing checkpoint 1 with RETURN; prints out 2, as expected.
Replacing checkpoint 2 with RETURN; prints out 0. Huh?
Can you spot why and what the fix should be?
Hint: Swapping the order of the true/false calls would result in outputs of 1, 0, and 2 respectively.
Huge Hint: At checkpoint 2, the value of oValue is actually null.
See comments for the solution.
So yeah, now you know something to watch out for when programming in plsql :)
