Re: sql parser for oracle 9i

"christian kasper" <christian_at_kasper.ws> wrote in message news:329aa663.0302240554.5722a47f_at_posting.google.com...
> > > hello,
> > > i need a sql parser for oracle 9i.
> I have a application with thousands of sql statements in pl/sql
> scripts, in triggers, in pro*c programs, in shell scripts ...
>
> My work in the project is to develop a scanner for
> insert/update/select/delete statements to describe the r/w access per
> procedure/trigger/function/script on tables/views. The output of the
> scanner is a html file that describes the relationship between
> procedure/trigger/function/script and r/w access on tables/views for
> documentation purposes.
>
> My question is, do you know a parser, who give me the names of
> tables/views in selects/insert/update/delete statements. The parser
> must know the syntax from oracle 9i.

I assume you also want to know which columns are accessed by the SQL statements, if you are going to document this well.

To do this, you have to read not only the SQL statements, but also the DDL for the SQL so that queries that don't name columns, and implicit relations constructed as intermediate forms can be resolved into the columns they use.

So this isn't just "parsing", you must do name and type resolution over the SQL. You must also pick out the SQL statements from the various languages you are discussing (PL/SQL, PRO*C, triggers, shell scripts, ...)

I think you need pretty strong parsing technology support, backed up by symbol table support, backed up by type inferencing procedures (to determine implicit types of joins, etc.).

You might be able to do this with PERL.
It'd be pretty hard at best, because you'd need a lot of machinery to support it..

A tool which probably can do it is our DMS Software Reengineering Toolkit. It has strong parsing technlogy, and can be "easily" taught to parse PRO*C and shell scripts; we've already taught it how to parse  SQL and PL/SQL. (as well as many other difficult to parse languages including C++ and COBOL).
It provides symbol table support and the necessary machinery to support the type inferencing.
See http://www.semdesigns.com/Products/DMS/DMSToolkit.html.

This would not be an weekend project, though.

--
Ira D. Baxter, Ph.D., CTO   512-250-1018
Semantic Designs, Inc.      www.semdesigns.com