Assume that the structure of the table on which a view is
based is modified. When you describe the view by using iSQL*Plus DESCRIBE
command, you get an error message that states that the object is invalid to
describe. This is because the command is not a SQL command and, at this stage,
the view is invalid because the structure of its base table is changed. If you
query the view now, the view is recompiled automatically and you can see the
result if it is successfully recompiled.
Note:-
In the case of local dependencies, the objects are on the same node in the same
database. The Oracle server automatically manages all local dependencies, using
the database’s internal “depends-on” table. When a referenced object is
modified, the dependent objects are invalidated. The next time an invalidated
object is called, the Oracle server automatically recompiles it.
For better understanding go through an example below:
SQL> create table testtab(col1 number,col2 number);
Table created.
SQL> insert into testtab values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> create view testtab_view as select * from
testtab;
View created.
SQL> select * from testtab_view;
COL1 COL2
--------- ---------
1 1
SQL> select object_name,status
from
user_objects where object_name='TESTTAB_VIEW';
OBJECT_NAME
STATUS
--------------- -----------
TESTTAB_VIEW
VALID
SQL> ALTER TABLE TESTTAB_VIEW ADD (COL3 NUMBER);
ALTER TABLE TESTTAB_VIEW ADD (COL3 NUMBER)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ALTER TABLE TESTTAB ADD (COL3 NUMBER);
Table altered.
SQL> select object_name,status
from user_objects
where
object_name='TESTTAB_VIEW';
OBJECT_NAME
STATUS
--------------- -----------
TESTTAB_VIEW
INVALID
SQL> DESC TESTTAB_VIEW;
ERROR:
ORA-24372: invalid object for describe
SQL> SELECT * FROM TESTTAB_VIEW;
COL1 COL2
--------- ---------
1 1
SQL> select object_name,status
from
user_objects where object_name='TESTTAB_VIEW';
OBJECT_NAME
STATUS
--------------- -----------
TESTTAB_VIEW
VALID
Note:- Your view definition is select * from testtab; Even though it
doesn’t display col3 why because this column was not there while creation of a
view, so view definition doesn’t contain that column in his definition.
Note: The data types of the columns in the base table do not matter at the
time of compiling the view. That means in the above example even
columns
C1 is of VARCHAR2 type in table T1 still the view will be made valid.
Because
Oracle precisely looks for columns C1 and C2 in table T1and not for any
specific data type. For better understanding Check below example:
SQL> create table test_tab (col1 number,col2 number);
Table created.
SQL> create view test_view as select * from test_tab;
View created.
SQL> desc test_view;
Name
Null? Type
-----------------------------------------------------
-------- ---------------
COL1
NUMBER
COL2
NUMBER
SQL> drop table test_tab;
Table dropped.
SQL> create table test_tab (col1 number,col2 varchar2(100),col3
number);
Table created.
SQL> desc test_view;
ERROR:
ORA-24372: invalid object for describe
SQL> select * from test_view;
no rows selected
SQL> desc test_view;
Name
Null? Type
-----------------------------------------------------
-------- ---------------
COL1
NUMBER
COL2 VARCHAR2(100)
