In an Oracle function or procedure call, there can be parameters that
have defaults, such as language code. If NULL is passed, this overrides
the default. What I want to do is to create a table that mirrors the
function's parameter list and call the function using the rows in that
table, but I don't want to override defaults with NULLs. How can I do
this? Is dynamically generating a function call the only way? Might
this cause a significant performace hit, for instance if I were
creating thousands of employees through the HRMS create_employee API?

Phil Hibbs.

PhilHibbs wrote:
Hmm ... do you want a function that ignores NULL parameters (if NULL is
passed or parameter is not passed, default value is used) or do you
want a function with 'normal' default parameters?
As I see it you can have a function that does one of both things, but
not one(1) function that does both.

best,
Martin

PhilHibbs wrote:

what a about testing the parameters in the function body and assign
default values to local variables if a parameter is NULL?
Something like:
function ProcessRow(Col1 in MyTable.Col1%Type, Col2 in
MyTable.Col2%Type, ...) return number is
vCol1 MyTable.Col1%Type;
vCol2 MyTable.Col2%Type;
begin
if Col1 is null then
vCol1 := DefaultValueCol1;
else
vCol1 := Col1;
end if;
if Col2 is null then
vCol2 := DefaultValueCol2;
else
vCol2 := Col2;
end if;
...
end;

Best regards
Thomas

--
For answers by personal mail use: thomas.sommerfeld at domain ust-gmbh.de

Thomas Sommerfeld wrote:
That's fine for my own functions, but I want to call HRMS API functions
that have defaults. I want to be able to specify the arguement, but
pass "nothing" in so that the default takes precedence.

The HRMS Data Pump White Paper says that DP works this way - you can
leave a column in the DP table as NULL and the default will override
the NULL - so I looked in the implementation and couldn't see anything
clever that implemented this behaviour, and when I tested it, the NULL
in the table is what got used and not the default. In other words, the
white paper LIES about this! It says it does what I was wanting to do,
but in fact does not! This implies to me that this is a difficult thing
to do (maybe the author of the WP wanted to implement this feature, but
it proved too difficult or slow and so was removed).

Phil Hibbs.