-
Creating a cursor with dynamic where clause
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;
-
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,
-
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''
-
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.
Last edited by jkoopmann; 04-03-2003 at 12:34 PM.
-
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.
-
good,
then you have the columns for the where clause
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|