Solved

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

Posted on 2011-03-17
4
684 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
scheduler notification 9 80
T-SQL: Only Wanting One Record 8 61
Check for any ASM patches and install them. 1 25
Using a SUBQUERY for the set variable 10 29
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 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