Re: Dynamic query in stored procedure

From: Javier Montero <jmontero19_at_gmail.com>
Date: Tue, 4 Jun 2013 07:10:07 -0700 (PDT)
Message-ID: <790ecdc9-763d-46af-98e8-cbd18c1d9235_at_googlegroups.com>

On Tuesday, October 14, 2008 7:27:53 AM UTC-6, DA Morgan wrote:
> 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

Thank U Daniel Received on Tue Jun 04 2013 - 16:10:07 CEST