Re: pl/sql dynamic where

You could use Dynamic SQL (start reading up on DBMS_SQL) or something like

--

IF criteria1 IS NULL
THEN
   criteria1 := '1';
END IF;

IF criteria2 IS NULL
THEN
   criteria2 := '1';
END IF;
...

SELECT *
FROM mytable
WHERE field1 LIKE NVL(criteria1, '%')
AND field2 LIKE NVL(criteria2, '%');

However if performance is an issue and you're not likely to have more than
5 clauses, write them seperately, giving Oracle a chance to cache the
parsed versions of them.

PS: Out of interest, how do you do such a thing in Pro*C?