?
Solved

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

Posted on 2011-03-17
4
Medium Priority
?
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

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 74

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

Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

719 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