ORACLE PL/SQL: Functions and Optional Parameters, How?

Question

I'm looking for the optimal way to create a function that can accept no parameters and return all results, but also accepts parameters and return those results.

The standard I've been dealing with at my job is this:

FUNCTION get_records (
  i_code                                 IN records.code%type := NULL,
  i_type                                 IN records.type%type := NULL
) RETURN results

The problem is that I want to return records that have a type of NULL as well, and using:

WHERE type = nvl(i_type, type)

It only returns records with actual types and not the null records.. for obvious reasons. I was just wondering if there is a standard way of doing this that could be implemented across all functions we use. Coincidentally, if I provide a parameter... I don't want the NULL values of that field.

Answer 1

why not simply what you have with the addition of

 type = i_type OR (i_type IS NULL AND type IS NULL)

that way, when the passed in param is null it looks for everything (including nulls) or the specified value. Now, if you just want the nulls...

example (change the value from null to 5 and you will see the output)

WITH TESTDATA AS (
        SELECT LEVEL dataId
          FROM DUAL
         CONNECT BY LEVEL <= 100
        UNION
        SELECT NULL 
         from dual
)
SELECT * 
  FROM TESTDATA
 where dataId = :n or (:n is null AND dataId is null) ;

Whereas :n = 6 the results will be

DATAID                 
---------------------- 
6

(deleted a new post, misread a param) But I like the new approach and avoiding the NVL

here's a nifty way to do it if you are not adverse to dynamic sql

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

Answer 2

The standard way to solve this issue is to overload the function, instead of using default values:

FUNCTION get_records (
  i_code                                 IN records.code%type,
  i_type                                 IN records.type%type
) RETURN results;

FUNCTION get_records (
  i_code                                 IN records.code%type
) RETURN results;

FUNCTION get_records RETURN results;

Note: If you also need a version i_type by itself, you might have trouble if it has the same underlying type as i_code - in which case you'd need to use a different name for the function.

Answer 3

Right off the top of my head, I would guess the use of the DEFAULT keyword would do the trick, doesn't it? (The following link will give some further details.)

1. Using the DEFAULT keyword.

   CREATE OR REPLACE FUNCTION get_records (
       i_code IN records.code%type DEFAULT NULL,
       i_type IN records.type%type DEFAULT NULL
   ) RETURN results
   

EDIT #1

If I understand the question correctly, you want to return all of the records when the i_type parameter is NULL. In absence of further details, my guess would be the following.

CREATE OR REPLACE FUNCTION get_records (
    i_code IN records.code%TYPE DEFAULT NULL,
    i_type IN records.type%TYPE DEFAULT NULL
) RETURN results
BEGIN
    IF (i_type IS NULL) THEN
        select *
            from table
    ELSE
        select *
            from table
            where type = NVL(i_type, type)
    END IF

    EXCEPTION
        WHEN OTHERS THEN
            NULL
END

That is all I can do with the information provided, although the function body is commented below.

EDIT #2

I am a bit rusty from Oracle, so I consulted some documentation as linked below:

Oracle/PLSQL: NVL Function

As I have read it, you had better use the NVL function within your SELECT instruction, and not your WHERE clause.

In the end, what is your question exactly? Could you make it crystal clear?

Answer 4

To reiterate my understanding of the issue: You can't take the default value of NULL to mean "return all records", because the current expected behavior is that it will return only those records where the value is actually NULL.

One possibility is to add a boolean parameter corresponding to each lookup parameter, which indicates whether it should actually be used to filter results. A potential issue with this is that a caller might specify a lookup value but fail to set the flag to true, producing unexpected results. You can guard against this at runtime by raising an exception if the flag is false for any lookup value other than NULL.

Another possibility is to define an out-of-domain value for each lookup column -- e.g. if the string 'ANY' is passed for the parameter, it will not filter values on that column. This should work fine as long as you can find a worthwhile sentry value for each column. I'd suggest declaring the sentry values as constants in some package, so that calls to the function could look like get_records( PKG.ALL_CODES, 'type1' ).

Answer 5

I have been using the following hack (assuming p_product is an optional procedure input):

where t.product = decode(p_product, null, t.product, p_product)

procedure get_data(p_product in number := null)
...
select *
from tbl t
where t.product = decode(p_product, null, t.product, p_product);
...

This will create 'where 1=1' scenario when the product is not passed in, and use the product otherwise. One potentially major drawback is that this parses to the same execution plan regardless of parameters.