Oracle BIF Drawbacks

The foremost drawback is that until Oracle8i you could not use an index to query a column that contained a BIF.
For example, assume that we have the LAST_NAME column of the CUSTOMER table indexed.
The following query would use the index:

Select * from customer where last_name like ‘A%’; 
This query, because it uses a BIF, cannot use the index:
Select * from customer where last_name like to_upper(‘a%’);

Here is the code to create a custom SQL BIF. Note that the DETERMINISTIC clause is required for an SQL BIF. This is because you must tell the database that the function will always return the same output value when given the same input value.

CREATE OR REPLACE FUNCTION 
  plus_tax(p_book_retail_price number) 
RETURN NUMBER DETERMINISTIC 
AS 
 price_plus_tax NUMBER(5,2); 
BEGIN 
 -- Tax is set at 7% 
 price_plus_tax := p_book_retail_price +  p_book_retail_price*.07;
 return price_plus_tax; 
END;