Re: Extract SQL statements from Database/File
- From: chet justice <chet.justice@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Tue, 15 Sep 2009 11:24:31 -0400
Some time back I was trying to build a parser to get fine-grained
dependencies from source code.
Step one was to put TEXT from DBA_SOURCE, for each OWNER, TYPE, and NAME
into a CLOB. Part of that is
here<http://code.google.com/p/plsqlsourcedownload/source/browse/#svn/trunk>
.
Step two was to use REGEX to then extract the DML so not only would I have
the dependencies from DBA_DEPENDENCIES, I would know whether it was an
INSERT/UPDATE/DELETE/SELECT statement which I thought would be even more
helpful (I had legacy systems in mind initially where entry points into
tables were strewn about the code and the end goal being to consolidate
them).
It was mostly an excuse to get better (uh...learn) regular expressions.
Point is, it sounds similar to what you described, extracting individual
statements from source code.
chet
On Tue, Sep 15, 2009 at 11:06 AM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
> Rajesh Rao wrote:
> > The objective is to generate explain plans against all of the statements
> in a performance environment before deployment in Production. This is, in
> case, the current performance test cases never execute certain SQL
> statements. I understand that this would not be foolproof and explain plans
> might still differ from actual execution plans.
> >
> > The problem with PL/SQL profiler is the same as I stated in the earlier
> email. There would be a need to execute the packages and it is possible that
> the execution might not execute some SQL statements due to the conditional
> flows, unless the data is conditioned appropriately for each and every
> possible test case. This is for a critical application that is being
> re-architected and it's a complete rewrite. Hence the need to cover all
> bases.
> >
> > Regards
> > Raj
> >
> >
> I see. Well, the point raised by Joe about dynamic SQL was very valid,
> and perhaps you should be careful with the EXECUTE IMMEDIATE statements
> and calls to DBMS_SQL. And I'm not sure that checking execution plans
> will give you must assurance about plan changes. If there is a place
> where bind variables are systematically used, it's PL/SQL. Don't give
> more worth to the exercise than it really deserves, even with a nearly
> perfect code coverage.
> There is a tool I haven't personally used but which I have seen demo'd
> and that looks interesting, which might be an answer to the problem of
> extracting statements -Smart-TS. There may be others, but at least I
> have heard of this one.
>
> HTH
>
> SF
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
chet justice
www.oraclenerd.com
Other related posts:
