WHERE IN condition not accepting String value

I am dynamically constructing a string with name user_data in PL/Sql procedure by appending USERNAMEs, single quotes(') and commas(,) of the form

'abc123','xyz456','pqr789'

But when I pass this string to WHERE IN condition of SELECT statement

SELECT * FROM table_name WHERE USERNAME IN (user_data)

It is throwing a NO_DATA_FOUND exception.

On the other hand if my string contains only one user without the quotes, it is able to find that user and display desired output.

The datatype of string user_data is varchar2.

You can do it using a collection:

CREATE TYPE VARCHAR2s_Table IS TABLE OF VARCHAR2(100);
/

Then enter your data like this:

SELECT *
FROM   table_name
WHERE  user_data MEMBER OF VARCHAR2s_Table( 'abc123','xyz456','pqr789' );

Alternatively:

You can create a function to split the data and generate the collection:

CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN VARCHAR2_TABLE DETERMINISTIC
AS
  p_result       VARCHAR2_TABLE := VARCHAR2_TABLE();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Then you can do:

SELECT *
FROM   table_name
WHERE  user_data MEMBER OF split_String( 'abc123,xyz456,pqr789', ',' );

or:

SELECT *
FROM   table_name
WHERE  user_data MEMBER OF split_String( TRIM( '''' FROM '''abc123'',''xyz456'',''pqr789''' ), ''',''' );