Re: Building a Dynamic Where clause in an Oracle Function

anacedent <anacedent_at_hotmail.com> wrote in message news:<328wc.39213$oi5.14145_at_fed1read07>...
> Brett Baloun wrote:
> > Hello,
> >
> > I am trying to build the Where clause w/in my function to complete the
> > SQL statement. I want to build the clause based on parameters passed
> > to the function and some other logic. The where_clause example in
> > this function is just a basic example of what I ultimately want to do.
> >
> > I get the following error when I create the function:
> > 13/2 PL/SQL: SQL Statement ignored
> > 16/9 PLS-00382: expression is of wrong type
> >
> > CREATE OR REPLACE function GET_COUNT10(begin_dt IN VARCHAR2, end_dt IN
> > VARCHAR2, status IN VARCHAR2)
> >
> > RETURN NUMBER
> >
> > IS
> > total_count NUMBER;
> > where_clause varchar2(100):= NULL;
> > BEGIN
> > IF begin_dt != null AND end_dt != null THEN
> > where_clause := 'status = COMPLETE';
> > END IF;
> >
> > select count(*)
> > INTO total_count
> > from GENERAL_ROSTER
> > where where_clause;
> >
> > RETURN(total_count);
> > END;
> > /
> >
> > Thank you in advance for you assistance.
> > Brett
>
> To do what you want to do, you'll need to use EXECUTE IMMEDIATE.
> I suggest that you construct the whole SQL statement into a
> single VARCHAR2 variable & 1st use DBMS_OUTPUT to display it.
> The test it using SQL*Plus before proceeding.
>
> FWIW - your "WHERE CLAUSE" is FUBAR; if my assumption is correct.
> It appears that "status" the the name of a VARCHAR2 column.
> If so, then what you need is
> where_clause := 'status = ' || CHR(39) || 'COMPLETE' || CHR(39);
>
> THT & YMMV
Hello THT & YMMV,

Thank you very much for your response - I really appreciate it.

However, I am new to PL/SQL. Could you please be a little more detailed in your response?

Yes, 'status' is a VARCHAR2 column. We would want to include multiple columns for the where clause - if passed to our function. I just wanted to include a sample of what we were doing. Ultimately, we want to build a where clause by adding additional clauses dynamically. IE where_clause := where_clause + status = 'COMPLETE' - is this possible?

Thank you in advance for your assistance.

Brett Received on Mon Jun 07 2004 - 10:06:58 CDT