dynamic where clause

[Guest_imported]

i am trying to write a program that will accept a dynamic number of user inputs for one parameter. for example
if a user inputs 'smith, jones, williams'

my program will break this string up and query the database for all of the entries relating to these 3 employees.

does anyone know how id create a cursor thatd accpet a dynamic number of inputs for a parameter like this. is there a way to do a loop and have something like

'AND emp_name = x'

keep getting added on to the end of the where clause?

while x keeps changing as i loop though and parse the string.

can anyone help a brutha out?

Thanks.

lance

 

[Kenrae]

You should construct

 AND (emp_name = x OR emp_name=y OR ...)

, and then execute dynamically the constructed string.
Instead of using the

 execute immediate

statement, you'll need a ref cursor, and the next syntax:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 1000;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
   ...
END;

Fetching is something like that:

LOOP
   FETCH emp_cv INTO my_ename, my_sal;  -- fetch next row
   EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
   -- process row
END LOOP;
CLOSE emp_cv;

Hope this helps