How do we handle varchar2 parameter with comma delimiter and no quotes

[Vidhyapc]

Hi, My query gets directly executed in a report and a parameter is a varchar2 that gets the user_id passed with a comma delimiter but no quotes to the user_id( which are sometimes non-numeric), how can we handle this, i tried to pass this parameter to a function and return after appending quotes to it but this does not work as oracle searches user_id for the whole string returned ( eg user_id = '1','2',3'). Any help is appreciated

Thanks
Vidhya.P.C.

[debasisdas]

try using a pipe lined function.

[amitpatel66]

Just check if DBMS_UTILITIES. COMMA_TO_TABLE can be used in oracle reports. this would help you convert comma delimited values to Oracle Collection (Table) format and then you can loop through table to perform desired task on each value.

Second option EXECUTE IMMEDIATE 'SELECT * from table_name WHERE user_id IN ('||p_user_id|| ')';