Thej Kumar Thej Kumar - 3 months ago 9
SQL Question

passing variable value in pl/sql query block

I struck up with one issue. please need your expertise..

PL/SQL block contain a variable p_user_id varchar(50)

The value set to below value

p_user_id := '101,102,103';


I have query like below in PL/SQl block

select Count(*) into v_count
from users
where user_id not in (p_user_id);


Every time when I call this PL/SQL block v_count value is same i.e. total no of records in users table. Not In clause not working properly. Please help.

MT0 MT0
Answer

Using LIKE string comparison:

DECLARE
  p_user_id VARCHAR2(200) := '101,102,103';
  v_count   INT;
BEGIN
  SELECT Count(*)
  INTO   v_count
  FROM   users
  WHERE  ',' || p_user_id || ',' NOT LIKE '%,' || user_id || ',%';
END;
/

Using collections:

CREATE OR REPLACE TYPE intlist IS TABLE OF INT;
/

DECLARE
  p_user_id INTLIST := INTLIST( 101, 102, 103 );
  v_count   INT;
BEGIN
  SELECT Count(*)
  INTO   v_count
  FROM   users
  WHERE  user_id NOT MEMBER OF p_user_id;
END;
/