Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] :

PL/SQL function code can include a request to cache its results. Upon invocation of this function, the system checks the cache. If the cache contains the result from a previous function call with the same parameter values, then the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, then the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker

My doubt:

After a PL/SQL function is executed, its result set is stored in cache. Then a DDL statement changes the table/index/anything that the function uses...

If I run the same PL/SQL function now, will I get the same result or updated result?

share|improve this question
up vote 4 down vote accepted

cached results are invalidated and regenerated when transactions occur against the underlying data

I am fairly confident the same would be true for DDL changes but did you mean DML? The long and short is that Oracle is not going to allow an inconsistent result.

share|improve this answer
+1 The PL/SQL Language Reference says it this way If changes to any of these data sources are committed, the cached result becomes invalid and must be recomputed. Since DDL does an implicit commit, this includes DDL. – Leigh Riffel Aug 31 '11 at 14:41
Thank you.This is what i need.. – vettipayyan Sep 1 '11 at 4:15

"There are only two hard problems in Computer Science: cache invalidation and naming things." -- Phil Karlton

Part of Oracle's built-in functionality is Data Change Notification. This mechanism is generally used for cache invalidation in all tiers, for example, if you cache a query result on the client side, DCN ensures that you are always routed to the closest but most recent copy.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.