DBI: Identify schema objects for a statement

Errto has asked for the wisdom of the Perl Monks concerning the following question:

I have a module where I would like to take a given $dbh in DBI, and a given SQL statement, and programatically obtain the list of database objects (tables and views) that this query accesses. I could use SQL::Statement but I worry that I will need to support SQL syntax features that it doesn't (for example, subselects in the FROM clause). So I would like to somehow pass the statement to the database itself and get the information that way. For example, I would feed it

SELECT * FROM foo
 JOIN bar ON foo.a = bar.a 
JOIN (SELECT * FROM baz where something_or_other) as mysub1 ON foo.z =
+ mysub1.z 
WHERE foo.b in (SELECT y+1 FROM quux)
[download]

and it would give me back a list like ('foo','bar','baz','quux') My database is Oracle, so if no generic solution exists, I could happily live with an Oracle-specific one.

As an addendum, I should add that I also looked at using EXPLAIN PLAN and just querying the object names from the plan table, but I discovered that if my query uses views it would give me the underlying table names, whereas what I need are the view names.

Comment on DBI: Identify schema objects for a statement Select or Download Code

Replies are listed 'Best First'.
Re: DBI: Identify schema objects for a statement by jZed (Prior) on Aug 29, 2007 at 00:04 UTC
My first suggestion would be to look in DBD::Oracle and see what it stores in $sth after a prepare(), you may be able to get some information from that. More likely though, you won't. So my next best guess would be do a DESCRIBE and parse its output. That probably won't be satisfactory either. So my third guess is a module I haven't used and isn't Oracle specific but has richer parsing than my SQL::Statement ... DBIx::MyParse an embeddable parser for MySQL which can return data structures of any SQL that MySQL understands.	[reply]
Re^2: DBI: Identify schema objects for a statement by CountZero (Bishop) on Aug 29, 2007 at 13:26 UTC
But wouldn't that be very MySQL-centric and not necesseraly applicable to an Oracle DB? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re^3: DBI: Identify schema objects for a statement by jZed (Prior) on Aug 29, 2007 at 15:44 UTC
Well ultimatley, the OP is right in the intuition that "only Oracle can parse oracle" (or somesuch). If there isn't a way for it to do that in a way that returns a data structure containing table and view names, then the OP is left with options that will not be complete so MyParse is my thought on the least incomplete of the options. If the alternative is looking through all the SQL by hand, it would at least allow grabbing a lot of the SQL and setting the rest aside to be looked at by hand so it's a win (this also applies to a lesser extent to SQL::Statement). And too, MyParse is, after all an open source module and can therefore possibly be tweaked to handle more Oraclish dialects (isn't that what the bad guys spoke in LOTR?). You're right, MyParse isn't a good solution but it may be the best perl has to offer for this particular task. And Errto - here's anothr possibility: the Mimer SQL folks put out a very credible (non--perl, also non-free IIRC) SQL parser that might also be a possibility. update : And here's another thought: the SQL for table and view names is (somewhat) more standard between dialects than other aspects of SQL so errto may be able to get the names out of a partially-parsed structure. E.g given "SELECT foo FROM bar WHERE oracle_only_function(baz) = ?", the parser might be able to tell that the tablename is "bar" even if it barfs on the oracle_only_function().	[reply]
Re^4: DBI: Identify schema objects for a statement by rdfield (Priest) on Aug 30, 2007 at 11:13 UTC
Re^5: DBI: Identify schema objects for a statement by jZed (Prior) on Aug 30, 2007 at 15:36 UTC
Some notes below your chosen depth have not been shown here
Re: DBI: Identify schema objects for a statement by rdfield (Priest) on Aug 29, 2007 at 13:13 UTC
Although `v$sql_plan` does mention views, it's not complete (especially for simple views). However you could wrap up the sql in a procedure and look at dba_dependencies: `$dbh->do("create or replace procedure test1 as cursor c1 is $sql; begin null; end; "); $dbh->parse("select referenced_name,referenced_type from dba_dependencies where name = 'TEST1' and type = 'PROCEDURE'"); ...` [download] rdfield	[reply] [d/l] [select]
Re^2: DBI: Identify schema objects for a statement by Errto (Vicar) on Aug 29, 2007 at 14:49 UTC
Tried that and it worked great. Thanks. Creating and dropping procedures all the time seems somehow drastic, but sometimes you gotta do what you gotta do.	[reply]
Re: DBI: Identify schema objects for a statement by rdfield (Priest) on Aug 29, 2007 at 11:09 UTC
Use `$dbh->do("explain plan ... $sql")` and query the plan table for the objects. Or if the code has already executed you should be able to find the data in `v$sql_plan` rdfield	[reply] [d/l] [select]


Your skill will accomplish what the force of many cannot
	PerlMonks