create table emp(eno number,ename varchar2(10),dno number,sal number(9,2));

insert into emp values(1,'a',10,500);
/
insert into emp values(2,'b',20,700);
/
insert into emp values(3,'c',20,800);
/
insert into emp values(4,'d',20,900);
/

commit;

create or replace package type_def is
TYPE t_RefCurTyp IS REF CURSOR RETURN Emp%rowtype;
end type_def;
/

create or replace procedure 

Get_UserList( 

n_eno in emp.eno%type,
n_ename in emp.ename%type,
n_sal in emp.sal%type,
n_dno in emp.deptno%type,
o_RowCount OUT NUMBER,
o_RefCurUserList 
OUT Type_Def.t_RefCurTyp ) 

is

l_Prg_Name VARCHAR2(30) := 'Get_UserList'; 


l_SqlStmt  VARCHAR2(32767); 
l_Whr_Clause VARCHAR2(1000); 


BEGIN
 


-- Make sure essential parameters are passed 

IF n_eno IS NULL OR n_ename IS NULL THEN 

RAISE_APPLICATION_ERROR(-20001, ' n_eno or n_ename is NULL in ' ||l_Prg_Name); 

END IF; 

-- 
l_Whr_Clause := ' where eno ='||n_eno||' and ename='||n_ename||' ; 



IF n_sal IS NOT NULL THEN 
l_Whr_Clause := l_Whr_Clause||' AND SAL =n_sal; 
END IF; 


IF n_dno IS NOT NULL THEN 
l_Whr_Clause := l_Whr_Clause||' AND DNO = n_dno; 
END IF; 


l_SqlStmt :='SELECT count(1) FROM Emp '; 


l_SqlStmt :=l_SqlStmt||l_Whr_Clause; 



EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount ; 


l_SqlStmt := 'SELECT *  FROM Emp' ; 
l_SqlStmt :=l_SqlStmt||l_Whr_Clause; 



open o_RefCurUserList for l_SqlStmt; 


EXCEPTION


WHEN OTHERS THEN 

RAISE_APPLICATION_ERROR(-20000, 'DB Error: ' ||SQLERRM||' IN '||l_Prg_Name); 


END;

/

LINE/COL ERROR
-------- -----------------------------------------------------------------
41/5     PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>
         delete exists prior <a single-quoted SQL string>

l_Whr_Clause := ' where eno ='||n_eno||' and ename='||n_ename||' ; 
...
l_Whr_Clause := l_Whr_Clause||' AND SAL =n_sal;
...
l_Whr_Clause := l_Whr_Clause||' AND DNO = n_dno;