File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes What I Achieve by recompiling a view ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "What I Achieve by recompiling a view ?" Watch "What I Achieve by recompiling a view ?" New topic

What I Achieve by recompiling a view ?

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

I have two questions.

First One is
I created a table as

create table pg(id number,name varchar2(30));

then I created a view as

create view pgview as
select id,name
from pg;

then I changed the datatype of column id as

alter table pg
modify id varchar2(10);

Now I queried the view User_objects and it shows the status of pgview as INVALID

then I inserted a row in the table pg and again queried the view User_objects now it shows the status as VALID.

My question is how status changes from INVALID to VALID .

My second quastion is

Suppose I rename a column or drop a column of a table , and that column was included in the view.
Now That view have no meaning ,Even If I try to recompile the view it will say "View altered with compilation errors"
I have to create the view a new altogether.

Now suppose rather than renaming or dropping the column I just change the datatype of the column ,and that column was included in the view.
Now Even If I don`t recompile the view it works ok.

So what I achieve from the statement " alter view view_name compile "


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

1) Invalid objects in Oracle are automatically recompiled when used. Consider
2) You'll use alter view view_name compile to compile it explicitly, without waiting for it to be used. Typical use would be in schema upgrade scripts, for example - you don't want to wait for the view to be used by an application or user to be compiled, you want to catch any possible problems early.

Edit: I'd suggest reading Oracle's Database Concepts Guide, lots of questions you've asked lately are well covered there.
I agree. Here's the link:
subject: What I Achieve by recompiling a view ?
It's not a secret anymore!