Using collections to create variable parameter functions.

If you’re familiar with java, c, c# (or, I’m sure other languages as well) then you’ve probably encountered functions that allow a variable number of input parameters without requiring a fixed set of overloading.

Java

int my_variable_function(String str, int ...intlist)

int my_variable_function(char *str, …)

int my_variable_function(string str, params int[] intlist)

PL/SQL does not expose a similar syntax for user-defined functions. However, it is possible to pass in a collection containing a variable number of values to achieve the same functionality, albeit with a slightly bulkier syntax.

For example…

FUNCTION my_variable_function(str IN VARCHAR2,
                              intlist IN inttab:= NULL)
       RETURN INTEGER

Such a function could then be used as follows:

SELECT my_variable_function('test one'),
       my_variable_function('test two', inttab(1, 2, 3)),
       my_variable_function('test three', inttab()),
       my_variable_function('test four', NULL),
       my_variable_function('test five', inttab(9,8,7,6,5,4,3,2,1))
  FROM DUAL;

One of the more common requests I’ve received that I solved with this technique is a multi-format date validator and/or date constructor. First, we need a collection type. In the example above I had a collection of integers, in this next one it will be a collection of varchar2 values, defined as follows:

CREATE TYPE vctab AS TABLE OF VARCHAR2(4000);

Then we can define a function with the collection parameter like this:

CREATE FUNCTION dynamic_to_date(p_str IN VARCHAR2,
                                p_formats IN vctab)
    RETURN DATE
    DETERMINISTIC
IS
    v_temp         DATE;
    v_successful   BOOLEAN := FALSE;
    v_index        INTEGER := p_formats.FIRST;
BEGIN
    WHILE v_index <= p_formats.LAST AND NOT v_successful
    LOOP
        BEGIN
            v_temp := TO_DATE(p_str, p_formats(v_index));
            v_successful := TRUE;
        EXCEPTION
            WHEN OTHERS
            THEN
                -- Conversion failed, try next format in the list
                v_index := v_index + 1;
        END;
    END LOOP;

    IF v_successful
    THEN
        RETURN v_temp;
    ELSE
        RAISE VALUE_ERROR;
    END IF;
END;

And then invoke it as follows:

SQL> SELECT dynamic_to_date('21 AUG 2009',
  2                         vctab('DD/MM/YYYY', 'DD/MM/YYYY HH24:MI', 'DD MON YYYY'))
  3    FROM DUAL;

DYNAMIC_TO_DATE('21
-------------------
2009-08-21 00:00:00

SQL> SELECT dynamic_to_date('21 AUG 2009 AD',
  2                         vctab('DD/MM/YYYY', 'DD/MM/YYYY HH24:MI', 'DD MON YYYY'))
  3    FROM dual;
SELECT dynamic_to_date('21 AUG 2009 AD',
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SDS.DYNAMIC_TO_DATE", line 26

Often when I build these types of functions I will include an optional parameter to provide alternate error handling. For example, returning an invoker-defined message on exception, or returning NULL.

CREATE FUNCTION dynamic_to_date(p_str IN VARCHAR2,
                                p_formats IN vctab,
                                p_on_error IN VARCHAR2:= NULL)
    RETURN DATE
    DETERMINISTIC
IS   
    v_temp    DATE;
    v_ok      BOOLEAN := FALSE;
    v_index   INTEGER := p_formats.FIRST;
BEGIN
    WHILE v_index <= p_formats.LAST AND NOT v_ok
    LOOP
        BEGIN
            v_temp := TO_DATE(p_str, p_formats(v_index));
            v_ok := TRUE;
        EXCEPTION
            WHEN OTHERS
            THEN
                -- Conversion failed, try next format in the list
                v_index := v_index + 1;
        END;
    END LOOP;

    IF v_ok
    THEN
        RETURN v_temp;
    ELSIF p_on_error IS NULL
    THEN
        RETURN NULL;
    ELSE
        raise_application_error(-20001, p_on_error, FALSE);
    END IF;
END dynamic_to_date;

Then we can use the function the same with success or different results on failure.

SQL> SELECT dynamic_to_date('21 AUG 2009',
  2                         vctab('DD/MM/YYYY', 'DD/MM/YYYY HH24:MI', 'DD MON YYYY'))
  3    FROM DUAL;

DYNAMIC_TO_DATE('21
-------------------
2009-08-21 00:00:00

SQL> SELECT dynamic_to_date('21 AUG 2009 AD',
  2                         vctab('DD/MM/YYYY', 'DD/MM/YYYY HH24:MI', 'DD MON YYYY'))
  3    FROM dual;

DYNAMIC_TO_DATE('21
-------------------


SQL> SELECT dynamic_to_date('21 AUG 2009 AD',
  2                         vctab('DD/MM/YYYY', 'DD/MM/YYYY HH24:MI', 'DD MON YYYY'),
  3                         'Input string does not match any of the supplied formats')
  4    FROM dual;
SELECT dynamic_to_date('21 AUG 2009 AD',
       *
ERROR at line 1:
ORA-20001: Input string does not match any of the supplied formats
ORA-06512: at "SDS.DYNAMIC_TO_DATE", line 29
ORA-06512: at line 1

Using the same idea you can extend your own functions with input collections of dates, numbers, user-defined types, or even ANYDATA values. I hope you find it useful.

Leave a ReplyCancel reply