Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.

UPDATE October 2015 – the latest ( and let’s face it, least buggy) version of this application is now avaiable on GitHub.

At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here.

Enhancements from the first version

I’ve made a few changes in this version, as well as sorting out a couple of bugs that are just too embarrassing to mention…

• Searching has been refined with the use of regular expressions to improve accuracy
• Debug information added by means of conditional compilation statements
• Runtime information available by means of DBMS_APPLICATION_INFO
• Dependencies on synonyms now picked up.

Application Components

The application comprises scripts to create the CRUD_OWNER database schema and tables.
There is a database procedure, which trawls through the code to work out the relationships.
Finally there is an xml file we can use to deploy the SQLDeveloper Extension.

A word about where to install

I’m sure that you’re far too sensible to take an unsolicited piece of software off the web and dump it straight into a production environment. You’ll probably also have noticed that, to get an accurate picture of your dependencies, all you need to do is run this code in an environment that has the same structure (i.e. tables and database code) as your production instance.

Database Installation

The first step is to create our application owner – CRUD_OWNER. As part of this, we need to grant SELECT CATALOG ROLE to enable the use of DBMS_METADATA. Therefore, we need to connect as SYS as SYSDBA….

$ sqlplus sys@xe as sysdba 
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 10 19:03:14 2011 
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
Enter password: 
Connected to: 
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 

SQL> GRANT connect, resource, select any dictionary, 
 2   select_catalog_role TO crud_owner identified by pwd 
 3  / 

Grant succeeded. 

SQL> 

Replace pwd with the password you want to use for this user.

Now we need to connect as CRUD_OWNER to complete the setup…

$ sqlplus crud_owner/pwd@xe 

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 10 19:07:15 2011 
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

Connected to: 
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 

SQL>

First, create the tables to hold the matrix

CREATE TABLE db_crud( 
    table_owner VARCHAR2(30), 
    table_name VARCHAR2(30), 
    object_owner VARCHAR2(30), 
    object_name VARCHAR2(128), 
    object_type VARCHAR2(20), 
    create_flag VARCHAR2(1), 
    read_flag VARCHAR2(1), 
    update_flag VARCHAR2(1), 
    delete_flag VARCHAR2(1)) 
/ 
GRANT SELECT ON db_crud TO PUBLIC 
/

CREATE TABLE crud_run_log ( 
    last_run_date DATE) 
/

Then the procedure to build the matrix…
NOTE – big thanks to Bob Siegel ( see comments below) for pointing out the issue with the underscores in the regular expression :

CREATE OR REPLACE PROCEDURE crud ( pa_refresh IN VARCHAR2 DEFAULT 'N')
    AUTHID CURRENT_USER AS
