I was looking for an answer to this question for quite some time and ended up developing my own approach.
As of version 12.1, Oracle does not offer this feature which is widely available in most modern procedural languages. I found only one Oracle feature that somehow resembles the one in question – a built-in default constructor for PL/SQL collection types. We can pass arbitrary number of values into such constructors.
Here is a simple example:
DECLARE
TYPE vc_table IS TABLE OF VARCHAR2(30);
v_table vc_table;
BEGIN
v_table:=vc_table('ABC', 'DEF', 'GHI');
FOR i IN 1..v_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_table(i));
END LOOP;
END;
/
Result:
ABC
DEF
GHI
The line that initializes v_table variable references a constructor that takes 3 values as arguments. It can take more (or less) values as well.
How could we exploit this feature to accept variable number of arguments in our procedures/functions?
The problem with the above example is that we have to have a collection type before we can use it and this would make our procedure/function dependent on such custom type.
A necessary help comes from Oracle’s built-in collection types:
sys.odcivarchar2list, sys.odcinumberlist, etc.
Let say we need to mimic Oracle’s built-in GREATEST function for a variable number of numeric arguments. Here is how we could use sys.odcinumberlist type:
CREATE OR REPLACE FUNCTION my_greatest(p_list sys.odcinumberlist)
RETURN NUMBER
AS
v_result NUMBER;
BEGIN
SELECT CASE WHEN SUM(NVL2(COLUMN_VALUE,0,1))>0 THEN TO_NUMBER(NULL)
ELSE MAX(COLUMN_VALUE)
END
INTO v_result
FROM TABLE(p_list);
RETURN v_result;
END;
/
Remember, the GREATEST function returns NULL if at least one of its arguments is NULL. That’s why we need to check for NULLs in the p_list collection.
Here is how we could test the function:
SELECT my_greatest(sys.odcinumberlist(45,2,46,65,2,1,0)) "greatest",
my_greatest(sys.odcinumberlist(45,2,NULL,65,2,1)) "null_greatest"
FROM dual
Result:
| greatest | null_greatest |
|---|---|
| 65 | – |
The use of sys.odcinumberlist constructor is not very elegant as the data type name is very long, but it does do the trick. You can pass as many arguments to the constructor as you wish. To make things look a bit prettier, we can create a short synonym:
CREATE OR REPLACE SYNONYM nl FOR sys.odcinumberlist /
Now, the last (testing) query will transform to the following:
SELECT my_greatest(nl(45,2,46,65,2,1,0)) "greatest",
my_greatest(nl(45,2,NULL,65,2,1)) "null_greatest"
FROM dual
It still does not look like true “parameter array” with the arbitrary length, but it is very close.
The following is a short list of Oracle’s built-in collection types that you can use for mimicking “arbitrary number of arguments”:
- sys.odcidatelist
- sys.odciobjectlist
- sys.odcirawlist
- sys.odcinumberlist
- sys.odcivarchar2list
For anything more complex, you may need to create your own collection type.
If you find this post useful, please press the LIKE button and subscribe.
My Oracle Group on Facebook:
Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Suggested Reading:
Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.
sqlpatterns 