Home » SQL & PL/SQL » SQL & PL/SQL » Compiling all Views Using a Job (Oracle 9i)
Compiling all Views Using a Job [message #313226] Fri, 11 April 2008 02:38 Go to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

Hi all,
I want to compile all the views in my database every day. I can create a job and i can do this. but i dont want to hard code. means i dont want to use my views name directly. it has to take dynamically and compile all invalid views. so that what can i do? Please Inform...

Regards,
Kalyan.
Re: Compiling all Views Using a Job [message #313228 is a reply to message #313226] Fri, 11 April 2008 02:42 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Have a look at the data dictionary view: DBA_OBJECTS
I guess you'll find all the information you want & need in there.
Re: Compiling all Views Using a Job [message #313237 is a reply to message #313228] Fri, 11 April 2008 03:45 Go to previous messageGo to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

hi,
i m asking that automatic compilation..
i can compile using this query.

alter views view_name compile view.

but there are lot of views. i cannot use each view like that.

so that i want to create a job so that i can compile all the invalid views.

how to do that?

Thanks,
Re: Compiling all Views Using a Job [message #313239 is a reply to message #313237] Fri, 11 April 2008 03:52 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Some tools have some built-in functionality for this already (for sure PL/SQL Developer does, not sure about Toad and SQL Developer).

The other option would be to build up the statement needed (using this objects view) and use execute immediate.
Re: Compiling all Views Using a Job [message #313240 is a reply to message #313237] Fri, 11 April 2008 03:52 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You could have a look at DBMS_UTILITY.COMPILE_SCHEMA

But that's going to do more than you actually want.

Guess I have to elaborate a bit more on the DBA_OBJECTS-hint Smile

- Query all invalid vieww: DBA_OBJECTS OBJECT_TYPE='VIEW' and STATUS='INVALID'
- For each retrieved record perform the 'ALTER VIEW ... COMPILE'

I hope this hint helps you to find it on your own, good luck
Re: Compiling all Views Using a Job [message #313242 is a reply to message #313237] Fri, 11 April 2008 03:53 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Well, if you look at the DBA_OBJECT view as Marc suggested, you will see that all of those views are listed. You would need to loop through the list of views and dynamically build and execute the recompile statement. My question, however, would be; why do you only want to recompile your views, what about your subprograms too?
Re: Compiling all Views Using a Job [message #313243 is a reply to message #313242] Fri, 11 April 2008 03:56 Go to previous messageGo to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

ya i want to use that for my sub programs also...
thank you. i will try this.
Re: Compiling all Views Using a Job [message #313245 is a reply to message #313243] Fri, 11 April 2008 03:58 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
mailtokkalyan wrote on Fri, 11 April 2008 10:56
ya i want to use that for my sub programs also...
thank you. i will try this.



Please post your solution if it's finished & tested, so others can benefit as well.
Re: Compiling all Views Using a Job [message #313304 is a reply to message #313245] Fri, 11 April 2008 06:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have to ask - why would you want to do this?

If the view source contains errors, it will still be invalid after this procedure.
If the view source doesn't contain errors, but is invalid do to a change in another object, then it will be automatically recompiled when it is needed.

So - Why?
Re: Compiling all Views Using a Job [message #313309 is a reply to message #313304] Fri, 11 April 2008 06:27 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
If the view source doesn't contain errors, but is invalid do to a change in another object, then it will be automatically recompiled when it is needed.

So - Why?

I would prefer to find out that a view/ procedure/ package et al was going to fail before my (l)users executed it. At least it would give me the opportunity to fix the problems if there were any before I had much wailing and gnashing of teeth and beating of breast from the (l)users. My main concern here would be why on earth does this have to be recompiled on a daily basis? Are table definitions changing every day in a production db? Say it ain't so, Joe Smile
Re: Compiling all Views Using a Job [message #313319 is a reply to message #313309] Fri, 11 April 2008 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're getting errors in your views, then presumably you are putting things out into a production system without testing them, and you've got more problems than this will fix.

Either way, I'd use All_Errors, or a query on All_Objects looking for STATUS=INVALID
Re: Compiling all Views Using a Job [message #313331 is a reply to message #313309] Fri, 11 April 2008 07:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from the mentioned issue about changing things on a regular (even daily) basis on a production system, I would end my installation with a recompile of invalid objects, instead of start my day with it..
Re: Compiling all Views Using a Job [message #313337 is a reply to message #313319] Fri, 11 April 2008 07:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
hen presumably you are putting things out into a production system without testing them, and you've got more problems than this will fix.
That's what I was trying to imply (obviously not very well )
Re: Compiling all Views Using a Job [message #313339 is a reply to message #313337] Fri, 11 April 2008 07:21 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Interestingly enough, I have a job like that on all systems I am DBA on. To keep a watchful eye on the other developers. Wink

I did externally it with PERL, though, since I didn't want to create jobs or procedures on systems I don't do development for.
Previous Topic: Calling function in a procedure
Next Topic: Need help with my code to have expected output.
Goto Forum:
  


Current Time: Sat Dec 10 22:42:41 CST 2016

Total time taken to generate the page: 0.08112 seconds