Dummy output parameters

Yesterday I encountered a code snippet which taught me something I did not think was possible. But it is, as I'll show in this blogpost. It's not spectacular in any way, just convenient at most.

When you need a function or procedure to retrieve some value, you'll start examining the existing code base if a function already exists that does the job. Probably you won't find an exact match, but there is this procedure with retrieves a lot of values including the value you're interested in. Sounds familiar? You now have two choices depending on whether you want to minimize code or execution time.

If you want to minimize execution time, you don't want to reuse this procedure as it does more work than you need, and you'll end up writing your own specific function.

If you want to minimize code, you'll end up reusing the procedure and ignore all output parameters you're not interested in. It's this last variant I will be talking about in the rest of this post. Here is an example of the api procedure I want to reuse:

SQL> create or replace procedure api
  2  ( p1 out number
  3  , p2 out number
  4  , p3 out number
  5  )
  6  as
  7  begin
  8    p1 := 1;
  9    p2 := 2;
 10    p3 := 3;
 11  end api;
 12  /

Procedure created.

And this is how I would reuse this code:

SQL> declare
  2    l_number_i_am_interested_in number;
  3    l_p2_dummy                  number;
  4    l_p3_dummy                  number;
  5  begin
  6    api
  7    ( p1 => l_number_i_am_interested_in
  8    , p2 => l_p2_dummy
  9    , p3 => l_p3_dummy
 10    );
 11  end;
 12  /

PL/SQL procedure successfully completed.

For each output parameter I would declare a variable with the suffix _dummy to make my intent clear that I'm not going to really use these variables. The code snippet I saw, was of this form:

SQL> declare
  2    l_number_i_am_interested_in number;
  3    l_dummy                     number;
  4  begin
  5    api
  6    ( p1 => l_number_i_am_interested_in
  7    , p2 => l_dummy
  8    , p3 => l_dummy
  9    );
 10  end;
 11  /

PL/SQL procedure successfully completed.

Just one variable for the two dummy output parameters. I thought this could not work, but clearly it does. What value is assigned to l_dummy? The actual parameters l_dummy and l_dummy get a value assigned equal to the final values of the formal parameters p2 and p3. So l_dummy will get value 2 and l_dummy will get value 3. The order of assigning these values will now determine the real value of l_dummy. Fortunately, in this situation, it doesn't matter which value is assigned to the l_dummy variable, since we're not interested in it. But of course I'm a bit curious, so:

SQL> declare
  2    l_number_i_am_interested_in number;
  3    l_dummy                     number;
  4  begin
  5    api
  6    ( p1 => l_number_i_am_interested_in
  7    , p2 => l_dummy
  8    , p3 => l_dummy
  9    );
 10    dbms_output.put_line(l_dummy);
 11  end;
 12  /
3

PL/SQL procedure successfully completed.

It appears to be the last value in this case. So after the invocation of api ends, p1 gets the value 1 assigned, then l_dummy will get the value 2 assigned, and then -overwriting the previous step- l_dummy will get the value 3 assigned. This is confirmed when extending the example with a lot more dummy output parameters:

SQL> create or replace procedure api
  2  ( p1 out number
  3  , p2 out number
  4  , p3 out number
  5  , p4 out number
  6  , p5 out number
  7  , p6 out number
  8  , p7 out number
  9  , p8 out number
 10  , p9 out number
 11  )
 12  as
 13  begin
 14    p1 := 1;
 15    p2 := 2;
 16    p3 := 3;
 17    p4 := 4;
 18    p5 := 5;
 19    p6 := 6;
 20    p7 := 7;
 21    p8 := 8;
 22    p9 := 9;
 23  end api;
 24  /

Procedure created.

SQL> declare
  2    l_number_i_am_interested_in number;
  3    l_dummy                     number;
  4  begin
  5    api
  6    ( p1 => l_number_i_am_interested_in
  7    , p2 => l_dummy
  8    , p3 => l_dummy
  9    , p4 => l_dummy
 10    , p5 => l_dummy
 11    , p6 => l_dummy
 12    , p7 => l_dummy
 13    , p8 => l_dummy
 14    , p9 => l_dummy
 15    );
 16    dbms_output.put_line(l_dummy);
 17  end;
 18  /
9

PL/SQL procedure successfully completed.

Less variables declared makes the code cleaner in my opinion, so I like this newly learned construct.

---

UPDATE

And in case you mix the order of the actual parameters, it's still the last formal parameter that gets the value:

SQL> declare
  2    l_number_i_am_interested_in number;
  3    l_dummy                     number;
  4  begin
  5    api
  6    ( p1 => l_number_i_am_interested_in
  7    , p2 => l_dummy
  8    , p3 => l_dummy
  9    , p7 => l_dummy
 10    , p8 => l_dummy
 11    , p9 => l_dummy
 12    , p4 => l_dummy
 13    , p5 => l_dummy
 14    , p6 => l_dummy
 15    );
 16    dbms_output.put_line(l_dummy);
 17  end;
 18  /
9

PL/SQL procedure successfully completed.