Re: Extract SQL statements from Database/File
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Rajesh.Rao@xxxxxxxxxxxx
- Date: Tue, 15 Sep 2009 16:40:13 +0200
Rajesh Rao wrote:
>
> Hello All, any tools/scripts out there that you are aware of, to
> extract all select/insert/update/delete statements from within the
> packages for a schema? The schema in question has very large number of
> packages. Executing the packages themselves with tracing enabled was
> one way I could think of, but that might not hit all SQL’s within the
> package (conditional flows, etc).
>
> Regards
>
> Raj
>
Raj,
What's the idea behind your question? My experience with packages is
that when they are poorly written, which happens more often than I'd
like, the problem isn't so much with individual statements that look and
are usually innocuous enough and for the most part "untunable" by
themselves, but with cursor loops all over the place and a crazy logic
(to my eyes, at least) directly imported from the most orthodox
procedural programming applied to sequential files.
Have you thought about using the PL/SQL profiler? Wonderful tool, and
under-used IMHO.
Otherwise, I have once written a C program that scans the source of
packages, looks for WHEN OTHERS THEN [return] NULL and other niceties,
cursor loops, statements in cursor loops, it was also computing
cyclomatic complexity and so on. The result was so dispiriting that I
have stopped using it.
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
