Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Allow for explicitly ignoring procedure IN / OUT parameters

Lukas EderJun 21 2018 — edited Jun 21 2018

Consider the following procedure:

CREATE OR REPLACE PROCEDURE proc (a NUMBER, b NUMBER := 5, c NUMBER, d OUT NUMBER) IS

BEGIN

  dbms_output.put_line(a || ':' || b || ':' || c);

  d := a + b + c;

END proc;

/

It has two features that could use some additional syntax sugar:

  • Defaulted IN parameters
  • OUT parameters (or return values)

Especially the OUT parameters can be annoying at times, when they have informational purposes only, e.g. a return code that can be safely ignored, or some debug information, etc. This is specifically true with badly designed APIs that have dozens of useless OUT parameters, each of which has to be assigned. Currently, if I want to ignore the OUT parameter, I still have to assign it to a local variable:

DECLARE

   ignore NUMBER;

BEGIN

   proc(a => 1, c => 3, d => ignore);

END;

/

Also, in order to apply the default value for the B parameter, I have to use named parameters, which can be a hassle at times, if I don't really want to look up their names.

A new DEFAULT or IGNORE keyword

My suggestion here would be to introduce a new usage of the DEFAULT keyword, similar to how it is already used in INSERT or UPDATE statements:

INSERT INTO t (a) VALUES (DEFAULT);

UPDATE t SET a = DEFAULT;

In my procedure call case, I would be able to write:

BEGIN

   proc(1, DEFAULT, 3, DEFAULT);

END;

/

The semantics would be:

  • In the case of the IN parameter B, the DEFAULT value would be applied. This is the same as using named parameters and not passing B at all.
  • In the case of the OUT parameter D, the DEFAULT keyword simply means that the OUT parameter is ignored.

Another, better keyword could be used for the OUT parameter case, e.g. IGNORE or VOID, in case of which it might also make sense to use it for function calls. Here are some alternative syntaxes:

BEGIN

  -- Pseudo local variable

   IGNORE := func(1, 2, 3);

  -- Keyword

  IGNORE func(1, 2, 3);

  -- Pseudo function

  IGNORE(func(1, 2, 3));

  -- Just ignore it entirely as in other languages

  func(1, 2, 3)

END;

/

There might be better syntax than the above, and the specific keyword to be used could be discussed forever, but the idea would be useful in general, especially when working with functions that have side effects, and whose return value is irrelevant (I know functions shouldn't have side effects, but real world legacy is far from ideal).

IN OUT parameters cannot have a DEFAULT value, but we could imagine a syntax like this:

CREATE OR REPLACE PROCEDURE proc (a NUMBER, b IN OUT NUMBER, c NUMBER) IS ...;

BEGIN

  proc(1, 2 IGNORE OUT, 3);

END;

/

Or even introduce additional syntax that allows for splitting the IN from the OUT value (this could be an idea on its own):

DECLARE

   v_out NUMBER;
BEGIN

  proc(1, IN 2 OUT v_out, 3);

END;

/

In case of which we could ignore the OUT value again:

DECLARE

  v_out NUMBER;
BEGIN

  -- IGNORE keyword suggestion

  proc(1, IN 2 OUT IGNORE, 3);

  -- DEFAULT keyword suggestion

  proc(1, IN 2 OUT DEFAULT, 3);

END;

/


Workarounds

I'm aware of the possible workaround of wrapping an ill-designed procedure / function in a custom procedure / function. That's a workaround. A new language feature might offer a more convenient approach at tackling this problem on a case-by-case basis.

Comments
Post Details
Added on Jun 21 2018
2 comments
2,119 views