To add multiple parameters with comma separated

To add multiple parameters with comma separated

------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE APPS.xx_multiple_parameter_acc
AS
g_var varchar2 (3000) := NULL;
g_time DATE;

FUNCTION return_value (i_para varchar2)
RETURN varchar2;
END;
/



CREATE OR REPLACE PACKAGE BODY APPS.xx_multiple_parameter
AS
FUNCTION return_value (I_para VARCHAR2)
RETURN VARCHAR2
AS
l_len NUMBER := LENGTH (I_para);
BEGIN
IF g_time IS NULL
THEN
g_time := SYSDATE;
g_var := i_para;
ELSE
IF (((SYSDATE - g_time) * 60 * 60 * 24) > 30)
THEN
g_time := SYSDATE;
g_var := i_para;
ELSE
IF ( NVL (LENGTH (g_var), 0)
- NVL (LENGTH (REPLACE (g_var, i_para, NULL)), 0)
- l_len != 0
)
THEN
IF g_var IS NULL
THEN
g_var := i_para;
ELSE
g_var := ''||i_para||''','||''''||g_var||'';
END IF;
ELSE
g_var := REPLACE (g_var, i_para);
END IF;
END IF;
END IF;

g_var := REGEXP_REPLACE (REGEXP_REPLACE (g_var, '^,|,$', ''), ',,', ',');
RETURN g_var;
END return_value;
END xxstx_multiple_parameter;
/

Use the function in your select statement





------------------------------------------------------------------------------------------------