Equivalent to Oracle Java Varargs

Question

I am trying to create a PL / SQL procedure that can handle an unknown but relatively small number of lines / Varchar 2s as a parameter. An equivalent in Java could be the use of Varargs:

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

Looking at the Oracle documentation on collections and records , it seems that using an associative array is an appropriate choice, but I'm not sure.

Could you tell me if an associative array is suitable?

If it is possible to call an anonymous associative array when calling a procedure call?

thanks

Answer 1

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

Answer 2

You have at least 3 options:

• (standard) uses an associative array as a procedure parameter
• define a “sufficient” number of optional formal parameters
• use one varchar parameter with a specific char delimiter

code example 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 ); /* ... */ 

code example 2.) (no more than 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' ); /* ... */ 

code example 3.) (special char be ';' - should 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!;' ); /* ... */