# Database Discussions > Oracle >  Creating a cursor with dynamic where clause

## Gennady1

Is it possible to create a cursor without knowing the contents of the where clause until runtime?

What I'm trying to do is similar to

cursor myCursor IS 
SELECT * FROM emp 
WHERE || p_whereclause;

----------


## jkoopmann

gennady1,
are you talking pl/sql?
if so yes  :Smilie: 
here is a small snippit of how i have prebuilt a sql statement (with where clause) and executed it.

sql_stmt0 := 'SELECT decode(r.column_name,null,''NULL'',r.column_name),  s.column_name '||
               '  FROM doc_columns_view r, doc_columns_view s '||
	       ' WHERE r.owner                (+) = :1'||
	       '   AND s.owner                    = :2'||
	       '   AND r.table_name           (+) = :3'||
	       '   AND substr(s.table_name,4)     = substr(:4,4)'||
	       '   AND r.column_name          (+) = s.column_name ';

  OPEN cur1 FOR sql_stmt0 USING rowner, sowner, rtable, stable;
  LOOP
    FETCH cur1 INTO rcolumn,scolumn;
    EXIT WHEN cur1%NOTFOUND;
    ...<code here>...
  END LOOP;
  CLOSE cur1;

hope this helps,

----------


## Gennady1

Thanks for your reply James.

But in your example you know the columns that a being passed to the procedure.

I need to have a generic where clause, meaning I don't know the number of columns or which columns will be passed to the procedure.
In other words, a user will be passing a string that can have different columns and values.
Here are the examples of something a user can pass.

'commission is null'
'empno = 4 and deptno = 10'
'empname = 'SMITH''

----------


## jkoopmann

if you don't know the columns that are valid for a query, you are really talking about free form query from the user. 

since you are not controling the query and are asking the user to build a where clause on "anything", you will, more than likely, get enormous amounts of errors.

can you not control anything, if not, sounds like you give them access to sql/plus.

the previous example was not intended to show you how to supply columns to the where clause, but just an example of putting in a where clause that could be dynamic and executing it through a cursor.

----------


## Gennady1

Actually, the user will use a front end app, which is written in VB.  By selecting different drop down boxes, VB program would construct the where clause.

----------


## jkoopmann

good,
then you have the columns for the where clause  :Smilie:

----------

