Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Re^2: DBI: Identify schema objects for a statement

by CountZero (Bishop)
on Aug 29, 2007 at 13:26 UTC ( #635796=note: print w/replies, xml ) Need Help??

in reply to Re: DBI: Identify schema objects for a statement
in thread DBI: Identify schema objects for a statement

But wouldn't that be very MySQL-centric and not necesseraly applicable to an Oracle DB?


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

  • Comment on Re^2: DBI: Identify schema objects for a statement

Replies are listed 'Best First'.
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().
      with my_alias as ( select * from view1@DBLINK1) select (select * from view27 v27 where v27.col1 = a.col3) from (select * from table1) a, (select * from (select * from my_alias) b, view c where = d where =
      Table and view names "more standard"?


        Compared to say LIMIT clauses or concatenation operators or function names, yes. And what's so nonstandard about the table names in your example? They are all in the format "FROM x".

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://635796]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2018-01-21 00:31 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (227 votes). Check out past polls.