Re: Generate a CRUD matrix

From: Galen Boyer <>
Date: Sat, 16 Jan 2010 13:45:00 -0500
Message-ID: <>

"Terry Dykstra" <> writes:

> Is it possible to extract a CRUD matrix for a procedure or package in
> Oracle. I'm using 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.

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.

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

           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:// - complaints: ---
Received on Sat Jan 16 2010 - 12:45:00 CST

Original text of this message