RE: sql parser for oracle

From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
To: "Adi Hirschtein" <adi@xxxxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
Date: Mon, 25 Sep 2006 11:10:18 +0200
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@xxxxxxxxxxxxx] 
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@xxxxxxxxxxxxxx] 
Sent: 25 September 2006 09:22
To: knecht.stefan@xxxxxxxxx; adi@xxxxxxxxxxxxx
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@xxxxxxxxx] 
Sent: Monday, 25 September, 2006 9:13 AM
To: adi@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx> 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
RE: sql parser for oracle

Other related posts:
