Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parsing SQL Statement
I tried using the Zql on following statement (picked from another post), but it failed to parse on the subquery :
$ cat TEST.in
select b.grantee, a.password_required, p.default_role from dba_roles a, dba_role_privs p, ( select grantee from( select grantee from dba_tab_privs where privilege in ('INSERT','UPDATE', 'DELETE') union select grantee from dba_sys_privs where (privilege like '%UPDATE%' or privilege like '%DELETE%' or privilege like '%INSERT%') ) )b where b.grantee = a.role and b.grantee = p.grantee (+) and a.password_required = 'NO' order by b.grantee;
EXIT;
$ java Zql.ZqlParser TEST.in
/* Reading from TEST.in*/
Exception in thread "main" Zql.ParseException: Encountered "(" at line 1, column 91
.....
Original SQL
from dba_roles a, dba_role_privs p, ( select distinct grantee from( select grantee from dba_tab_privs where privilege in ('INSERT','UPDATE', 'DELETE') union select grantee from dba_sys_privs where (privilege like '%UPDATE%' or privilege like '%DELETE%' or privilege like '%INSERT%') ) )b where b.grantee = a.role and b.grantee = p.grantee (+) and a.password_required = 'NO' order by b.grantee
Thanks for the info though.
Deepak
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 ____________________________________________________________________________________ Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 26 2007 - 12:15:50 CDT
![]() |
![]() |