CODEWITHSUNDEEP
oracle-databaseplsql
waldrumpus
waldrumpus

Reputation: 2590

Ignoring a PL/SQL function's return value

Sometimes I call a PL/SQL function to receive and process the value it returns. Other times I would like to call the same function for its side effects only.

For example, consider a logging function that logs its arguments in a table, and subsequently returns a formatted string created out of the same arguments. Sometimes I'd like to use the returned value, e.g. to display it to the user, other times I just call the function for the table logging and ignore the return value. In the latter case I'd like to avoid declaring a variable to hold the returned value and write an assigment statement.

This question touches the subject, and the answer seems to be that the caller must consume the value returned by the function, be it via an assignment statement or select into.

Is there a way to call a function in PL/SQL and discard the returned value?

Upvotes: 2

Views: 1384

Answers (2)

Dominik
Dominik

Reputation: 1

Recognizing that it's not possible to ignore a function return value, there are some workarounds that do not require a dummy variable, which the original poster seemed to want to avoid. For example, I just had an example where a function returns a BOOLEAN and I want to evaluate that in some cases, but not in others. So I used:

IF function(x) OR TRUE THEN ... END IF;

For other return types, you could use

IF function(x) IS NOT NULL OR TRUE ... END IF;

Not great, but shorter than creating and assigning a dummy variable.

Upvotes: 0

APC
APC

Reputation: 146239

assigning a variable and then doing nothing with it is a code smell

If we're talking code smells, having a function which changes database state is a bit whiffy. The convention is that procedures change state and functions only read state.

So, to take your example, you could implement your logging function as two overloaded procedures in a logger package.

procedure log_this(p1_in number, p2 in timestamp, p_fmt_txt out varchar2);

procedure log_this(p1_in number, p2 in timestamp);

In the package body the second procedure just calls the first procedure and swallows the out parameter.

Now you can call the appropriate procedure, depending on whether you want to see the formatted string.

Of course, this solution won't work if you want to call your function in a SELECT statement, but changing database state through a query really is a code smell.

Upvotes: 6

Related Questions