PLSQL - Identify where a parameter value has been defaulted

John_K

Following on from my question here: Procedure parameters - has been defaulted?

I think it would be really useful to be able to see in PL/SQL when the value of a parameter is such because that value has been defaulted, rather than explicitly set by the user user. I.e.

1. create procedure x(a in varchar2 default null, b in varchar2 default null, c in varchar2 default null) 
2. is 
3. begin 
4.   update xxtab set a1=a, b1=b, c1=c; 
5. end; 
6. / 

1. x(a=>Null, c=>'Carrot'); 

I have no way in my code of knowing whether the user explicitly supplied Null for the a parameter, or whether it is null because of the default in the procedure spec. The reason being that it would make coding table handlers/API's a lot easier - we could do things differently if the user has explicitly requested a null value in a parameter, and ignore them if they haven't supplied that parameter. We can do this by defaulting dummy "out of range" values, but I find that a bit clumsy. I would like an isDefaulted function (or pseudo-property of a parameter).

Sven W.

I feel this suggestion hurts the basic design principle of "separation of concerns".

It should not matter how a certain value was supplied. Only the value itself should matter.

If it is important to have this distinction, then I would add another parameter, like a "Call mode" or something similiar to separate the different cases.

John_K

I feel this suggestion hurts the basic design principle of "separation of concerns".

It should not matter how a certain value was supplied. Only the value itself should matter.

If it is important to have this distinction, then I would add another parameter, like a "Call mode" or something similiar to separate the different cases.

I appreciate your point, and kind of agree, however I'd think it isn't so much of "how it was supplied" but "whether it was supplied". 99% of the time it doesn't matter - and as you said, it's the value that matters, however when you are asking a user to provide the value of something to make an update, you don't know if the user said "the value should be null" or whether they said "I don't know what the value should be".

Dinidu Hewage

My opinion is that this will make developers' life much hard. Because when you are testing the method you will get two types of nulls which should be verified the source of null. So I suggest that this should be handled by the business logic such as you have suggested, using dummy value.

John_K

My opinion is that this will make developers' life much hard. Because when you are testing the method you will get two types of nulls which should be verified the source of null. So I suggest that this should be handled by the business logic such as you have suggested, using dummy value.

I wasn't suggesting it be a different type of null - it was a suggestion that if the requirement was to differentiate then it is possible to do so. So the value would still be null - just we could use a pseudo column or function to determine whether that had been supplied as null or not (if required).

Dinidu Hewage

I wasn't suggesting it be a different type of null - it was a suggestion that if the requirement was to differentiate then it is possible to do so. So the value would still be null - just we could use a pseudo column or function to determine whether that had been supplied as null or not (if required).

Yes I understand, you are suggesting a way to find the source of "Null". My worry is that It would add an extra complexity for the developers who debug the code later.

Sven W.

It seems this idea   from Kim tackles the same problem, although with a slightly different suggestion.