--------------------------------------------------------------------------------
--  Description :                                                             
--  Procedure to reverse-engineer a CRUD Matrix for all of the application    
--  tables in the database.                                                   
--                                                                            
-- Parameter :                                                                
--      pa_refresh - 'Y' refresh all of the data in the table                 
--                   'N' just look at program units changes since the last    
--                       run of this procedure                                
-- Functionality :                                                            
--  1) Identify all of the non-oracle user owned tables                       
--  2) For each table, get a list of dependent objects                        
--     include objects that are dependent on synonyms rather than the tables  
--     directly                                                               
--     If pa_refresh set to N, just look for objects that have been changed   
--     or created since the last run.                                         
--  3) Get the source code for each object                                    
--  4) Work out the relationship                                              
--  5) Dump the results into the db_crud table                                
--  6) Update the crud_run_log with the timestamp for this run                
--------------------------------------------------------------------------------
    l_last_run DATE;

    CURSOR c_last_run IS
        SELECT last_run_date
        FROM crud_run_log;

    TYPE rec_tabs IS RECORD (
        table_owner dba_tables.owner%TYPE,
        table_name dba_tables.table_name%TYPE);

    TYPE typ_tabs IS TABLE OF rec_tabs INDEX BY PLS_INTEGER;

    tbl_tabs typ_tabs;

    TYPE rec_dep_objs IS RECORD (
        dep_owner dba_dependencies.owner%TYPE,
        dep_name dba_dependencies.name%TYPE,
        dep_type dba_dependencies.type%TYPE,
        syn_name dba_synonyms.synonym_name%TYPE);

    TYPE typ_dep_objs IS TABLE OF rec_dep_objs INDEX BY PLS_INTEGER;

    tbl_dep_objs typ_dep_objs;

    l_clob CLOB;
    l_create VARCHAR2(1);
    l_read VARCHAR2(1);
    l_update VARCHAR2(1);
    l_delete VARCHAR2(1);

    CURSOR c_delta( cp_owner VARCHAR2, cp_name VARCHAR2, cp_last_run DATE) IS
        SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_'),
            NULL
        FROM dba_dependencies dep, dba_objects obj
        WHERE dep.owner = obj.owner
        AND dep.name = obj.object_name
        AND dep.type = obj.object_type
        AND dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
           'VIEW', 'MATERIALIZED VIEW')
        AND dep.referenced_owner = cp_owner
        AND dep.referenced_name = cp_name
        AND obj.last_ddl_time > cp_last_run
        UNION
        SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_'),
            syn.synonym_name
        FROM dba_dependencies dep, dba_objects obj, dba_synonyms syn
        WHERE dep.owner = obj.owner
        AND dep.name = obj.object_name
        AND dep.type = obj.object_type
        AND dep.referenced_owner = syn.owner
        AND dep.referenced_name = syn.synonym_name
        AND dep.referenced_type = 'SYNONYM'
        AND syn.table_owner = cp_owner
        AND syn.table_name = cp_name
        AND obj.last_ddl_time > cp_last_run;

    CURSOR c_full( cp_owner VARCHAR2, cp_name VARCHAR2) IS
        SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_'), 
            NULL
        FROM dba_dependencies dep
        WHERE dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
            'VIEW', 'MATERIALIZED VIEW')
        AND dep.referenced_owner = cp_owner
        AND dep.referenced_name = cp_name
        UNION
        SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_'),
            syn.synonym_name
        FROM dba_dependencies dep, dba_synonyms syn
        WHERE dep.referenced_owner = syn.owner
        AND dep.referenced_name = syn.synonym_name
        AND dep.referenced_type = 'SYNONYM'
        AND syn.table_owner = cp_owner
        AND syn.table_name = cp_name;

    e_first_run EXCEPTION;
    l_dummy PLS_INTEGER := 0;

    --
    -- Variables for debug timing info
    --
    l_start PLS_INTEGER := 0;
    l_end PLS_INTEGER := 0;
    l_elapsed PLS_INTEGER := 0;

    --
    -- Variables for message to return in the event
    -- of an EXCEPTION being raised
    --
    l_owner dba_dependencies.owner%TYPE;
    l_name dba_dependencies.name%TYPE;
    l_type dba_dependencies.type%TYPE;
    l_loc VARCHAR2(3) := '010';

