dynamic stored procedure

I am new to oracle. i'm trying to write a function/stored procedure that passes variables to be used in the where clause and i'm lost.

basically, here's what i have:

-----------------------------------------code
block----------------------------------

create function testFunction (param_name in USERTABLE.NAME%TYPE, param_account in ACCOUNTTABLE.ACCOUNT%TYPE, param_case in CASETABLE.CASE_NUMBER%TYPE, param_ssn in USERTABLE.SS_NO%TYPE)

return cursor
as
begin
 cursor c is

Select ACCOUNTTABLE.account, USERTABLE.NAME_U, USERTABLE.SS_NO, CASETABLE.CASE_NUMBER FROM ACCOUNTTABLE JOIN USERTABLE ON ACCOUNTTABLE.ACCOUNT = USERTABLE.ACCOUNT LEFT JOIN CASETABLE ON ACCOUNTTABLE.ACCOUNT = CASETABLE.ACCOUNT  WHERE ACCOUNTTABLE.STATUS = 'STATUS1' AND ROWNUM < 1000 and .....

return c;
end;

------------------------------END CODE
BLOCK-------------------------------

and i want to add:

if (param_account is not null) then
"and where ACCOUNTTABLE.ACCOUNT = param_account"
end if
if (param_name is not null) then
"and where USERTABLE.NAME = param_name"

end if
etc...

Please help

Thanks Received on Wed Aug 23 2006 - 14:38:02 CDT