I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins.
Problem is that usually I would like to just look for one table's data, not everything even though data is fetched through a view. What would be the best way of implementing this with Oracle?
In practice, something like this I would want to do
CREATE VIEW emp_emp( e1_ename, e2_empno, e2_deptno ) SELECT e1.ename, e2.empno, e2.deptno FROM emp e1, emp_add e2 WHERE e1.empno = e2.empno;
I would want to be able to say
SELECT e2.* FROM emp_emp;
To my current knowledge I would have to write all fields related to table e2 by hand.
I have been thinking a solution where I could actually use USER_TAB_COLS table to dynamically create a list of columns i would want to select. So there could be a wrapper which would create
SELECT E2_EMPNO, E2_DEPTNO FROM emp_emp; automatically if i would call for instance a function with parameter list of all tables I want to fetch.