CODEWITHSUNDEEP
sqloracle-databaseplsql
Thej Kumar
Thej Kumar

Reputation: 91

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.

Upvotes: 1

Views: 3467

Answers (3)

Prashant Mishra
Prashant Mishra

Reputation: 637

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;
    /

Upvotes: 0

William Robertson
William Robertson

Reputation: 15991

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

Upvotes: 1

MT0
MT0

Reputation: 167877

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;
/

Upvotes: 1

Related Questions