What i dont undestand is what exactly you need. IF you want to know how to execute dynamic sql, you use the execute immediate sentence:
text := 'select * from table';
execute immediate text;
ps: be careful when building dynamic queries to protect against sql injection.
If it's just to filter and to avoid SQL injections, you can do this :
CREATE PROCEDURE TEST (p_filtre VARCHAR2 DEFAULT NULL) AS BEGIN SELECT * FROM TABLE_YOU_WANT WHERE p_filter IS NULL OR COLUMN_YOU_WANT like 'p_filtre'; END TEST;