Re: Dynamic query in stored procedure

Tim Mickelson wrote:
> Hi
>
> This question is about stored procedures. I know i can make a query
> such as:
>
> query := SELECT A, B, C FROM USERS WHERE NAME = :a AND SURNAME = :b
> OPEN my_cursor FOR query USING var_a, var_b
>
> But my question is, if I don't know how many parameters I have in the
> where part, how do I do? I do not want to concatenate part by part.
>
> E.g.
>
> query := SELECT A, B, C FROM USERS WHERE 1 > 0
> IF(var_a IS NOT NULL) THEN
> query := query || ' AND NAME = :name'
> END IF;
>
> ....
>
> How do I open this query, I don't know how many params I have?

Looking at your table and column names I am led to suggest you learn the following query:

SELECT keyword
FROM gv$reserved_words;

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org