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.
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;
/
There are no rows in the users table where user_id has the value '101,102,103'.
If you want to split a string into a set of separate values, you have to do a bit more than just use it in a not in expression.
Some possible approaches:
declare
p_csvlist varchar2(100) := '2002, 7369, 7499, 7902, 7934';
v_count integer;
begin
select count(*) into v_count
from emp e
where e.empno in
( select extractvalue(xt.column_value,'e')
from table(xmlsequence
( extract
( xmltype('<coll><e>' || replace(p_csvlist,',','</e><e>') || '</e></coll>')
, '/coll/*') )) xt );
dbms_output.put_line(v_count || ' rows');
end;
or this
declare
p_csvlist varchar2(100) := '2002, 7369, 7499, 7902, 7934';
v_count integer;
begin
select count(*) into v_count
from emp e
where e.empno in
( select regexp_substr(p_csvlist, '[^,]+',1,rownum)
from dual
connect by rownum <= length(p_csvlist) - length(replace(p_csvlist,',')) );
dbms_output.put_line(v_count || ' rows');
end;
or this (only works with numeric values):
declare
p_csvlist varchar2(100) := '2002, 7369, 7499, 7902, 7934';
v_count integer;
begin
select count(*) into v_count
from emp e
where e.empno in
( select to_number(xt.column_value)
from xmltable(p_csvlist) xt );
dbms_output.put_line(v_count || ' rows');
end;
Examples are from my FAQ article: www.williamrobertson.net/documents/comma-separated.html
You can use sys.DBMS_DEBUG_VC2COLL --collection datatype provided by Oracle itself
DECLARE
p_user_id sys.DBMS_DEBUG_VC2COLL := sys.DBMS_DEBUG_VC2COLL( 101, 102, 103 );
v_count INT;
BEGIN
SELECT Count(*)
INTO v_count
FROM users
WHERE user_id not in(select * from table(p_user_id));
END;
/
In clause doesn't work properlyIt works as it suppose to work. You are not passing a list of values to theinclause, you are passing just one value -'101,102,103'