CREATE TABLE emp2 AS SELECT * FROM emp;
create table new_emp2 as select * from emp where 1=2;

SET DEFINE OFF;
INSERT INTO new_emp2 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
VALUES (7800,'SAHEED','MANAGER',7839,to_date('07-03-15 14:35:24','DD-MM-RR HH24:MI:SS'),3000,NULL,60); 
commit;

create or replace PROCEDURE get_emp_details(p_start_date    IN VARCHAR2,
                                            p_end_date      IN VARCHAR2,
                                            cur_emp_details out sys_refcursor) AS
  l_history_date number := 0;
BEGIN
  IF to_date(p_start_date, 'DD-MM-YYYY') < TO_DATE('01-01-2000') AND
     to_date(p_end_date, 'DD-MM-YYYY') < TO_DATE('01-01-2000') THEN
    OPEN cur_emp_details FOR
      SELECT ROWNUM SRNO, emp2.*
        FROM emp2
       WHERE hiredate BETWEEN p_start_date AND p_end_date; -- history 
  elsif to_date(p_start_date, 'DD-MM-YYYY') >= TO_DATE('01-01-2000') AND
        to_date(p_end_date, 'DD-MM-YYYY') >= TO_DATE('01-01-2000') THEN
    OPEN cur_emp_details FOR
      SELECT ROWNUM SRNO, new_emp2.*
        FROM new_emp2
       where hiredate between p_start_date and p_end_date; --present
  ELSE
    OPEN cur_emp_details FOR
      SELECT ROWNUM SRNO, T.*
        FROM (SELECT *
                FROM new_emp2
              union all
              SELECT * from emp2) T
       where hiredate between p_start_date and p_end_date; --present--present + history 
  END IF;
END get_emp_details;


var cur_emp_details1 refcursor;
var cur_emp_details2 refcursor;
var cur_emp_details3 refcursor;
set autoprint on;
BEGIN
get_emp_details('01-JAN-1980','10-MAR-2000',:cur_emp_details1);--HISTORY 
get_emp_details('01-JAN-2000','10-MAR-2015',:cur_emp_details2);--PRESENT
get_emp_details('01-JAN-1980','10-MAR-2015',:cur_emp_details3);--HISTORY + PRESENT
end;