Re: Fetch table names from query

From: DA Morgan <>
Date: Thu, 22 May 2008 11:48:22 -0700
Message-ID: <>

Dan Blum wrote:
> DA Morgan <> wrote:

>> ?lvaro G. Vicario wrote:
>>> Is there any simple way to extract the table names of a given SELECT 
>>> query without actually running it?
>>> I've implemented a very simple results cache in my PHP application but 
>>> I'd like to improve it. And one of the key points is finding out what 
>>> tables a query reads from so I can handle obsolete data. Writing a 
>>> reliable SQL parser in PHP looks like a hard task and obtaining a full 
>>> explain plan from the Oracle server looks like an overkill.
>>> Any ideas?
>>> Thank you in advance,

>> This is horrifying. Obsolete data is defined by an SLA with the customer
>> not by whether it is accessed.

>> For example I have organizations here in the US that are, by law,
>> required to keep data online for 7 years. It hopefully will never be
>> accessed. But on audit, if it isn't there, they are in a boatload of
>> trouble.

>> This is a really bad idea, for many reasons, you should drop
>> immediately. Leave managing the database to the database professionals.
>> It is not something someone writing PHP should involve themselves in.

> I think he means he wants to identify obsolete data in his results cache,
> so he can refresh it from the database, not delete data from the database.
> However, I agree that this is not a wonderful idea, as it is essentially
> recreating Oracle functionality. If results caching is that critical,
> I would suggest running 11g, which implements it.

Then, assuming we are talking about 11g, which is the only version that has a result cache that is automatically implemented by Oracle and not subject to developer meddling except via the DBMS_RESULT_CACHE built-in package.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu May 22 2008 - 13:48:22 CDT

Original text of this message