Re: Building a Dynamic Where clause in an Oracle Function

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 Received on Fri Jun 04 2004 - 18:59:59 CDT