Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question, bhogeswara.
Asked: February 15, 2018 - 5:41 pm UTC
Last updated: February 16, 2018 - 11:07 am UTC
Version: 12.1
Viewed 1000+ times
create or replace procedure fetch_ids(ename in varchar2,hiredate in date) as begin select * from emp where empname=ename and join_date=hiredate ; end;
if ename is null then select * from emp; else select * from emp where empname=ename and join_date=hiredate ; end if;
select * from emp where ( ename is not null and join_date=hiredate and empname=ename ) or ( ename is null) ;
execute immediate 'select * from emp ' || case when ename is not null then 'where empname=:ename and join_date=:hiredate' else 'where :ename is null and nvl(:hiredate, sysdate) is not null' end using ename, hiredate
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library