BEGIN
    --
    -- Set the Module and Action values to allow runtime monitoring in 
    -- V$SESSION
    --
    DBMS_APPLICATION_INFO.SET_MODULE('CRUD', 'Start');

    --
    -- First, check to see if we're doing a full refresh or just looking
    -- for changes since the last run
    --
    IF pa_refresh = 'N' THEN
        l_loc := '015';
        OPEN c_last_run;
        FETCH c_last_run INTO l_last_run;
        IF c_last_run%NOTFOUND THEN
            CLOSE c_last_run;
            RAISE e_first_run;
        END IF;
        CLOSE c_last_run;
    ELSE
        --
        -- Cleardown the table for a complete refresh
        --
        l_loc := '020';
        EXECUTE IMMEDIATE 'TRUNCATE TABLE db_crud';
    END IF;

    --
    -- Get a list of all the tables not owned by Oracle supplied users
    --
    l_loc := '030';

    SELECT owner, table_name BULK COLLECT INTO tbl_tabs
    FROM dba_tables
    WHERE owner NOT IN( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'FLOWS_020100',
      'FLOWS_FILES', 'MDSYS', 'OUTLN', 'SYS', 'SYSTEM', 'TSMSYS', 'XDB',
      'EXFSYS', 'PERFSTAT', 'WMSYS');

    --
    -- Conditional compilation needs to be enabled with
    -- ALTER SESSION SET PLSQL_CCFLAGS = 'debug:TRUE' at COMPILE time
    --
    $IF $$debug $THEN
        DBMS_OUTPUT.PUT_LINE('Tables found = '||tbl_tabs.COUNT);    
    $END    

    --
    -- Now loop through the list of tables. This is the outer loop that we
    -- use to pick up all of the dependent objects for the table
    --
    DBMS_APPLICATION_INFO.SET_ACTION('Processing '||tbl_tabs.COUNT||' tables.');

    l_loc := '040';
    FOR i IN 1..tbl_tabs.COUNT LOOP
        IF pa_refresh = 'N' THEN
            l_loc := '045';
            OPEN c_delta( tbl_tabs(i).table_owner, tbl_tabs(i).table_name,
                l_last_run);
            FETCH c_delta BULK COLLECT INTO tbl_dep_objs;
            CLOSE c_delta;
        ELSE
            l_loc := '050';
            OPEN c_full( tbl_tabs(i).table_owner, tbl_tabs(i).table_name);
            FETCH c_full BULK COLLECT INTO tbl_dep_objs;
            CLOSE c_full;
        END IF;
        --
        -- Inner loop - go through the source of the dependent object
        --
        $IF $$debug $THEN    
            DBMS_OUTPUT.PUT_LINE('dependent_objs count for '
                ||tbl_tabs(i).table_owner||'.'||tbl_tabs(i).table_name
                ||' = '||tbl_dep_objs.COUNT);        

            l_start := DBMS_UTILITY.GET_TIME;
        $END            

        IF tbl_dep_objs.COUNT > 0 THEN
            l_loc := '060';
            FOR j IN 1..tbl_dep_objs.COUNT LOOP
                --
                -- Record the owner, name and type for the message to
                -- output in the event of an EXCEPTION
                --
                l_owner := tbl_dep_objs(j).dep_owner;
                l_name := tbl_dep_objs(j).dep_name;
                l_type := tbl_dep_objs(j).dep_type;
                --
                -- Start by re-setting the CRUD flags
                --
                l_create := 'N';
                l_read := 'N';
                l_update := 'N';
                l_delete := 'N';
                --
                -- Work out the type of object. If it's a VIEW or
                -- MATERIALIZED VIEW we'll assume it's just a READ.
                -- Otherwise, we'll look through the source code.
                --
                IF tbl_dep_objs(j).dep_type IN ('VIEW', 'MATERIALIZED VIEW')
                THEN
                    l_read := 'Y';
                ELSE
                    l_loc := '065';
                    --
                    -- Get the sourcecode - after some experimentation
                    -- stripping spaces makes searching much faster although 
                    -- it does not get rid of space characters such as tabs.
                    -- REGEXP_REPLACE(.....'[[:space:]]') would do this but
                    -- it increased runtime by a factor of about 5.
                    -- 
                    -- 
                    -- ...the exception being INSERT where we have to filter
                    -- out INSERT SELECT statements. To do this we need to
                    -- look for the word SELECT, so we can't get rid of the
                    -- word boundaries (i.e. spaces)
                    -- NOTE - thanks to Bob Siegel for pointing out the 
                    -- issue with underscores
                    --
                    l_clob := DBMS_METADATA.GET_DDL(
                        tbl_dep_objs(j).dep_type, tbl_dep_objs(j).dep_name,
                        tbl_dep_objs(j).dep_owner);
                    l_loc := '070';
                    l_dummy := REGEXP_INSTR(l_clob, 
                        'INSERT[^;][^\SELECT\]*INTO[^;_]*'
                        ||NVL( tbl_dep_objs(j).syn_name, 
                            tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');

                    --
                    -- Now we can strip the spaces for the remaining
                    -- checks
                    --
                    l_clob := REPLACE(l_clob,' ');
                    IF l_dummy > 0 THEN
                        l_create := 'Y';
                    ELSE
                        l_loc := '075';
                        l_dummy := REGEXP_INSTR( l_clob, 
                            'MERGE[^;]*INTO[^;_]*'
                            ||NVL( tbl_dep_objs(j).syn_name, 
                                tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');
                        IF l_dummy > 0 THEN
                            l_create := 'Y';
                        END IF;
                    END IF;    
                    l_loc := '080';
                    l_dummy := REGEXP_INSTR( l_clob, 'SELECT[^;]*FROM[^;_]*'
                        ||NVL( tbl_dep_objs(j).syn_name, 
                            tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');
                    IF l_dummy > 0 THEN
                        l_read := 'Y';
                    END IF;    
                    l_loc := '090';
                    l_dummy := REGEXP_INSTR(l_clob, 
                        'UPDATE[^;_]*'
                        ||NVL( tbl_dep_objs(j).syn_name, 
                            tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');
                    IF l_dummy > 0 THEN
                        l_update := 'Y';
                    ELSE
                        l_loc := '095';
                        l_dummy := REGEXP_INSTR( l_clob, 
                            'MERGEINTO[^;_]*'
                            ||NVL( tbl_dep_objs(j).syn_name, 
                                tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');
                        IF l_dummy > 0 THEN
                            l_update := 'Y';
                        END IF;
                    END IF;    
                    l_loc := '100';
                    l_dummy := REGEXP_INSTR(l_clob, 
                        'DELETE[^;_]*'
                        ||NVL( tbl_dep_objs(j).syn_name, 
                            tbl_tabs(i).table_name)||'[^_]',1,1,0,'i');
                    IF l_dummy > 0 THEN
                        l_delete := 'Y';
                    END IF;    

                    --
                    -- Finally, if we haven't found a CRUD, the fact there is a
                    -- dependency means it's going to be a read ( even if it's
                    -- just a %type reference to one of the tables columns).
                    --
                    l_loc := '110';
                    IF l_create = 'N' AND l_read = 'N' AND l_update = 'N'
                        AND l_delete = 'N'
                    THEN
                        l_read := 'Y';
                    END IF;
                END IF;
                l_loc := '120';

                --
                -- Now add the result to the table / update the record in the
                -- table as appropriate
                --
                MERGE INTO db_crud
                USING dual
                ON (
                    table_owner = tbl_tabs(i).table_owner
                    AND table_name = tbl_tabs(i).table_name
                    AND object_owner = tbl_dep_objs(j).dep_owner
                    AND object_name = tbl_dep_objs(j).dep_name
                    AND object_type = tbl_dep_objs(j).dep_type)
                WHEN MATCHED THEN
                    UPDATE
                        SET create_flag = l_create,
                            read_flag = l_read,
                            update_flag = l_update,
                            delete_flag = l_delete
                WHEN NOT MATCHED THEN
                    INSERT (table_owner, table_name, object_owner,
                        object_name, object_type, create_flag,
                        read_flag, update_flag, delete_flag)
                    VALUES( tbl_tabs(i).table_owner, tbl_tabs(i).table_name,
                        tbl_dep_objs(j).dep_owner, tbl_dep_objs(j).dep_name,
                        tbl_dep_objs(j).dep_type, l_create, l_read, l_update,
                        l_delete);
            END LOOP; -- dependent objects loop
        END IF; -- count of dependent objects

        $IF $$debug $THEN
            l_end := DBMS_UTILITY.GET_TIME;
            l_elapsed := (l_end - l_start) /100;
            DBMS_OUTPUT.PUT_LINE('Total time for table '
                ||tbl_tabs(i).table_owner||'.'||tbl_tabs(i).table_name
                ||' - '||l_elapsed||' seconds.');
        $END    

        l_loc := '130';
        IF MOD( i, 10) = 0 THEN
            DBMS_APPLICATION_INFO.SET_ACTION(i||' tables of '||tbl_tabs.COUNT
                ||' processed.');
        END IF;            
    END LOOP; -- tables loop

    --
    -- Finally, update the crud_run_log table and the ACTION value
    -- to show that the procedure has completed
    -- 
    l_loc := '140';
    UPDATE crud_run_log
    SET last_run_date = SYSDATE;
    IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO crud_run_log VALUES( SYSDATE);
    END IF;        
    COMMIT;
    DBMS_APPLICATION_INFO.SET_ACTION('Completed');

EXCEPTION WHEN e_first_run THEN
    RAISE_APPLICATION_ERROR(-20001,
        'No last run information for CRUD. Run with pa_refresh set to Y.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error at location '||l_loc);
        DBMS_OUTPUT.PUT_LINE('Object is '||l_owner||'.'||l_name||' type - '||l_type);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        RAISE;
END;
/

Now we just need to run it. Still connected as crud_owner :

set timing on
BEGIN
    crud( pa_refresh => 'Y');
END;
/

The runtime will obviously depend on how many tables are being processed and how many dependencies there are on each of those tables.
In my experience, this can be anything from a couple of seconds ( on an XE database) to around 15 minutes on a large database ( 850 table, 1200 stored program units).
If you want to monitor progress whilst the procedure is running, you can find the session id before executing the procedure.

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')

--------------------
		1346

Then, once it’s running, you can open another session and check progress :

SELECT module, action
FROM gv$session
WHERE audsid = 1346
/

Running in Debug

If you want to get a bit more information, you can take activate the Conditional Compilation blocks in the code by compiling with the appropriate flags set. If we take one of these blocks as an example :

$IF $$debug $THEN    
            DBMS_OUTPUT.PUT_LINE('dependent_objs count for '
                ||tbl_tabs(i).table_owner||'.'||tbl_tabs(i).table_name
                ||' = '||tbl_dep_objs.COUNT);        
            l_start := DBMS_UTILITY.GET_TIME;
        $END     

The IF statement ( or $IF statement) is looking for a variable called debug being set to true. You can activate this code as follows :

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug:TRUE'
  2  /

Session altered.

SQL> ALTER PROCEDURE crud COMPILE
  2  /

Procedure altered.

SQL> 

Now we’ll get the debug lines out when we run the procedure :

set serveroutput on size 1000000
set timing on
spool crud.log
BEGIN
    crud( pa_refresh => 'Y');
END;
/
spool off

If you look in the crud.log file we’ve just written the output to, you’ll find entries such as :

dependent_objs count for HR.EMPLOYEES = 3                                       
Total time for table HR.EMPLOYEES - 0 seconds.                                  

Running an incremental update

If you want to just capture any changes since the last run, without doing the whole lot again…simply pass the refresh parameter a value of ‘N’ and the procedure will just look for stuff with a LAST_DDL_TIME greater than the last timestamp in crud_run_log.
For example, let’s now create a new function in the HR schema :

CREATE OR REPLACE FUNCTION get_dname( p_dept_id IN NUMBER)
    RETURN VARCHAR2 IS
    l_return departments.department_name%TYPE;
BEGIN
    SELECT department_name
    INTO l_return
    FROM departments
    WHERE department_id = p_dept_id;
    RETURN l_return;
END;
/

If we now check, we can see that a dependency has been created since the last time we ran the crud procedure :

SELECT dep.owner, dep.name, dep.type
FROM dba_dependencies dep, dba_objects obj
WHERE dep.owner = obj.owner
AND dep.name = obj.object_name
AND dep.type = obj.object_type
AND dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
  'VIEW', 'MATERIALIZED VIEW')
AND dep.referenced_owner = 'HR'
AND dep.referenced_name = 'DEPARTMENTS'
AND obj.last_ddl_time > ( select last_run_date from crud_owner.crud_run_log)
/

OWNER	   NAME 		TYPE
---------- -------------------- --------------------
HR	   GET_DNAME		FUNCTION

At this point in time, we can see that this dependency has not been captured in our crud matrix :

select object_owner, object_name, object_type
from crud_owner.db_crud
where table_owner = 'HR'
and table_name = 'DEPARTMENTS'
/

OBJECT_OWN OBJECT_NAME		OBJECT_TYP
---------- -------------------- ----------
HR	   EMP_DETAILS_VIEW	VIEW

SQL> 

Now, if we run the crud matrix again ( remember, the refresh parameter is set to ‘N’ by default)…

SQL> BEGIN
  2  	crud;
  3  END;
  4  /

PL/SQL procedure successfully completed.

And now check the crud matrix…

select object_owner, object_name, object_type
from crud_owner.db_crud
where table_owner = 'HR'
and table_name = 'DEPARTMENTS'
/

OBJECT_OWN OBJECT_NAME		OBJECT_TYP
---------- -------------------- ----------
HR	   EMP_DETAILS_VIEW	VIEW
HR	   GET_DNAME		FUNCTION

SQL> 

Adding the Extension to SQLDeveloper

The final step is to add a new tab to SQLDeveloper, showing the CRUD details for each table.
The first step is to create the crud.xml file, which looks like this :

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[CRUD]]></title>
    <query>
        <sql>
            <![CDATA[SELECT object_owner, object_name, object_type, create_flag, read_flag, update_flag, delete_flag
                    FROM crud_owner.db_crud
                    WHERE table_owner = :OBJECT_OWNER
                    AND table_name = :OBJECT_NAME
                    ORDER BY object_name, object_type, object_owner]]>
        </sql>
    </query>
    </item>
</items>

Now we need to add the user-defined extension to SQLDeveloper.

Open SQLDeveloper and select the Tools/Preferences menu.
In the tree on the left of the dialog box, open the Database node and select User Defined Extensions.

Defining a new User Defined Extension in SQLDeveloper

Click the Add Row button.
Click in the Type field and select Editor from the drop down.
Click Browse and select the crud.xml file
Click Save, then Click OK.

Enter the location of the xml file

Now re-start SQLDeveloper, when you go back in, you should see a new tab in the tables view …

CRUD, glorious CRUD !

I did wonder whether I should put this onto SourceForge, but, given the distinct absence of comments from my first post, I suspect it’s not exactly widely used and to do so would therefore be something akin to vanity publishing.
If you do feel inclined to give it a whirl please let me know how you get on.
Right now, I’m going to get back to the cricket. England seem to be beating everyone they should lose to and losing to everyone they should beat. At least it’s not dull !