Re: Parsing SQL Statement
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: sharmakdeep_oracle@xxxxxxxxx
- Date: Wed, 21 Mar 2007 09:00:05 -0700 (PDT)
Deepak
This question has been asked before - eg
//www.freelists.org/archives/oracle-l/09-2006/msg01064.html. Follow that
thread to find various parsers (of greater or lesser value).
> Is there an easy way to extract the different parts of an SQL statement
> (SELECT, FROM, WHERE), in order to determine the columns being used,
> tables being accessed and columns being used in the where clause?
No, not an easy way. However if you restrict yourself to SELECTs, you can
create a view and then
- ALL/USER_TAB_COLUMNS tells you the columns being *projected*
- ALL/USER_DEPENDENCIES tells you all the objects your view references directly
(and then you can follow the dependency network to find indirect references)
> For simple SQL that has only one of the above clauses, may be simple (using
> substr, instr etc.),
> but it gets complicated with inline views, sub-queries etc.
It certainly does!
> Would "Oracle Text" be of any significance here in order to do the parsing?
Not really - that can tell you that the word 'SELECT' is near the word
'ORDER_ID' - but that's not precise enough. Even when you have a syntax tree,
it's not always obvious which column in the select list goes with which table
in the from clause.
Good luck with that...
Regards Nigel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
