|
|
|
|
|
|
| Author |
Message |
snarks@gmail.com
*nix forums beginner
Joined: 07 Jun 2006
Posts: 12
|
 Posted: Mon Jul 17, 2006 9:32 am Post subject:
Calling functions - NULL vs default
|
|
|
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. |
|
| Back to top |
|
 |
Martin T.
*nix forums beginner
Joined: 07 Mar 2005
Posts: 39
|
| Posted: Tue Jul 18, 2006 11:28 am Post subject:
Re: Calling functions - NULL vs default
|
|
|
PhilHibbs wrote:
| Quote: | 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.
|
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 |
|
| Back to top |
|
|
Thomas Sommerfeld
*nix forums beginner
Joined: 18 Jul 2006
Posts: 1
|
| Posted: Tue Jul 18, 2006 10:00 pm Post subject:
Re: Calling functions - NULL vs default
|
|
|
PhilHibbs wrote:
| Quote: | 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.
Hi Phil, |
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 |
|
| Back to top |
|
|
snarks@gmail.com
*nix forums beginner
Joined: 07 Jun 2006
Posts: 12
|
| Posted: Wed Jul 19, 2006 9:51 am Post subject:
Re: Calling functions - NULL vs default
|
|
|
Thomas Sommerfeld wrote:
| Quote: | Hi Phil,
what a about testing the parameters in the function body and assign
default values to local variables if a parameter is NULL?
|
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. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Nov 20, 2008 9:07 pm | All times are GMT
|
|
Mortgage Loans | Mortgages | Mortgage Calculator | Debt Consolidation | Western Union Money Transfer
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
