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 :)