Parsing PL/SQL Source Code

Hi Everybody!!

My name is Gabriel Jenik and I am in the need of analyzing a PL/SQL
source code in order to extract all the tables being used by any SQL
statemnt (INSERT, UPDATE, DELETE or SELECT)

The code is 7000 lines long so it is not usefull to do it by hand.
That's when I thought about awk and regex.

Does anybody has a clue, thought, experience or idea about from where
to start? Any startegy that I could follow?

I got stuck when having to deal with multiple tables in a "select ...
from ..." statement.

Thanks

Gabriel

On 1 Oct 2004 10:47:04 -0700 in comp.lang.awk, gabrieljenik@yahoo.es
(Gabriel Jenik) wrote:

>I am in the need of analyzing a PL/SQL
>source code in order to extract all the tables being used by any SQL
>statemnt (INSERT, UPDATE, DELETE or SELECT)
>
>The code is 7000 lines long so it is not usefull to do it by hand.
>That's when I thought about awk and regex.
>
>Does anybody has a clue, thought, experience or idea about from where
>to start? Any startegy that I could follow?
>
>I got stuck when having to deal with multiple tables in a "select ...
>from ..." statement.

Save yourself the trouble, it's not worth the hassle, due to the
non-line oriented nature of SQL, and additional clauses that may be
present.
Make a copy of the file (perhaps with line numbers), use an editor to
search for 'insert', 'update', 'delete', and 'from', delete all the
lines that don't contain tables, then clean up remaining junk.
You should be able to do it in about an hour for 7000 lines.

--
Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada

Brian.Inglis@CSi.com  	(Brian[dot]Inglis{at}SystematicSW[dot]a
b[dot]ca)
fake address		use address above to reply

Brian Inglis <Brian.Inglis@systematicsw.invalid> wrote:
> On 1 Oct 2004 10:47:04 -0700 in comp.lang.awk, gabrieljenik@yahoo.es
> (Gabriel Jenik) wrote:
> 
>
> Save yourself the trouble, it's not worth the hassle, due to the
> non-line oriented nature of SQL, and additional clauses that may be
> present.
> Make a copy of the file (perhaps with line numbers), use an editor to
> search for 'insert', 'update', 'delete', and 'from', delete all the
> lines that don't contain tables, then clean up remaining junk.
> You should be able to do it in about an hour for 7000 lines.

Good advice.  However, if you're extracting only the names of tables,
then it's doable using regex, because you know the syntax of SQL and
where the table names appear.  But, because SQL is free-form, you need
to slurp the file as long string, and apply regex on that, ignoring the
special meaning of \n.  Perl, Python, and Bash can do this, but Awk,
Sed, and Grep will have hard time.

--
William Park <opengeometry@yahoo.ca>
Open Geometry Consulting, Toronto, Canada

On 2 Oct 2004 06:39:09 GMT in comp.lang.awk, William Park
<opengeometry@yahoo.ca> wrote:

>Brian Inglis <Brian.Inglis@systematicsw.invalid> wrote: 
>
>Good advice.  However, if you're extracting only the names of tables,
>then it's doable using regex, because you know the syntax of SQL and
>where the table names appear.  But, because SQL is free-form, you need
>to slurp the file as long string, and apply regex on that, ignoring the
>special meaning of \n.  Perl, Python, and Bash can do this, but Awk,
>Sed, and Grep will have hard time.

The problem is not finding where the table name(s) start, rather
deciding when they stop, as each SQL dialect has various different
clauses that can follow.

--
Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada

Brian.Inglis@CSi.com  	(Brian[dot]Inglis{at}SystematicSW[dot]a
b[dot]ca)
fake address		use address above to reply