Re: Generate a CRUD matrix
Date: 15 Jan 2010 10:12:02 -0700
Message-ID: <4b50aff2$1_at_news.victoria.tc.ca>
joel garry (joel-garry_at_home.com) wrote:
: On Jan 15, 6:38=A0am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
: > On Jan 14, 6:40=A0pm, joel garry <joel-ga..._at_home.com> wrote:
: >
: >
: >
: > > On Jan 14, 12:36=A0pm, vsevolod afanassiev
: >
: > > <vsevolod.afanass..._at_gmail.com> wrote:
: > > > I don't think it is possible: a stored procedure may select/insert/
: > > > update/delete many tables, something like that
: >
: > > > SELECT
: > > > FROM TABLE_A
: >
: > > > UPDATE
: > > > TABLE_B
: >
: > > > DELETE TABLE_C
: >
: > > > Searching USER_SOURCE won't be enough
: >
: > > =A0What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)? =A0Once =
: you
: > > get the candidate list, you parse the procedures for their tables.
: > > Select is easy, just whatever follows FROM until no more commas,
: > > update and delete will always be one or two words after? =A0Did I
: > > misread the OP or miss something obvious? =A0I was thinking of getting
: > > the various elements separately (these procedures update, containing
: > > tables x, y z..., etc), but maybe easier just to get procedure/table/
: > > elements parsing directly, put in table, then do what you want.
: >
: > > jg
: > > --
: > > _at_home.com is bogus.http://thedailywtf.com/Articles/The-Little-Red-Switc=
: h.aspx
: >
: > I do not think parsing the from clause would be all that easy since
: > you have to allow for encountering terms like inner join, right outer
: > join, etc... then what about dynamic SQL where portions of the SQL
: > statement are going to be held in variables? =A0You may also have to
: > deal with commented out sections of source.
: Very good points, obviously my head is stuck in O7. Still, you can
: perhaps get everything in a new join syntax up until the ON keyword.
: This makes a lot more sense if you are limited to a certain subset of
: language usage on an older system, rewriting the latest oracle SQL
: would be huge - I can imagine one impenetrable model clause could stop
: me cold. I totally missed the dynamic SQL issue, though it would
: still have to have some kind of FROM, I think?
: >
: > I would be interested in getting a copy of a routine that can
: > automatically extract SQL from stored code even if it had no ability
: > to deal with dynamic SQL (since no well written system should use more
: > than a few dynamic SQL statements).
: >
: > HTH -- Mark D Powell --
: OK, I'm convinced, this would bite off more than I could chew. Easier
: to just extract all the procedures and eyeball them for crud. Maybe
: comments in procedures will answer the OP :-)
No, use the dependencies table (USER_DEPENDENCIES (?)) to find what table names are accessed. Then search for those names. Or grep those names to find the line numbers in the code and go straight to those line numbers. Or use (e.g.) perl to add a tag to each keyword, and search for the tag (which is easier than searching for each keyword).
untested:
:: DOS BAT FILE, invoke perl for each keyword (line wrapped for msg) for %i in (list the table names here) do perl -i.%i.bak -pe "s/(%i)/ /*!*/ $1/gi" source-file.pls
In this example I would search for /*!*/.
$0.10 Received on Fri Jan 15 2010 - 11:12:02 CST