Generate a CRUD matrix
in [Oracle]
|
Prev: deleting many rows from a table
Next: SQLPLUS Question
From: Terry Dykstra on 14 Jan 2010 12:23 Is it possible to extract a CRUD matrix for a procedure or package in Oracle. I'm using 10.2.0.4 SE. Getting a list off tables etc is easy enough to do using ALL_DEPENDENCIES, but I would like to know for each table whether the proc is updating, inserting etc. -- Terry Dykstra
From: joel garry on 14 Jan 2010 12:51 On Jan 14, 9:23 am, "Terry Dykstra" <tddyks...(a)forestoil.ca> wrote: > Is it possible to extract a CRUD matrix for a procedure or package in > Oracle. I'm using 10.2.0.4 SE. > Getting a list off tables etc is easy enough to do using ALL_DEPENDENCIES, > but I would like to know for each table whether the proc is updating, > inserting etc. > > -- > Terry Dykstra The first thing that comes to mind is using a subquery to search user_source for the keywords for each matrix element, to limit the selection of user_source in a script that would otherwise print all procedures. jg -- @home.com is bogus. I have my Gumby and Pokey! http://en.wikipedia.org/wiki/Art_Clokey http://www.signonsandiego.com/news/2010/jan/09/gumby-animator-art-clokey-dies-88-california/
From: vsevolod afanassiev on 14 Jan 2010 15:36 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
From: joel garry on 14 Jan 2010 18:40 On Jan 14, 12:36 pm, vsevolod afanassiev <vsevolod.afanass...(a)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 What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)? Once 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? Did I misread the OP or miss something obvious? I 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 -- @home.com is bogus. http://thedailywtf.com/Articles/The-Little-Red-Switch.aspx
From: Mark D Powell on 15 Jan 2010 09:38
On Jan 14, 6:40 pm, joel garry <joel-ga...(a)home.com> wrote: > On Jan 14, 12:36 pm, vsevolod afanassiev > > <vsevolod.afanass...(a)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 > > What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)? Once 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? Did I > misread the OP or miss something obvious? I 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 > -- > @home.com is bogus.http://thedailywtf.com/Articles/The-Little-Red-Switch.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? You may also have to deal with commented out sections of source. 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 -- |