Reputation: 5232

Oracle Default Values

I've got a quick question about default values in PL/SQL functions in Oracle. Take this program as an example;

create or replace
FUNCTION testFunction
(
  varNumber IN NUMBER DEFAULT 0
)
RETURN NUMBER
AS
BEGIN
  dbms_output.put_line(varNumber);
  RETURN varNumber;
END;

The idea here being that if no value is specified for varNumber when this function is called, then it will take the value of 0.

Now, my problem is that my functions are getting called from a web services layer that will always pass in NULL as the value for parameters which it doesn't have a value for. Oracle interprets NULL as a value, and so does not initialise varNumber to its default of 0.

I can see why this approach makes sense, but I was wondering if there was a way to override this behaviour, and make it so that if a NULL value is passed, that it causes Oracle to assign the explicit DEFAULT value that is specified in the function header?

I have considered the option of doing a manual check...

IF(varNumber IS NULL) THEN
   varNumber := 0;
END IF;

However, there are hundreds of functions where this may be an issue, never mind the large number of parameters per function, and so I'd prefer it if I could find a more general solution to the problem.

Cheers for any insight you can give.

Upvotes: 1

Answers (3)

Jim Hudson

Reputation: 8079

You can't assign values to an IN parameter, but you could make them IN/OUT and then set them. That raises a big potential for misuse and confusion, though.

So I think you'd do better with a local variable. But you can do it in the declaration. That is,

create or replace
FUNCTION testFunction
(
  varNumber IN NUMBER DEFAULT 0
)
RETURN NUMBER
AS
  vFix number := nvl(varNumber,0);
BEGIN
  dbms_output.put_line(vFix);
  RETURN vFix;
END;

Upvotes: 2