Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed00.sul.t-online.de!t-online.de!inka.de!rz.uni-karlsruhe.de!feed.news.schlund.de!schlund.de!news.online.de!not-for-mail
From: Thomas Sommerfeld <TSommerfeld@gmx.de>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Calling functions - NULL vs default
Date: Wed, 19 Jul 2006 00:00:00 +0200
Organization: 1&1 Internet AG
Lines: 40
Message-ID: <e9jlq4$vm1$2@online.de>
References: <1153128740.157959.71690@b28g2000cwb.googlegroups.com>
NNTP-Posting-Host: pd95186cd.dip0.t-ipconnect.de
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: online.de 1153260164 32449 217.81.134.205 (18 Jul 2006 22:02:44 GMT)
X-Complaints-To: abuse@einsundeins.com
NNTP-Posting-Date: Tue, 18 Jul 2006 22:02:44 +0000 (UTC)
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
In-Reply-To: <1153128740.157959.71690@b28g2000cwb.googlegroups.com>
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:128635

PhilHibbs wrote:
> 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