| Dynamic resultset [message #38271] |
Fri, 05 April 2002 15:34  |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
I am trying to develop a procedure which should return a dynamic resultset ,
A)
Procedure p1 (p_where varchar2)
..
r1 resultset; -- weak (ref)cursor
begin
qry:= 'Select * from tablex where '||p_where;
open r1 for qry ;
..
end;
/
This obviously will fail becoz open cursor is not flexible to handle to dynamic queries..
Also I tried this..
B)
Procedure p1 (p_empno varchar2)
begin
qry:= 'Select empname from emp where empno=:a';
Execute immediate qry into res using p_empno;
...
end;
The above works fine .but the same logic when applied like this fails
Procedure p1 (p_where varchar2)
..
begin
qry:= 'Select empname from emp where =:where';
Execute immediate qry into res using p_where;
...
end;
How can I achieve this?
Appreciate if someone can enlighten me!
Thanx
oraboy
(venkat_jobmails@yahoo.com)
|
|
|
|
| Re: Dynamic resultset [message #38274 is a reply to message #38271] |
Sat, 06 April 2002 04:45  |
Bhagwan Singh
Messages: 23
Registered: December 2001
|
Junior Member |
|
|
hi Buddy,
u can do the same with your option A itself by applying dynamicity in cursor.Here is a small snippet.u just have to modify ur line accordingly.it will definitely work.Iam assuming that u have a cursor type(weak) declaration already there on the specification and r1 is an object of the type.
.............................................
OPEN r1 FOR 'SELECT * FROM emp WHERE
empno ||'= :a'
USING p_empno;
-- I think u missed the obove line.
LOOP
-- Do your processing here
EXIT WHEN r1%NOTFOUND;
END LOOP;
CLOSE r1;
...........................................
or rather correct me if Iam wrong or have not understood ur problem :-)
Bhaggs
|
|
|
|
| Re: Dynamic resultset [message #38280 is a reply to message #38271] |
Sun, 07 April 2002 17:51 |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
thanks for ur reply
I know it works for dynamic values wherein u have the same query condition( where empno=:a in your example)
but I wanna change the whole where clause dynamically
Will it work?
I checked it up..and using works only to substitute the value dynamically
hope you r clear with my question now
|
|
|
|
| Re: Dynamic resultset [message #38294 is a reply to message #38280] |
Mon, 08 April 2002 09:30 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
If the number of variables is constant as you vary the WHERE clause, you can still use the USING clause, but if you are changing the number of variables (one version has three columns to check, another has two columns, etc.), then you will need to hardcode the values in the string.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
