Team,
The oracle version we are using is : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit.
let us use the basic scott user's emp table and the select statement goes like this:
select empno,ename,sal,comm,hiredate from scott.emp
where trunc(hiredate) between to_date('01-jan-1985') and to_date(01-dec-1999);
In other words, lets reconvert this query into parameter based with 2 new extra columns like below:
select empno,ename,sal,comm,hiredate , to_date(p_st_date,'mm/dd/yyyy') ,to_date(p_enddate,'mm/dd/yyyy') from scott.emp
where trunc(hiredate) between to_date(p_st_date,'mm/dd/yyyy') and to_date(p_enddate,'mm/dd/yyyy');
My requriement goes like this:
I want to convert the latest select stmt which is given below with parameters as p_st_date and p_enddate into a parameterized view.
select empno,ename,sal,comm,hiredate , to_date(p_st_date,'mm/dd/yyyy') ,to_date(p_enddate,'mm/dd/yyyy') from scott.emp
where trunc(hiredate) between to_date(p_st_date,'mm/dd/yyyy') and to_date(p_enddate,'mm/dd/yyyy');
Please suggest me on creating the parameterized view and seletct stmt for how to view the output of the parameterized view.
Thank You