Re: Parsing SQL Statement

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Mon, 26 Mar 2007 10:15:50 -0700 (PDT)
Message-ID: <266551.98732.qm@web52805.mail.re2.yahoo.com>

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

select distinct b.grantee, a.password_required, p.default_role

       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

Original Message ---- From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com> To: sharmakdeep_oracle_at_yahoo.com Cc: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, March 21, 2007 11:00:05 AM Subject: 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


 
____________________________________________________________________________________
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-l

Received on Mon Mar 26 2007 - 12:15:50 CDT