RE: sql parser for oracle

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Mon, 25 Sep 2006 11:10:18 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D568710@mailserver1>

I am using this one in order to test my deparser :

"
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

This SQL is valid and the trap here is that 'b.grantee' belong to dba_tab_privs or dba_sys_privs which are themself into an unamed view and the view itself is the
results of an union. Since the inner inlined view is non named, the deparser has to generated a default name and limit its scope to 'b' In order to fool a bit more, note the presence of the 2 'or' into brackets. These brackets do not mark the start and end of an inlined view.

This is one the most basic I have in stock for generating in house headaches in Perl. A deparser must be something of several tens of thousand of lines.
if you consider also processing ANSI syntax or construct such as 'with sub as '

B.Polarski

From: Adi Hirschtein [mailto:adi_at_zetapoint.com] Sent: Monday, 25 September, 2006 10:38 AM To: Polarski, Bernard
Subject: RE: sql parser for oracle

Well, u saved me some time...

As u guess I'm interested in an advanced parser that can take care of the inline views and some other complex sql expressions

so I'll keep searching -:-)

Thanks,

Adi

From: Polarski, Bernard [mailto:Bernard.Polarski_at_atosorigin.com] Sent: 25 September 2006 09:22
To: knecht.stefan_at_gmail.com; adi_at_zetapoint.com Cc: oracle-l
Subject: RE: sql parser for oracle

I tested them and they are very basic, so basic that you will quickly run away.

I am also very interrested in a deparser but never find one that survives inlining view. Be aware that SQL beautifyer will not help.

These just to do break on some keyword without keeping track of the level of of break of the inline view nor which predicate belongs to which part of the SQL

going even further, as of linking a specifc line of predicate to a table, taking into accounts the level of inlining views had never been achieved.

I made some initial investigation and it is a daunting work, even in perl.

B. Polarski

From: Stefan Knecht [mailto:knecht.stefan_at_gmail.com] Sent: Monday, 25 September, 2006 9:13 AM To: adi_at_zetapoint.com
Cc: oracle-l
Subject: Re: sql parser for oracle

Have a look on CPAN, lotsa PERL modules to parse SQL

http://search.cpan.org/search?query=sql&mode=all

Stefan

On 9/25/06, Adi Hirschtein <adi_at_zetapoint.com> wrote:

Hi Guys,

Does anyone know where I can find sql parser?

I don't mind if it will be an oracle solution or something outside the db like java/c++ code or any other language.

Generally, I need to take sql statements and to parse them to small pieces (e.g. taking out the columns ,tables ,where clause and etc).

Thanks,

Adi

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

Received on Mon Sep 25 2006 - 04:10:18 CDT