Prev: deleting many rows from a table
Next: SQLPLUS Question
From: Galen Boyer on 16 Jan 2010 13:45
"Terry Dykstra" <tddykstra(a)forestoil.ca> writes:

> 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.

If you took selection out of the equation you might be able to do it.

UPDATE TABLE
INSERT INTO TABLE
DELETE TABLE

Those should be able to be found from the source if you did regexp
replace of all whitespace characters down to say, a space, '\s+' works.
There could be comment characters between these ie:
UPDATE -- Updating because of X
TABLE

So get rid of those as well. Sort of an interesting exercise. You'd
have to have a way to find beginning and ending of comments.

The dependencies found that aren't in that list would be a subset of the
select objects. Its the objects in that DML list that also have
selection that seems more complicated.

ANOTHER OPTION.
What you could try is create a new schema and create synonyms to all the
objects in the Matrix schema. Grant CRUD from MATRIX to the new schema
on all objects. Then, psuedo-code (Probably run as a user with
appropriate privileges in both schemas):

FOR obj in correct compilation order from MATRIX LOOP
FOR priv IN SELECT DELETE UPDATE INSERT LOOP
1) AS MATRIX schema, revoke priv from obj;
2) AS new schema, compile all objects force;
3) Capture the errors;
4) AS MATRIX schema, grant priv on obj to new schema;
END LOOP;
END LOOP;

The captured errors would give you good information on the dependencies.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Terry Dykstra on 18 Jan 2010 13:22
"Malcolm Dew-Jones" <yf110(a)vtn1.victoria.tc.ca> wrote in message
news:4b50d4ca$1(a)news.victoria.tc.ca...
> 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.

I think it was Joel who suggested the "eye-balling". Not me.

Terry


First  |  Prev  | 
Pages: 1 2 3
Prev: deleting many rows from a table
Next: SQLPLUS Question