How to pass arbitrary number of arguments to a PL/SQL procedure?

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”.

Advertisements

TABLE functions in Oracle 12.2

Oracle 12.2 introduced a nice little feature – when selecting from a table-function you no longer have to use the TABLE function:

Before 12.2, here is what you had to do:

SELECT *
FROM TABLE([your-collection-or-table-function])

Starting with 12.2 the code becomes shorter:

SELECT *
FROM [your-collection-or-table-function]

Example (Oracle 12.2):

SELECT *
FROM sys.odcinumberlist(5,2,6,3,78)

Result:

COLUMN_VALUE
------------
           5
           2
           6
           3
          78

Note that if you try the last query in Oracle 12.1 or any prior release, you will get the following error:

FROM sys.odcinumberlist(5,2,6,3,78)
                       *
ERROR at line 2:
ORA-00933: SQL command not properly ended

This is an indication that you may need to check your Oracle version:

SELECT *
FROM v$version
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.