Home » SQL & PL/SQL » SQL & PL/SQL » Recompile View to show the newly added columns
Recompile View to show the newly added columns [message #295038] Mon, 21 January 2008 03:26 Go to next message
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 #295040 is a reply to message #295038] Mon, 21 January 2008 03:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have to create the view using "create or replace view" again.

By
Vamsi
Re: Recompile View to show the newly added columns [message #295048 is a reply to message #295038] Mon, 21 January 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should never have a program that does not list the columns.

In addition, depending on your version, you also have to regrant all privileges you granted.

Regards
Michel

[Updated on: Mon, 21 January 2008 03:51]

Report message to a moderator

Re: Recompile View to show the newly added columns [message #295055 is a reply to message #295048] Mon, 21 January 2008 03:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is perfectly valid for a view-definition to contain a select * without naming the individual columns.

[Edit: I should add: in my opinion]

[Updated on: Mon, 21 January 2008 04:00]

Report message to a moderator

Re: Recompile View to show the newly added columns [message #295059 is a reply to message #295055] Mon, 21 January 2008 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with you Frank, assuming that you know that "select *" in a view definition means "select all columns of the table at the time of this definition".
This is not the case of most developers that think that means "select all columns of the table at the time of execution".

Regards
Michel
Re: Recompile View to show the newly added columns [message #295065 is a reply to message #295038] Mon, 21 January 2008 04:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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...
Re: Recompile View to show the newly added columns [message #295118 is a reply to message #295114] Mon, 21 January 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is none.

Regards
Michel

[Updated on: Mon, 21 January 2008 06:38]

Report message to a moderator

Re: Recompile View to show the newly added columns [message #295135 is a reply to message #295038] Mon, 21 January 2008 07:37 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Remember a view is simply a stored query, would you like your queries to alter them selves, I sure wouldn't! If you want the emp_vu to always look like emp then issue the following command.


create synonym emp_vu as emp;

Re: Recompile View to show the newly added columns [message #295263 is a reply to message #295135] Mon, 21 January 2008 22:41 Go to previous message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thank you all for your suggestions...
Previous Topic: KILL command
Next Topic: please solve this Query
Goto Forum:
  


Current Time: Thu Dec 08 02:06:22 CST 2016

Total time taken to generate the page: 0.19417 seconds