WHERE IN condition not accepting String value

Question

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.

Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Erich Kitzmueller, Feb 26 '16 at 10:18
or this http://stackoverflow.com/questions/6155146/problem-using-oracle-parameters-in-select-in/6155215#6155215 — Erich Kitzmueller, Feb 26 '16 at 10:22
Can you please print and post the content of your string `user_data` in both cases, working and not working? — Aleksej, Feb 26 '16 at 10:34
Possible duplicate of [PL/SQL - Use "List" Variable in Where In Clause](http://stackoverflow.com/questions/35231757/pl-sql-use-list-variable-in-where-in-clause) — MT0, Feb 26 '16 at 10:41
@ammoQ That first link is for SQL Server and is not a duplicate. — MT0, Feb 26 '16 at 10:42
@Aleksej my string is like 'abc123','xyz456','pqr789' . In this case it does not work i.e returns no data, although all the three users exists. While when the string is like abc123 (without quotes), then it comes up with the result. — Ashish Chauhan, Feb 26 '16 at 10:47
@MT0 I don't think there is much difference regarding this problem between SQL Server and Oracle. — Erich Kitzmueller, Feb 26 '16 at 10:50
@AshishChauhan Your string is still a string, no matter what it contains, but `in` requires a list of values. You cannot format the string in a way that turns it magically into a list. — Erich Kitzmueller, Feb 26 '16 at 10:52
@ammoQ I have tried using a array of characters by creating a type and passing that in place of string but then the procedure does not compile. It shows **inconsistent datatype:expected CHAR got MY_CUSTOM_STRING_ARRAY** — Ashish Chauhan, Feb 26 '16 at 10:57
@AshishChauhan there is no easy solution. Look at this link: http://www.techonthenet.com/oracle/questions/cursor3.php There are some workarounds, but putting the values in an array and using that array is also tricky. Here are some more hacks to do that: https://community.oracle.com/thread/3542574?start=0&tstart=0 — Erich Kitzmueller, Feb 26 '16 at 11:12
@ammoQ Not that tricky - [my answer](http://stackoverflow.com/a/35649649/1509264) gives a simple PL/SQL function that will create an array by iterating over a string looking for delimiters and there are plenty of other ways of doing it using [hierarchical SQL queries](http://stackoverflow.com/a/30990470/1509264) (usually with regular expressions). — MT0, Feb 26 '16 at 11:18
@MD0: Tricky enough. One might expect that there is a direct way to pass an array to an `in` clause, but there isn't. Anyway, your solution is probably as good as it gets. — Erich Kitzmueller, Feb 26 '16 at 11:54
@ammoQ Doing it all in SQL adds complexity but when you are in the PL/SQL scope or are connecting via an external language then you can [pass in an array directly as a bind variable](http://stackoverflow.com/questions/34699223/how-to-load-a-large-number-of-strings-to-match-with-oracle-database/34699771#34699771). — MT0, Feb 26 '16 at 12:01
@MT0: I know, but wouldn't it be nice if you could just write `PreparedStatement st = con.prepareStatement("select * from vour_table where id in :your_collection");` instead of all that `SELECT t.* FROM your_table t INNER JOIN TABLE( :your_collection ) c ON t.id = c.COLUMN_VALUE");` trickery? — Erich Kitzmueller, Feb 26 '16 at 12:19
@ammoQ You *should* be able to do it using `MEMBER OF` (as I do below) rather than `IN`. So: `con.prepareStatement("SELECT * FROM your_table WHERE id MEMBER OF :your_collection");` (However, I don't have my test system available at the moment to verify this.) — MT0, Feb 26 '16 at 12:24

MT0 · Answer 1 · 2016-02-26T11:06:55.670

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''' ), ''',''' );

score 0 · Answer 2 · answered Feb 26 '16 at 10:29

It isn't work like that. Your expression is equal to

SELECT * FROM table_name WHERE USERNAME = '''abc123'', ''xyz456'', ''pqr789''';

That's why no results found.

You could do something like this, to achive desire effect:

SELECT * FROM table_name WHERE user_data like '%''' || USERNAME || '''%';

But it wouldn't be the same though.

score 0 · Answer 3 · answered Feb 26 '16 at 11:01

You can a dynamic string in your IN clause with something like this:

SQL> declare
  2      user_data varchar2(1000);
  3      vSQL      varchar2(2000);
  4      type      tabUser is table of varchar2(16);
  5      outData   tabUser;
  6  begin
  7      user_data := '''abc'', ''123'', ''zzz''';
  8      --
  9      vSQL := 'select userName from table_name where username in (' || user_data || ')';
 10      --
 11      execute immediate vSQL bulk collect into outData;
 12      --
 13      dbms_output.put_line('user_data: ' || user_data);
 14      for i in outData.first .. outData.last loop
 15          dbms_output.put_line('User: ' || outData(i));
 16      end loop;
 17  end;
 18  /
user_data: 'abc', '123', 'zzz'
User: abc
User: 123

PL/SQL procedure successfully completed.

SQL> select * from table_name;

USERNAME
----------------
abc
ABC
123

In this case you always have to use quoted names in your string, no matter if you have one or more usernames.

score 0 · Answer 4 · answered Feb 26 '16 at 22:23

Your select statement

SELECT * FROM table_name WHERE USERNAME IN (user_data)

will be treated as

SELECT * FROM table_name WHERE USERNAME = 'abc123','xyz456','pqr789'

which is not correct.

One alternative is

SELECT * FROM table_name WHERE INSTR(user_data, USERNAME) > 0

WHERE IN condition not accepting String value

4 Answers4

Linked

Related