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

About these ads

13 thoughts on “Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

    • Corrie,

      just a quick note to say thanks for the comment. It’s nice to know I’m not just talking to myself :-P

      Mike

  1. Mike,

    I think there’s a bug in the REGEXP_INSTR. I have a table LOG_FORECAST_UNIT and a separate table UNIT. Your crud matrix for procedure X has the delete from = Y for both tables but there is only a delete statement for LOG_FORECAST_UNIT. I haven’t fully manually parsed your regular expression logic but I’m guessing it is ignoring the underscore. This same error seems to occur in all the REGEXP_INSTR evaluations but I’m just focusing on this one delete to make it simpler.

    Bob

  2. This is very nice but 1 comment – This gives only direct dependency of all objects to the table. What about following:

    Function 1 depends on Table A – This is captured

    1) Function 2 Calls Function 1 and thus indirectly dependent of Table A and need to be reported.
    2) Package X which has procedure Y that calls Function 2
    and this type of nested dependency needs to capture and reported to be complete.

    Any ideas on how to incorporate this changes ?

    • Karma,

      the dependency information you’re after is already in the data dictionary.

      To take an example :

      CREATE TABLE silly( silly1 varchar2(1));
      
      CREATE OR REPLACE FUNCTION silly_fn 
          RETURN VARCHAR2 AS 
          l_dummy varchar2(1);
      BEGIN
          SELECT silly1 INTO l_dummy 
          FROM silly;
          RETURN 'Hello'; 
      end;
      /
      
      CREATE OR REPLACE FUNCTION really_silly_fn 
          RETURN VARCHAR2 AS
      BEGIN
          RETURN silly_fn;
      end;
      /
      

      So, we have a function (SILLY_FN) which is dependent on a table (SILLY). We also have a function (REALLY_SILLY_FN) which is in turn dependent on SILLY_FN.

      To find this dependency, we can look in DBA_DEPENDENCIES using the table as our starting point :

      SELECT owner, name, type, level
      FROM dba_dependencies dep
      START WITH referenced_owner = 'DDDBA' 
          AND referenced_name = 'SILLY' 
          AND referenced_type = 'TABLE'
      CONNECT BY PRIOR owner = referenced_owner 
          AND PRIOR name = referenced_name 
          AND PRIOR type = referenced_type
      ORDER BY level
      /
      

      The result of this query is :

      
      OWNER	NAME			TYPE		LEVEL
      ------  --------------  --------    -----
      DDDBA	SILLY_FN		FUNCTION	1
      DDDBA	REALLY_SILLY_FN	FUNCTION	2
      

      HTH

      Mike

  3. It seems my problem is going to be filtering out false references generated as a result of packages. If I want to generate a CRUD matrix for application workflow X which invokes program unit A.1 in package A, I need to be able to filter out the CRUD generated for package A resulting from program units A.2, A.3 etc. which aren’t called by workflow X. Any thought given to this at all ?

    Thanks,

    • Rob,

      it gets a bit tricky for the simple reason that packages are treated as a single entity in the data dictionary.
      Searching through the package code programatically to isolate the individual procedures and functions would be problematic and subject to error.
      This is especially true if you do not include the program unit name in the END statement for a particualar procedure and you have nested blocks in the procedure.
      In the example you quote, the fact that workflow X invokes a member of package A would show up in the CRUD matrix.
      Yes, you do still have to do a bit of work to figure out which particular package member it is, but it does give you a starting point.

      Apologies for the delay in responding.

      HTH ( although probably not much),

      Mike

  4. Hi Mike

    It is really a nice stuff, thanks for all your effort.

    I have a got a chance to look at this link, when I am searching for something related to …
    Is there any way to get table level & index level CRUD details based on their access to figure out which tables & indexes are in use and not in-use, in a given period?.

    Indexes can be tracked usiing “Index usage” option (in Oracle), but again we need to enable this option for all the indexes, that too in production is quite difficult job.

    From my understanding, your CRUD matrix will provide all dependencies for a given (or all) tables and what kind of CRUD operation it perform on the table. How this will be extended to know, which tables are actually accessed for what operation and which is not all for a given duration. OR is there any other way to get these details?.

    Appreciate for your inputs.

    Thanks

    Bharathy

    • Bharathy,

      I think that your best bet is probably Auditing ( although I’m not sure if this covers indexes).
      There’s a couple of good articles on this topic on the Oracle Base site.
      10gR2 auditing is covered here.
      The 11g enhancements are here.

      If you’re looking at activity within a given timeframe then you may want to consider ASH/AWR capabilities ( provided of course that you have the appropriate licenses).

      HTH

      Mike

  5. Mike,

    Have you tested or received any feedback on how well this works with Dynamic SQL statements where the query is built across a number of lines of code?

    • NewEnglander,

      To this point, I’ve not found line breaks to cause a problem.
      As for Dynamic SQL however, this app takes the DBA_DEPENDENCIES tables as it’s starting point.
      Dependencies via dynamic sql statements are not recorded in this table.
      To demonstrate ( with apologies for the Dynamic Duo theme ) :

      create table catchphrases(
          phrase VARCHAR2(4000))
      /
      
      INSERT INTO catchphrases(phrase) VALUES('Holy dependencies, Batman !')
      /
      
      
      CREATE OR REPLACE FUNCTION get_phrase_fn 
          RETURN VARCHAR2 AS
          l_phrase VARCHAR2(4000);
      BEGIN
          EXECUTE IMMEDIATE 'SELECT phrase FROM catchphrases'
              INTO l_phrase;
          RETURN l_phrase;
      END;
      /
      

      If we now look at the dependencies recorded in the data dictionary :

       
      SQL> select name, type
        2  from user_dependencies
        3  where referenced_name = 'CATCHPHRASES'
        4  and referenced_type = 'TABLE';
      
      no rows selected
      

      Is there any common pattern to the Dynamic SQL you have in mind ? For example, are table names specified as literals in the source code ? Do you use Native Dynamic SQL exclusively (i.e. EXECUTE IMMEDIATE) or do you also have some DBMS_SQL statements ?

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s