Possible? Functions that disregard variables?

[Ken Pawluk]

I'm new to Oracle and SQL so bear with me...
I have a function that uses 3 variables (string1, string2, integer1). I use this function in one portion of code using all 3 variables. Great. Now, what if I wanted to use it from somewhere else, but only give it 2 parameters and ignore the 3rd (string1, string2)? Is this possible? Or do I have to create a whole new oracledataset with these two variables, and a new function to go with it? Or do I have to change the SQL and variables through code?
thx
Ken

 

[mhajduk]

Ken

give me a little more information of what you are trying to do and i will see if i can help you.

Thanks
Mike Hajduk

 

[jpickup]

Parameters passed to procedures can have default values (much like those in C++). An example of the syntax of such a declaration is:

FUNCTION lookup_price(
SecurityCode IN VARCHAR2,
PriceSource IN VARCHAR2,
ValueDate IN DATE,
ZeroOnMissing IN BOOLEAN DEFAULT true) RETURN NUMBER;

You can then call the function leaving out the last argument, for example:
begin
price := lookup_price('SomeSecurity', 'Bloomberg', sysdate);
end;

in this case true will be passed by default as the last parameter.

[This message has been edited by jpickup (edited 08 June 2001).]

 

[Ken Pawluk]

I just re-read my message, and it doesn't make sense to me either. Sorry for the confusion.
Let me try to clarify.
I have a TOracleDataSet with SQL as follows:
-----------------------
select um_item_desc.*, um_item_desc.rowid from UM_ITEM_DESC
where (item_desc = :s_item_desc
and item_code_char = :s_item_code_char
and component_code = :i_component_code)
-----------------------
I use the following code in a Delphi function to set the variables:
-----------------------
function GetResult(sItemDesc, sItemCodeChar: string; iCompCode: integer): boolean;
begin
result := false;
SetVariable('s_item_desc', sItemDesc);
SetVariable('s_item_code_char',sItemCodeChar);
SetVariable('i_component_code',iCompCode);
Refresh();
if Recordcount > 0 then
result := true;
end
------------------------
The first time I use this function I call it like:
GetResult(sCodeDesc, sCodeChar, iCompCode);

Now, here's what I'm asking:
The second time I use this function I would like to be able to disregard the third parameter and still have Oracle pick up hits based on the two parameters:
GetResult(sCodeDesc,sCodeChar, ? );

Otherwise, I have to have a completely different TOracleDataSet with only 2 variables and possibly another (almost similar) function that only sets 2 variables. Or, I change the variables and SQL in this dataset at runtime.

My problem isn't as complicated as jpickup thinks! It'll be awhile before I start using Oracle, procedures, triggers, etc...! Remember that I'm a newbie with this so don't overthink it.

BTW, Delphi can use default parameters also (not just C++).

thx,
Ken

[This message has been edited by Ken Pawluk (edited 08 June 2001).]

 

[mhajduk]

here is a quick and dirty way without using parm lists and lets you send any of the three. could be syntax errors did it quickly.
I hope this gives you some ideas.
-----------------------
function GetResult(sItemDesc, sItemCodeChar: string; iCompCode: integer): boolean;
var firstline:boolean;
begin
result := false;

// exit if all three are blank

if (sitemdesc='') and (sitemcodechar='') and (icomcode=0) then exit;

// build the new sql script

close;
firstline:=true;
sql.clear;
sql.add('select um_item_desc.*,um_item_desc.rowid from UM_ITEM_DESC where (');
if sitem_desc'' then
begin
setvariable('s_item_desc',sitem_desc);
sql.add('item_desc = :s_item_desc ');
firstline:=false;
end;
if sitem_codechar'' then
begin
if firstline then
Sql.add(' and ');
setvariable('s_item_code_code',sitem_codechar);
sql.add('item_code_char= :s_item_code_char ');
end;
if icompcode>0 then
begin
if firstline then
Sql.add(' and ');
setvariable('i_component_code',icompcode);
sql.add('component_code= :i_component_code');
end;
sql.add(' )');

// execute the script and return result

Refresh();
if Recordcount > 0 then
result := true;
end
------------------------

Goodluck
Mike Hajduk

 

[mhajduk]

error in above code the lines that say
if firstline then
should be
if not firstline then

MHajduk