Coding Dynamic Where Clauses

[johne417]

Um, yeah, I'm new so I'm not sure if this is the right place. If it's not, please let me know. And if this is a dumb question, please be gentle

The following code in a stored procedure in oracle (simplified version):

stored procedure paramaters include a couple of variables passed in: i_providerid, and i_extrawhere.

SELECT
b.bookingid
[many other things]
FROM
booking b
,groups g
[and so on]
WHERE
b.providerid = i_providerid
AND b.groupid = g.groupid(+)
"i_extrawhere"
ORDER BY b.date

Obviously, the "i_extrawhere" is invalid syntax here - That's my question, what do I do with it. The variable i_extrawhere contains the value of potentially several additional AND clauses to go in there (e.g. i_extrawhere = "AND b.admin = 1 AND b.phone LIKE '123%' &quot.

Syntactically, how do you do this?

Thanks in advance for any help.

 

[sem]

You may use dynamic sql: DBMS_SQL package or REF CURSOR.

declare
type c is ref cursor;
v c;
r emp%rowtype;
begin
open v for 'SELECT * from emp where name like ''A%'' ' || i_extrawhere||' ORDER BY b.date';
fetch v into r;
close v;
end;


Regards, Dima

http://seminihin.narod.ru