CODEWITHSUNDEEP
sqloracle-databaseplsql
Ashish Chauhan
Ashish Chauhan

Reputation: 25

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.

Upvotes: 0

Views: 2242

Answers (4)

Kaizhe Huang
Kaizhe Huang

Reputation: 1006

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

Upvotes: 0

MT0
MT0

Reputation: 167972

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

Upvotes: 3

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 0

Slava N.
Slava N.

Reputation: 596

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.

Upvotes: 0

Related Questions