IF in_lname is not null then
   sql_stmt := sql_stmt || 'where gc_last_name = :1 AND gc_first_name LIKE :2 ';
    IF in_fname is not null then
       t_fname := in_fname;
    ELSE
       t_fname := '%';
    END IF;
    
END IF; 
open fm_lom_cv for sql_stmt using in_lname, in_fname;

CREATE OR REPLACE ... ( pLName VARCHAR2, pFName VARCHAR2, pZIPCode NUMBER) IS
stmt VARCHAR2(300) := 'SELECT * FROM EMP WHERE last_name LIKE :p1 AND first_name LIKE :p2 AND ZIPCODE BETWEEN :p3 AND :p4';
  tLName VARCHAR2(30);
  tFName VARCHAR2(30);
  fromZIP NUMBER;
  toZIP NUMBER;
BEGIN
   IF pLName IS NULL THEN
     tLName := '%';
   ELSE
     tLName := pLName;
   END IF;
   IF pFName IS NULL THEN
     tFName := '%';
   ELSE
     tFName := pFName;
   END IF;
   IF pZIPCode IS NULL THEN
     fromZIP := 0;
     toZIP := 99999;
   ELSE
     fromZIP := pZIPCode;
     toZIP := pZIPCode;
   END IF;
  
   exec statement ... USING tLName , tFName, fromZIP , toZIP;

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE your_proc
  2    (p_results  OUT SYS_REFCURSOR,
  3  	p_job	   IN  VARCHAR2,
  4  	p_ename    IN  VARCHAR2 DEFAULT NULL)
  5  AS
  6    v_sql_stmt      VARCHAR2 (32767);
  7  BEGIN
  8    v_sql_stmt := 'SELECT job, ename FROM emp';
  9    v_sql_stmt := v_sql_stmt || ' WHERE job = :b_job';
 10    v_sql_stmt := v_sql_stmt || ' AND ename = NVL (:b_ename, ename)';
 11    OPEN p_results FOR v_sql_stmt USING p_job, p_ename;
 12  END your_proc;
 13  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> VARIABLE g_results REFCURSOR
SCOTT@10gXE> EXEC your_proc (:g_results, 'CLERK', 'ADAMS')

PL/SQL procedure successfully completed.

SCOTT@10gXE> PRINT g_results

JOB       ENAME
--------- ----------
CLERK     ADAMS

SCOTT@10gXE> EXEC your_proc (:g_results, 'CLERK')

PL/SQL procedure successfully completed.

SCOTT@10gXE> PRINT g_results

JOB       ENAME
--------- ----------
CLERK     SMITH
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER

SCOTT@10gXE>