pl/sql function- pass multiple varchar2 values dynamically for SELECT ... NOT IN()

Question

I have a table EMP with following definition:

EMP_ID         NOT NULL   NUMBER(6)    
EMP_NAME       NOT NULL   VARCHAR2(25) 
EMAIL          NOT NULL   VARCHAR2(25) 
PHONE_NUMBER              VARCHAR2(20) 
HIRE_DATE      NOT NULL   DATE         
JOB_ID         NOT NULL   VARCHAR2(10) 
SALARY                    NUMBER(8,2)

If we want to count employees whose name not in 'King', 'Steve', 'John' we simply use this query :

SELECT count(*) FROM emp WHERE emp_name NOT IN('King','Steve','John');

Now Here is what I want above this:

What I want to do is, Create a PL/SQL Function which returns number of count according to the dynamic input, Like if we pass:

SELECT count_emp('King,Steve,John') FROM dual;
SELECT count_emp('William,Donald') FROM dual;
SELECT count_emp('Daniel') FROM dual;

needs to return appropriate count, how can I achieve this using PL/SQL FUNCTION

This is what I have tried and Needs guideline:

CREATE OR REPLACE FUNCTION count_emp(emp_nm IN varchar) 
RETURN number
IS
  cnt      NUMBER;
BEGIN
  SELECT count(*) INTO cnt FROM emp WHERE emp_name NOT IN(emp_nm);
  RETURN cnt;
END;

it is giving result for single name, but how can I split/format multiple input(i.e. emp_nm) to pass in NOT IN()?

you could get the string into a variable split it and then pass the list into the in clause such that you would have 'King','Steve','John' instead of 'King,Steve,John' — Rat-a-tat-a-tat Ratatouille, Mar 05 '14 at 05:55
You can use dynamic SQL as well. There are plenty of similar questions on SO. — Yaroslav Shabalin, Mar 05 '14 at 06:19
I.e. [this](http://stackoverflow.com/questions/12800180/pl-sql-comma-seperated-list-within-in-clause) and [another one](http://stackoverflow.com/questions/11553398/dynamic-number-of-where-condition-in-oracle-sql). — Yaroslav Shabalin, Mar 05 '14 at 06:27
possible duplicate of [How to use parameters in a 'where value in...' clause?](http://stackoverflow.com/questions/11041647/how-to-use-parameters-in-a-where-value-in-clause) — user272735, Mar 05 '14 at 11:35

score 3 · Accepted Answer · answered Mar 05 '14 at 06:10

Try like this,

CREATE OR REPLACE 
FUNCTION count_emp(emp_nm IN VARCHAR) 
RETURN NUMBER
IS
     cnt      NUMBER;
BEGIN
     SELECT count(*) 
     INTO   cnt 
     FROM   emp
     WHERE  ename NOT IN(
          SELECT regexp_substr (emp_nm, '[^,]+', 1, ROWNUM)
          FROM   dual  
          CONNECT BY LEVEL <= LENGTH (regexp_replace (emp_nm, '[^,]+'))  + 1);

     RETURN cnt;
END;

score 2 · Answer 2 · answered Mar 05 '14 at 06:25

You can try dynamic sql:

CREATE OR REPLACE FUNCTION count_emp(emp_nm IN varchar) 
RETURN number
IS
  cnt      NUMBER;
BEGIN
  Execute immediate 'SELECT count(*) FROM emp WHERE emp_name NOT IN(' || emp_nm || ')'  returning into cnt;
  RETURN cnt;
END;

score 2 · Answer 3 · answered Mar 05 '14 at 18:01

You can also use MEMBER OF. Here is a snippet. Hope this helps!

-- Create a type in SQL    
CREATE OR REPLACE TYPE t_emp_name AS TABLE OF VARCHAR2 (10);

-- use MEMBER OF to use your list as IN parameter    
CREATE OR REPLACE FUNCTION count_emp (emp_nm IN t_emp_name)
   RETURN NUMBER
IS
   cnt   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO cnt
     FROM emp
    WHERE emp_name NOT MEMBER OF (emp_nm);

   RETURN cnt;
END;

-- Assign values to the list, you can do it dynamically as well. Call the function
DECLARE
   l_emp_name_list   t_emp_name;
   lv_count          NUMBER;
BEGIN
   l_emp_name_list := t_emp_name ('King', 'Steve'); --add more names as needed
   lv_count := count_emp (l_emp_name_list);
END;

This is just what I needed. Thanks for sharing! – Henok T Sep 19 '17 at 18:08 — Henok T, Sep 19 '17 at 18:08
glad to see that it was helpful! – Ram Dwivedi Sep 25 '17 at 05:42 — Ram Dwivedi, Sep 25 '17 at 05:42

pl/sql function- pass multiple varchar2 values dynamically for SELECT ... NOT IN()

3 Answers3