Solved

How to avoid ORA-04068, but still update materialized view that is used in a procedure?

Posted on 2011-03-17
4
677 Views
Last Modified: 2013-12-07
I need to update/refresh a materialized view that is the basis for records processed by a procedure. Any way to do this in PL/SQL?
0
Comment
Question by:FredTbay
  • 2
  • 2
4 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 35161048
You can't avoid it,  if you modify a plsql object, either explicity or implicitly (by modifying objects it's dependent on) then that object will need to be recompiled.
Upgrading to 11g will help because it does finer grained dependencies but if procedure is dependent on an object then you will encounter 4068's if you try to use it across ddl changes.

A simple refresh of data shouldn't cause any invalidations though
0
 

Accepted Solution

by:
FredTbay earned 0 total points
ID: 35166193
I guess that I was hoping that there was a way to do this in one process. I have an external VB program that calls on the procedure, I'll just have to have it call a separate procedure that makes the changes to the materialized view (I actually change a filter in the materialized view (drop/create), not just refresh), then call the procedure that uses the changed view.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35169799
if you don't have further info request, I suggest deleting the question rather than closing it
0
 

Author Closing Comment

by:FredTbay
ID: 35196662
It's not very elegant, but it'll work.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question