SQL & PL/SQL

Hi,

The values to input parameters in my procedure passed may be single value or multiple values with ',' separation or all(% in oracle)

i need to use these values in select query.

one way is to assign them in a variable and use it in select like below


IF (PARAM1 IS NULL OR PARAM1 = COMMON_PKG.PG_ALL_CONST_C) THEN
v_PARAM1 _str := 'A.COL1 LIKE ''%''';
ELSE
v_PARAM1 _str := 'A.COL1 IN ('''||PARAM1 ||''')';
END IF;

or using connect by for multiple values converting row into column wise and joining the tables in select clause.

Can any one suggest other than the above two ways if possible.

Thanks in advance.