Clever Geek Handbook

Oracle defaults

I have a quick question about defaults 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 is that if no value is set for varNumber when this function is called, then it will take the value 0.

Now my problem is that my functions are calling a call from the webservice layer which will always pass in NULL as the parameter value for which it has no value. Oracle interprets NULL as a value and therefore does not initialize varNumber by default 0.

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

I considered doing a manual check ...

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

However, there are hundreds of functions where this can be a problem, not to mention the large number of parameters for each function, and therefore I would prefer to find a more general solution to the problem.

Cheers you can give.

+1


source to share


3 answers


You cannot assign values ​​to an IN parameter, but you can make them IN / OUT and then set them. However, this creates a lot of potential for abuse and confusion.

So, I think you would be better off doing with a local variable. But you can do it in a declaration. I.e



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;
+2


source


Use NVL to determine the value.



NVL( value_in, replace_with )
+4


source


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

You can write it on one line like this:

varNumber = NVL(varNumber,0);

Good luck!

+1


source






More articles:


All Articles