Generate a CRUD matrix
in [Oracle]
|
Prev: deleting many rows from a table
Next: SQLPLUS Question
From: joel garry on 15 Jan 2010 12:33 On Jan 15, 6:38 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > 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. 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 :-) jg -- @home.com is bogus. http://www.newspeakdictionary.com/wastetime.gif
From: Tiago on 15 Jan 2010 13:05 On Jan 14, 2:23 pm, "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 Conquest's Clear SQL do what you want. I'm not affiliated to these guys and this is not a free software, but has usable trial version. http://www.conquestsoftwaresolutions.com/page/clearsql_pr_description hth -- Tiago
From: Malcolm Dew-Jones on 15 Jan 2010 12:12 joel garry (joel-garry(a)home.com) wrote: : On Jan 15, 6:38=A0am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: : > On Jan 14, 6:40=A0pm, joel garry <joel-ga...(a)home.com> wrote: : > : > : > : > > On Jan 14, 12:36=A0pm, 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 : > : > > =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 : > > -- : > > @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
From: Terry Dykstra on 15 Jan 2010 14:38 "Malcolm Dew-Jones" <yf110(a)vtn1.victoria.tc.ca> wrote in message news:4b50aff2$1(a)news.victoria.tc.ca... > joel garry (joel-garry(a)home.com) wrote: > : On Jan 15, 6:38=A0am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > : > On Jan 14, 6:40=A0pm, joel garry <joel-ga...(a)home.com> wrote: > : > > : > > : > > : > > On Jan 14, 12:36=A0pm, 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 > : > > : > > =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 > : > > -- > : > > @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 > I'm not following. What good does going to a line in the source table going to do for me? select col1, col2, .., ..., from mytable; -- Terry Dykstra
From: Malcolm Dew-Jones on 15 Jan 2010 14:49
Terry Dykstra (tddykstra(a)forestoil.ca) wrote: : "Malcolm Dew-Jones" <yf110(a)vtn1.victoria.tc.ca> wrote in message : news:4b50aff2$1(a)news.victoria.tc.ca... : > joel garry (joel-garry(a)home.com) wrote: : > : On Jan 15, 6:38=A0am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: : > : > On Jan 14, 6:40=A0pm, joel garry <joel-ga...(a)home.com> wrote: : > : > : > : > : > : > : > : > > On Jan 14, 12:36=A0pm, 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 : > : > : > : > > =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 : > : > > -- : > : > > @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 : > : I'm not following. What good does going to a line in the source table going : to do for me? You said you were going to "eyeball them for crud". I'm suggesting you can do that more quickly and reliably by first finding and marking the text that contains the references to the objects, that way in your editor you can jump straight to all the parts of the code that need to be examined, and also know you missed nothing (except execute immediate stuff). You can use user_dependencies to find the names of the objects. Whether this is useful depends on the number of objects and the size of the files and how you like to work. |