create or replace  procedure tmp_insert_emp(
vempno varchar2 default null,
vename varchar2 default null,
vdname varchar2 default null)
as 
v_query varchar2(32767);
v_where varchar2(32767);
BEGIN
v_where:=' WHERE emp.deptno=dept.deptno';
IF TRIM(vempno) IS NOT NULL THEN 
V_WHERE  :=V_WHERE||' AND EMPNO='''||vempno||'''';
end if;
IF TRIM(vename) IS NOT NULL THEN 
V_WHERE  :=V_WHERE||' AND ename='''||vename||'''';
end if;
IF TRIM(vdname) IS NOT NULL THEN 
V_WHERE  :=V_WHERE||' AND dname='''||vdname||'''';
end if;
v_query :='insert into tmp_emp  select empno,ename,job, dname from emp ,dept';
execute immediate v_query||V_WHERE;
end;

Hi My procedure looks like above
when i run the procedure with a new set of parameters no rows are getting inserted
but for the second time with same set of parameters correct number of rows getting inserted
if i change new set of parameters above scenario is happening again
can you tell what is causing this kind of behavior in my procedure

exec exec tmp_insert_emp(null,null,'ACCOUNTING'); -- 0 rows inserted
exec tmp_insert_emp(null,null,'ACCOUNTING'); -- 3 rows inserted
Note:- i am unable to reproduce same scenario with above procedure

V_WHERE  :=V_WHERE||' AND ename='''||vename||'''';