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