plsql fun

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

2 thoughts on “plsql fun

  1. So yeah, in plsql, there are actually three valid parameter qualifiers: In, Out, and InOut. Had I known this earlier, I might have saved a lot of time trying to find the issue.

    Apparently, defining something as Out means plsql will immediately null out the value upon entering that method.

    The solution is simply to change
    procedure helper_method(
    iChange IN boolean,
    oValue OUT number
    )
    into
    procedure helper_method(
    iChange IN boolean,
    oValue IN OUT number
    )

Leave a Reply

Your email address will not be published. Required fields are marked *