Oracle Default Values

Question

Hi everyone,

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.

Answer 1

Use NVL to define the value.

NVL( value_in, replace_with )

Answer 2

Your manual check is the only way to safely do what you want.

You can write that in one line like this though:

varNumber = NVL(varNumber,0);

Good luck!

Answer 3

Hm, looks like that might be the way I have to go. One question though, is there a way to assign values to IN parameters in Oracle? It would make this change significantly easier if I didn't have to create a new local var and then ensure that this is used in all the correct places throughout the function.

If I could just assign varNumber := NVL(varNumber,0) at the top of my function, that would be a good bit easier.

Answer 4

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;

Answer 5

Thanks, I think I'll have to go that route then. Cheers everyone for the help.