Building a Dynamic Where clause in an Oracle Function

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);

Thank you in advance for you assistance. Brett Received on Fri Jun 04 2004 - 15:34:09 CDT