Oracle equivalent of Java's Varargs

Question

I'm trying to create a PL/SQL Procedure which can handle an unknown, but relatively small, number of Strings/Varchar2s as a parameter. The equivalent in Java could be to use Varargs:

public void foo(String... bar) {
}

Looking at Oracle's documentation on Collections and Records it would seem that using an Associative Array is a suitable choice, but I'm not certain.

Can you please tell me if Associative Array is the correct way to go?

If it's possible to pass in an anonymous Associative Array when invoking the call to the Procedure?

Thanks

Answer 1

you have at least 3 options:

1. (standard) use an associative array as a procedure parameter
2. define a 'sufficient' number of optional formal parameters
3. use a single varchar parameter with a defined separator char

sample code 1.)

TYPE t_map IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(20);

CREATE OR REPLACE PROCEDURE demo_1 ( vararg IN t_map ) IS
BEGIN
   /* whatever */
   NULL;
END demo_1;

/* ... somewhere else ... */
my_var t_map;

my_var('first') := 'this';
my_var('next')  := ' is a ';
my_var('last')  := 'demo';

demo_1 ( my_var );
/* ... */

sample code 2.) (max. 5 parameters)

CREATE OR REPLACE PROCEDURE demo_2 (
      vararg1 IN  VARCHAR2 DEFAULT NULL
    , vararg2 IN  VARCHAR2 DEFAULT NULL
    , vararg3 IN  VARCHAR2 DEFAULT NULL
    , vararg4 IN  VARCHAR2 DEFAULT NULL
    , vararg5 IN  VARCHAR2 DEFAULT NULL
) IS
BEGIN
   /* whatever */
   NULL;
END demo_2;

/* ... somewhere else ... */
demo_2 ( 'this', ' is a ', 'demo' );
/* ... */

sample code 3.) ( special char being ';' - must not occur inside the payload data )

CREATE OR REPLACE PROCEDURE demo_3 (
      vararg IN  VARCHAR2
) IS
   l_arg2 VARCHAR2(50);
   l_arg5 VARCHAR2(50);
BEGIN
   l_arg2 := SUBSTR(REGEXP_SUBSTR(vararg, ';[^;]*', 1, 2), 2);
   l_arg5 := SUBSTR(REGEXP_SUBSTR(vararg, ';[^;]*', 1, 5), 2);
   /* whatever */
END demo_3;

/* ... somewhere else ... */
demo_3 ( ';this; is a ;demo;;really!;' );
/* ... */

Answer 2

create or replace type strings_type as table of varchar2(50);
select * from table (strings_type ('DD','CC','EE'));