How to pass a where-clause to a procedure
I have to develop a stored procedure where a part of the where-clause of the select of a cursor depends on the input parameter.
My procedure should be something like this:
procedure myproc (WHERECLAUSE in varchar2(100)) is
...
begin
...
declare
cursor mycur is
select *
from mytable
where a = b
and c = d
...
and <WHERECLAUSE>
...
;
...
begin
open mycur;
loop
fetch ...
...
...
...
...
end;
...
end myproc;
In order to join the parameter WHERECLAUSE to the select of the cursor I've thought of using the dynamyc sql.
My problem is if I have to use a dynamyc sql, should I put the whole of my code (declaration of cursor and the following statements) in a uinque variable to be executed by the dynamyc sql?
What do you adivise me to use?
Thanks in advance!