| view recompilation [message #538544] |
Fri, 06 January 2012 15:31  |
 |
justlearner
Messages: 9
Registered: January 2012
|
Junior Member |
|
|
Hi All,
Oracle can automatically recompile view when a schema object is redefined,but it isn't always right.For example:
SQL> CREATE TABLE student(
2 s_id VARCHAR2(10) CONSTRAINT pk_student PRIMARY KEY,
3 s_name VARCHAR2(20) NOT NULL,
4 s_gender VARCHAR2(10) CONSTRAINT nn_s_gender NOT NULL
5 );
SQL> CREATE VIEW s_view AS SELECT * FROM student;
SQL> ALTER TABLE student MODIFY s_name VARCHAR2(50);
SQL> SELECT * FROM s_view; --right
SQL> ALTER TABLE student DROP COLUMN s_name;
SQL> SELECT * FROM s_view; --wrong
SELECT * FROM s_view
*
ORA-04063: view "TEST.S_VIEW" has errors
SQL> CREATE OR REPLACE VIEW s_view AS SELECT * FROM student;
SQL> SELECT * FROM s_view;
I must use CREATE OR REPLACE statement to recreate view,what i want to know is when Oracle can automatically recompile view,and when i need recreate view using CREATE OR REPLACE statement?
Thanks.
|
|
|
|
|
|
| Re: view recompilation [message #538546 is a reply to message #538544] |
Fri, 06 January 2012 16:04  |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If you look at the source code for the view, and you can usually work out what change will mean it can't recompile:orcl> create view v1 as select * from dept;
View created.
orcl> select text from user_views where view_name='V1';
TEXT
-------------------------------------------------------------------
select "DEPTNO","DNAME","LOC" from dept
orcl>
Do you see? Add a column to the table, no problem; drop a column, and it isn't going to work any more.
|
|
|
|
| Re: view recompilation [message #538557 is a reply to message #538544] |
Sat, 07 January 2012 00:59 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
To say in another way what has been said; when you use "*" in view defintion, Oracle replaces it by te list of the columns at the time you create the view.
So, there is no more "*" in the stored view definition, as John showed it.
Regards
Michel
[Updated on: Sat, 07 January 2012 00:59] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
