Recompile View to show the newly added columns [message #295038] |
Mon, 21 January 2008 03:26  |
srinivasocp
Messages: 91 Registered: December 2005 Location: INDIA
|
Member |
|
|
Hi All,
I have a table EMP which has the columns empno and ename only.Now i created a view VU_EMP as select * from emp.Now when i select from the view,say select * from VU_EMP,i will be seeing empno and ename data.
Now i added a new column to the table EMP,but when i query the VU_EMP,i will be still seeing only empno and ename .
What should i do, to refresh this view VU_EMP to show the new columns that are added to the base table EMP.
Regards
Srini...
[Updated on: Mon, 21 January 2008 03:27] Report message to a moderator
|
|
|
|
|
|
|
Re: Recompile View to show the newly added columns [message #295065 is a reply to message #295038] |
Mon, 21 January 2008 04:31   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Here is the Demonstration of Michel's saying
SQL> create view emp_vw as select * from emp;
View created.
SQL> desc emp_vw
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(4)
EMPNAME VARCHAR2(5)
SUPID NUMBER(4)
DEPTNO VARCHAR2(4)
MGR NUMBER(3)
SQL> set lines 1000
SQL> select * from emp_vw
2 /
EMPID EMPNA SUPID DEPT MGR
---------- ----- ---------- ---- ----------
100 anu 1 10 301
1 sid 50 20 301
50 sagar 100 50 305
2 Rahul 1000 10 302
3 Rai 200 10 302
4 Raj 300 20 304
5 Ram 300 10 305
7 rao 200 10 301
6 CRA 400 10 306
7 200 10 301
10 rows selected.
SQL> alter table emp add sal number;
Table altered.
SQL> update emp set sal = 1000;
10 rows updated.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPID EMPNA SUPID DEPT MGR SAL
---------- ----- ---------- ---- ---------- ----------
100 anu 1 10 301 1000
1 sid 50 20 301 1000
50 sagar 100 50 305 1000
2 Rahul 1000 10 302 1000
3 Rai 200 10 302 1000
4 Raj 300 20 304 1000
5 Ram 300 10 305 1000
7 rao 200 10 301 1000
6 CRA 400 10 306 1000
7 200 10 301 1000
10 rows selected.
SQL> select * from emp_vw;
EMPID EMPNA SUPID DEPT MGR
---------- ----- ---------- ---- ----------
100 anu 1 10 301
1 sid 50 20 301
50 sagar 100 50 305
2 Rahul 1000 10 302
3 Rai 200 10 302
4 Raj 300 20 304
5 Ram 300 10 305
7 rao 200 10 301
6 CRA 400 10 306
7 200 10 301
10 rows selected.
SQL> create or replace view emp_vw as select * from emp
View created.
SQL> select * from emp_vw;
EMPID EMPNA SUPID DEPT MGR SAL
---------- ----- ---------- ---- ---------- ----------
100 anu 1 10 301 1000
1 sid 50 20 301 1000
50 sagar 100 50 305 1000
2 Rahul 1000 10 302 1000
3 Rai 200 10 302 1000
4 Raj 300 20 304 1000
5 Ram 300 10 305 1000
7 rao 200 10 301 1000
6 CRA 400 10 306 1000
7 200 10 301 1000
10 rows selected.
[Updated on: Mon, 21 January 2008 04:32] Report message to a moderator
|
|
|
Re: Recompile View to show the newly added columns [message #295114 is a reply to message #295065] |
Mon, 21 January 2008 06:32   |
srinivasocp
Messages: 91 Registered: December 2005 Location: INDIA
|
Member |
|
|
Thank you guys.....I know that by using create or replace view we can recreate a views definiton....but i was just wondering whether we have any other way of refreshing the view automatically,once a new column is added to the base table...
Regards
Srini...
|
|
|
|
|
|