Oracle Function Exception - No_DATA_FOUND

 I wanted to write a function that accepts 2 parameters. An Item_ID and Manufacturer and returns the support time.

In most cases the first parameter (Item_ID) is sufficient. There are few cases we need the Manufacturer as well.

Unfortunately sometimes customers don't enter the second parameter correctly or I get a NULL there instead of the Manufacturer. In those cases I still want to return a value based on the first parameter (Item_ID) only.

At first I tried using few selects into variables in the function and doing if then else on the result... But it broke each time I didn't have value for the second parameter. I could write a second function with exception and call it from the main function. I preferred this solution, though there are some extreme cases it might not cover.

So I used the Exception option in the function code here:

create or replace function f_SUPPORT (P_ITEM IN NUMBER, P_MANUFACTURER IN VARCHAR2)
RETURN NUMBER is SUP NUMBER;
BEGIN
select Support_mnth into SUP from warranty where item=p_item and MANUFACTURER=P_MANUFACTURER;
return(SUP);
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
select Support_mnth into SUP from warranty where item=p_item;
return(SUP);
end;
/