Is it possible for an Oracle package to identify which parameters have been supplied?
I need to build a stored package procedure that can accept multiple parameters e.g. 20, however, some calls to the package will supply fewer than 20 parameters, some will provide all 20 parameters.
The values of those parameters that are supplied will be used to insert/update a table, however, for the parameters that aren't supplied the related columns in the table will be left untouched.
I'm aware that I could identify which parameters have been omitted by setting default values that would never be supplied to the procedure. This would work okay for character and date values, however it isn't possible to identify a particular numeric value that wouldn't be supplied.
I could also use overloading to handle the various numbers of parameters, but this could result in a lot of overloaded definitions of the procedure and I would prefer to do it through inspecting the data.
Is it possible to determine which parameters have NOT been supplied to a package procedure?
The values of those parameters that are supplied will be used to insert/update a table, however, for the parameters that aren't supplied the related columns in the table will be left untouched.
I'm aware that I could identify which parameters have been omitted by setting default values that would never be supplied to the procedure. This would work okay for character and date values, however it isn't possible to identify a particular numeric value that wouldn't be supplied.
I could also use overloading to handle the various numbers of parameters, but this could result in a lot of overloaded definitions of the procedure and I would prefer to do it through inspecting the data.
Is it possible to determine which parameters have NOT been supplied to a package procedure?
Christoffer Swanström
I'm not aware of any way to check which parameters were supplied and which had default values substituted. Of course you could compare the parameter value with the default value, but that still leaves the possibility that the default value was explicitly supplied in the procedure call.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Sean.
I wasn't aware this could be done, I guess I just needed confirmation.
The link you provided is useful and I've logged my interest.
I wasn't aware this could be done, I guess I just needed confirmation.
The link you provided is useful and I've logged my interest.
Hmm, at least an assist would have been good for my answer which first stated that what you ask for can't be done...
a split is fair
ASKER
Sorry tosse, but I didn't feel you provided anything that I didn't already know, however, sdstuber did provide a link that confirmed my belief and a potential means of registering an interest in getting this functionality in the future.
Also, you stated "I'm not aware of any way...". That was my stance and I wanted confirmation. sdstuber gave this by categorically stating "No there is no such facility".
Also, you stated "I'm not aware of any way...". That was my stance and I wanted confirmation. sdstuber gave this by categorically stating "No there is no such facility".
Well, I guess I have to start making stronger statements. Usually answers qualified with "to my knowledge", AFAIK, etc have been good enough.