Re: Parsing SQL Statement

Deepak

This question has been asked before - eg http://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

--
http://www.freelists.org/webpage/oracle-l