Reverse Engineering a CRUD Matrix in Oracle (again) – with CRUDO

Dependencies between a table and database stored program units can be found in the DBA_DEPENDENCIES dictionary view. However, this only records the fact that there is a dependency.
Say you have a long-running report that will benefit from an index on a table. It would be good to know if there’s a packaged procedure that does a large update on that table and may therefore take longer with a new index to populate. How can you figure out if there are any such procedures ?
Well, you’re in luck.

CRUDO is not, as you may think, a domestic cleaning product. It is, in fact, a PL/SQL application for generating CRUD matrices for Stored Program Units against the tables that they reference.
I’ve been playing around with something like this for a while now, and
this time, I’ve posted the resulting code on GitHub.

CRUDO enables you to :

  • Determine a CRUD Matrix for each database stored program unit against a given table ( or synonym on that table)
  • generate Matrices for all tables in a schema
  • record the results in a table for fast querying
  • update existing records only for program units that have been changed since the last update of it’s record
  • specify override records for situations where the dependency may not be recorded in DBA_DEPENDENCIES(e.g. when using dynamic SQL statements)
  • view runtime information on what it is doing
  • amend logging levels to provide more detailed information on large runs
  • laugh at my inability to come up with a decent name for it

I developed CRUDO on Oracle 11gR2 and it will run on any Oracle Database Edition ( XE, Standard One, Standard, Enterprise).

Installing CRUDO

First thing to do is to get CRUDO from Github.
Head over to the CRUDO Github repository and click the Download ZIP button.

gh_download

Congratulations, you are now the proud owner of a file called crudo-master.zip.

Extract this into a location of your choosing.

Full installation instructions are included in the installation folder.
You can either install into an existing schema, or – the default option – you can create a new schema called CRUDO.
The examples that follow assume that you have done the latter.

You can create the CRUDO schema by running the script provied.
From the installation directory, connect to the database as a user with CREATE USER privileges and run…

@scripts/crudo_owner.sql

You will be prompted for :

  • The name of the user you want to create ( CRUDO by default)
  • The default tablespace for the new user ( default is USERS)
  • The temporary tablespace for the new user ( default is TEMP)

If you want to use any of the defaults, just hit return at the appropriate prompt.

The output of the script will look something like this :

SQL> @scripts/crudo_owner.sql
Enter the name of the new crudo Application Owner schema [crudo] : 
Default tablespace for the new schema [users] : 
Temporary tablespace for the new schem [temp] : 
Enter a password for the new schema [] : 

User created.


Grant succeeded.


User altered.

SQL> 

If you want to check that the user has been created as expected, well, there’s a script for that as well.
Connect as the newly created schema (in this case, CRUDO) and…

@scripts/pre_install_check.sql
SQL> @scripts/pre_install_check
Pre-requisite checks complete.

PL/SQL procedure successfully completed.

SQL> 

Now to install the database components that comprise the application.
Still in the installation directory, connect to the database as the application owner (crudo in this example) and…

@crudo_deploy.sql

Running this script will generate a log file in the same directory. If all goes well, it should look something like this :


Creating tables
===============
APPLICATION_LOGS

Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.

CRUD_MATRICES

Table created.


Table altered.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.

Creating Packages
=================
LOGS

Package created.


Package body created.

SEARCH_CODE

Package created.


Package body created.

GENERATE_MATRICES

Package created.


Package body created.

Deployment completed.

Optional Step – Installing the SQLDeveloper Extensions

CRUDO is a PL/SQL application. This means that all of the functionality is available via it’s PL/SQL packages.
We’ll look at these in detail later on.
If you are a SQLDeveloper user, then you may want to install the SQLDeveloper Extensions that come with the application to make life just that little bit more, well, GUI.

The Extensions are written in XML ( no Java). They can be added to SQLDeveloper in the usual way, namely…

  1. Open SQLDeveloper, go to the Tools menu and select Preferences
  2. Expand the Database node and select User Defined Extensions. It should look something like this :
  3. ude_dialog

  4. Click the Add Row button.
  5. In the new row that’s created, click in the Type column and select EDITOR from the drop-down
  6. add_tab1

  7. In the new row, click in the Location column and choose the crud_tab.xml file. You can find this file in the sqldev_extensions folder.
  8. add_tab2

    Next, add the Context Menu.

  9. Click the Add Row button.
  10. In the new row that’s created, click in the Type column and select ACTION from the drop-down
  11. add_action.png

  12. In the new row, click in the Location column and choose the crud_table_action.xml file. You can find this file in the sqldev_extensions folder.
  13. Cick OK.
  14. Re-start SQLDeveloper.

You should now see the CRUD MATRIX tab on the Tables view :

sqld_tab

If you right-click on a table, you should see the CRUDO Table Menu :

sql_menu

The Database Components

Whether you’ve decided to install the SQLDeveloper extensions or not, you will now have a database schema which contains the following tables :

  • CRUD_MATRICES – the table that holds all of the CRUD_MATRICES
  • APPLICATION_LOGS – table that holds any log messages generated by the application

…and the following packages…

  • GENERATE_MATRICES – containing all of the publicly called procedures and functions for the application
  • SEARCH_CODE – called from GENERATE_MATRICES to find DML statements in the source code
  • LOGS – used to manage entries to the APPLICATION_LOGS table

Granting Access to other users

A script is provided to grant access to the application to database users.
To run this, from the installation directory, connect as either the application owner (CRUDO in our case), or a user with GRANT ANY privileges and…

@scripts/grants.sql

You will be prompted for the application owner ( defaults to CRUDO) and the name of the user you’re granting access to. For example, if I want to grant access to MIKE it would look like this :

SQL> @scripts/grants.sql
Enter the the Application Owning Schema [CRUDO] :
Enter the name of the user to grant access to : mike
old   1: grant execute on &&app_owner..generate_matrices to &app_user
new   1: grant execute on CRUDO.generate_matrices to mike

Grant succeeded.

old   1: grant select on &&app_owner..crud_matrices to &app_user
new   1: grant select on CRUDO.crud_matrices to mike

Grant succeeded.

old   1: grant select on &&app_owner..application_logs to &app_user
new   1: grant select on CRUDO.application_logs to mike

Grant succeeded.

Grants completed.
SQL> 

Now you’ve got everything setup it’s time to…

Generating your first CRUD

In the following examples, I’m connected as MIKE. I’m going to be working on the HR schema.

First up, we’re going to generate matrices for the HR.EMPLOYEES table. There are two ways to do this…

Generating from a PL/SQL block

begin
    crudo.generate_matrices.crud_table('HR', 'EMPLOYEES');
    commit;
end;
/

Generating From SQLDeveloper

If you’re using the SQLDeveloper Extensions…

In the Object Tree, navigate to Other Users and find HR.
Expand the HR node, then the Tables and right-click on EMPLOYEES.

In the context menu that pops up, click on CRUDO Table Menu then CRUD Table.

You should now see this :

crud_tab_dialog

You’ll notice that the Owner and Table Name are displayed for information and are not enterable.
You’ll also notice that there is a third parameter – Refresh Type.

By default this is set to DELTA.

I’ll cover the REFRESH TYPE option a bit later. For now though, just accept the defaults and click Apply.

NOTE – in SQLDeveloper, the Menu Options run in an Autonomous transaction and commit automatically. This is done to ensure that they do not interfere with any other transactions that may be ongoing in your current SQLDeveloper session.

Viewing the Crud Matrices

Whatever method you’ve used, you should now be able to see the matrices we’ve generated for employees. In SQL*Plus :

select object_owner, object_name, object_type,
    create_flag, read_flag, update_flag, delete_flag,
    last_updated, last_updated_user
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'EMPLOYEES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     C R U D LAST_UPDATED	LAST_UPDATED_USER
------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------
HR			       EMP_DETAILS_VIEW 	      VIEW			     N Y N N 16-OCT-15		MIKE
HR			       SECURE_EMPLOYEES 	      TRIGGER			     Y N Y Y 16-OCT-15		MIKE
HR			       UPDATE_JOB_HISTORY	      TRIGGER			     N N Y N 16-OCT-15		MIKE

3 rows selected.

Alternatively, in SQLDeveloper, simply navigate to the table as before.
You’ll notice that there is an now some data on the CRUD Matrix tab…

crud_tab_display

However you view the output, you should see entries for all dependent objects that are not wrapped.

Generating matrices one table at a time may get a bit tedious. Fortunately, CRUDO allows you to…

Generate Matrices for all tables in a schema

As with the CRUD_TABLE procedure, CRUD_SCHEMA also has an optional REFRESH_TYPE parameter which is also set to DELTA by default.
the length of time this procedure runs for will depend on a number of factors such as the number of tables in the schema and the number, length and type of program units that are dependent on them.
Therefore, it’s not necessarily suited to interactive running and so there is no SQLDeveloper menu option for this.

Additionally, whilst the procedure will update the ACTION column in V$SESSION, you may want a bit more detail about what it’s doing.
You can get this by setting the Application’s logging level.

CRUDO has three logging levels :

  • ERROR – only errors are written to the APPLICATION_LOGS table ( the default)
  • INFO – Errors and Information messages are written to the table
  • DEBUG – All messages are written to the table

To find out what the log setting is for the current session :

select crudo.generate_matrices.get_log_level
from dual
/

If we’re running the schema crud, we may want to set the level to INFO :

begin
    crudo.generate_matrices.set_log_level('INFO');
end;
/

One other setting you may want to play around with is the BULK_COLLECT_LIMIT. This dictates the bulk collect limit used when processing objects dependent on a table. To check the current limit :

select crudo.generate_matrices.get_bulk_collect_limit 
from dual
/

To change the limit for the current session :

begin
    crudo.generate_matrices.set_bulk_collect_limit(500);
end;
/

To generate matrices for all tables in a schema, you can run…

begin
    crudo.generate_matrices.crud_schema('HR');
    commit;
end;
/

Once this has completed, we should see records in the CRUD_DETAILS table for all tables in the HR schema :

select distinct table_name
from crudo.crud_matrices
where table_owner = 'HR'
/

TABLE_NAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

Before I go too much further, I should probably explain…

Refresh Types

By default CRUDO will only re-examine program units that have a last_ddl_time later than the crud records were last created.
It will however, remove records for any program unit that has been dropped since it was last run.
If you want to ensure that all program units are processed, irrespective of when they were last changed, you specify a Refresh Type of FULL.

To demonstrate how this works, let’s start by making a note of the time at which our CRUD_MATRICES records were generated for the COUNTRIES table :

select object_owner, object_name, object_type,
    to_char(last_updated, 'HH24:MI')
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'COUNTRIES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     TO_CH
------------------------------ ------------------------------ ------------------------------ -----
HR			       EMP_DETAILS_VIEW 	      VIEW			     13:11

1 row selected.

Now, let’s create the following function (as the HR user), which reads the COUNTRIES table :

create or replace function get_country_name( i_country_id in countries.country_id%type)
    return countries.country_name%type
is
    l_rtn countries.country_name%type;
begin
    select country_name into l_rtn
    from countries
    where country_id = upper(i_country_id);
    
    return l_rtn;
exception when no_data_found then
    raise_application_error( -20000, 'No record for this country id');
end;
/

If we now run a DELTA refresh ( the default, remember), either via SQLDeveloper or via PL/SQL…

begin
    crudo.generate_matrices.crud_table('HR', 'COUNTRIES');
    commit;
end;
/

…we can see that the new record has been added, but the pre-existing record has not been updated…

select object_owner, object_name, object_type,
    to_char(last_updated, 'HH24:MI')
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'COUNTRIES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     TO_CH
------------------------------ ------------------------------ ------------------------------ -----
HR			       EMP_DETAILS_VIEW 	      VIEW			     13:11
HR			       GET_COUNTRY_NAME 	      FUNCTION			     13:30

2 rows selected.

By contrast, if we were to run a FULL refresh, we can see that all of these records are overwritten. Using PL/SQL…

begin
    crudo.generate_matrices.crud_table
    (
        i_owner => 'HR',
        i_table_name => 'EMPLOYEES',
        i_refresh_type => 'FULL'
    );
    commit;
end;
/

In SQLDeveloper, you simply need to select FULL from the drop-down

full_tab_crud

If we now re-query the records, we can see that they have all been overwritten :

select object_owner, object_name, object_type,
    to_char(last_updated, 'HH24:MI')
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'EMPLOYEES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     TO_CH
------------------------------ ------------------------------ ------------------------------ -----
HR			       EMP_DETAILS_VIEW 	      VIEW			     18:35
HR			       SECURE_EMPLOYEES 	      TRIGGER			     18:35
HR			       UPDATE_JOB_HISTORY	      TRIGGER			     18:35


Generally then, a FULL refresh will re-calculate all of the matrices for a given table. There is one type of record however, where this does not apply…

Override CRUD records

Consider the following procedure :

create or replace procedure add_country( i_id in varchar2, i_name in varchar2, i_region_id in varchar2)
is
begin
    execute immediate 'insert into countries(country_id, country_name, region_id) values(:1, :2, :3)'
        using i_id, i_name, i_region_id;
end;
/

Because the only reference to the COUNTRIES table is in a dynamic SQL statement, no dependency between the procedure and the COUNTRIES table is recorded in the data dictionary…

select owner, name, type 
from all_dependencies
where referenced_owner = 'HR'
and referenced_name = 'COUNTRIES'
/

OWNER			       NAME			      TYPE
------------------------------ ------------------------------ ------------------
HR			       EMP_DETAILS_VIEW 	      VIEW
HR			       GET_COUNTRY_NAME 	      FUNCTION

Sure enough, if we run a crud against the table…

begin
    crudo.generate_matrices.crud_table( 'HR', 'COUNTRIES');
    commit;
end;
/

… we won’t have a record for this procedure…

select table_owner, table_name
from crudo.crud_matrices
where object_owner = 'HR'
and object_name = 'ADD_COUNTRY'
and object_type = 'PROCEDURE'
/

no rows selected

In order to make sure that this crud matrix is recorded, you can…

Creating an override record

In PL/SQL, you can do this as follows :

begin
    crudo.generate_matrices.set_override
    (
        i_table_owner => 'HR',
        i_table_name => 'COUNTRIES',
        i_object_owner => 'HR',
        i_object_name => 'ADD_COUNTRY',
        i_object_type => 'PROCEDURE',
        i_create => 'Y',
        i_read => 'N',
        i_update => 'N',
        i_delete => 'N'
    );
    commit;
end;
/

In SQLDeveloper, you can do this from the CRUDO Table Menu using the Add CRUD Override Record option.

When you select this option, you’ll see the following dialog box :

override1

In the dialog :

  • Enter the name of the program unit you want to create the record for ( note, this is not case sensitive)
  • select the program unit’s type from the drop-down
  • set the Create, Read, Update and Delete flags to match the CRUD of this program unit against the table

In our example the dialog box should now look something like this :

override2

Once you’re happy with what you’ve entered, click Apply.

We can now see the new record in the CRUD_DETAILS table :

select object_owner, object_name, object_type,
    create_flag, read_flag, update_flag, delete_flag,
    last_updated, last_updated_user,
    override_flag
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'COUNTRIES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     C R U D LAST_UPDATED	LAST_UPDATED_USER	       O
------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -
HR			       ADD_COUNTRY		      PROCEDURE 		     Y N N N 16-OCT-15		MIKE			       Y
HR			       EMP_DETAILS_VIEW 	      VIEW			     N Y N N 16-OCT-15		MIKE			       N
HR			       GET_COUNTRY_NAME 	      FUNCTION			     N Y N N 16-OCT-15		MIKE			       N

From this point on, anytime we run a CRUD against the COUNTRIES table, even if we specify a full refresh, the override record will remain untouched.
CRUDO is smart enough to know that it’s not as smart as you :-).

Removing an Override Record

Imagine time has moved on a bit and the developer has come to realise that using dynamic SQL in this procedure is, perhaps, not the best way to approach matters.
Instead, the procedure has been re-written like this :

create or replace procedure add_country( i_id in varchar2, i_name in varchar2, i_region_id in varchar2)
is
begin
    merge into countries 
        using dual
        on ( country_id = i_id)
        when matched then
            update
                set country_name = nvl(i_name,country_name),
                    region_id = nvl(i_region_id, region_id)
        when not matched then
            insert( country_id, country_name, region_id)
            values( i_id, i_name, i_region_id);
            
end;    
/

Notice that the CRUD on COUNTRIES will have changed as the procedure may now UPDATE, as well as INSERT.
Additionally, because we’re no longer using dynamic SQL, Oracle recognises that there is a dependency for this procedure on the countries table :

select referenced_owner, referenced_name
from all_dependencies
where owner = 'HR'
and name = 'ADD_COUNTRY'
and type = 'PROCEDURE'
and referenced_type = 'TABLE'
/

REFERENCED_OWNER		       REFERENCED_NAME
------------------------------ ------------------------------
HR			       COUNTRIES

Whilst CRUDO would be able to recognize and record this crud, it will not contradict the override record.
Therefore, we’ll need to remove it.

Once again, you can either do this in PL/SQL…

begin
    crudo.generate_matrices.remove_override
    (
        i_table_owner => 'HR',
        i_table_name => 'COUNTRIES',
        i_object_owner => 'HR',
        i_object_name => 'ADD_COUNTRY',
        i_object_type => 'PROCEDURE'
    );
    commit;
end;
/

…or use the CRUDO Table Menu in SQLDeveloper…

From the menu, select the Remove Crud Override Record.
You will see the following dialog :

remove1

Enter the object name and select the object type from the drop-down, then click Apply.

When you re-query the CRUD_MATRICES table, you’ll notice that the override record has not been removed, but it has been updated…

select object_owner, object_name, object_type,
    create_flag, read_flag, update_flag, delete_flag,
    last_updated, last_updated_user,
    override_flag
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'COUNTRIES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     C R U D LAST_UPDATED	LAST_UPDATED_USER	       O
------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -
HR			       ADD_COUNTRY		      PROCEDURE 		     Y N N N 01-JAN-70		MIKE			       N
HR			       EMP_DETAILS_VIEW 	      VIEW			     N Y N N 16-OCT-15		MIKE			       N
HR			       GET_COUNTRY_NAME 	      FUNCTION			     N Y N N 16-OCT-15		MIKE			       N

So, the last_updated date is now 01-JAN-1970 and the override_flag is set to N.
This means that, when we come to run a CRUD against this table, this record will be overwritten, irrespective of the Refresh Type we choose…

begin
    crudo.generate_matrices.crud_table('HR', 'COUNTRIES');
    commit;
end;
/

If we now check, we can see that the record has indeed been updated…

select object_owner, object_name, object_type,
    create_flag, read_flag, update_flag, delete_flag,
    last_updated, last_updated_user,
    override_flag
from crudo.crud_matrices
where table_owner = 'HR'
and table_name = 'COUNTRIES'
order by object_owner, object_name, object_type
/

OBJECT_OWNER		       OBJECT_NAME		      OBJECT_TYPE		     C R U D LAST_UPDATED	LAST_UPDATED_USER	       O
------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -
HR			       ADD_COUNTRY		      PROCEDURE 		     Y N Y N 16-OCT-15		MIKE			       N
HR			       EMP_DETAILS_VIEW 	      VIEW			     N Y N N 16-OCT-15		MIKE			       N
HR			       GET_COUNTRY_NAME 	      FUNCTION			     N Y N N 16-OCT-15		MIKE			       N

SQL>

That just about wraps it up for our wander through CRUDO ( I hope those shoes weren’t expensive !)
If you decide to give it a go, I’d be interested to hear what you think.

Advertisements

2 thoughts on “Reverse Engineering a CRUD Matrix in Oracle (again) – with CRUDO

  1. Wow Mike this is really impressive!

    Too bad we need to manually overcome limitations related to Oracle Dynamic SQL.
    It would be nice if Oracle could do this for us, when the SQL is a string in EXECUTE IMMEDIATE but table name is static part of the string.
    Anyway. this looks really impressive. Seems like you’ve been hard working recently šŸ™‚

    • Jacek,

      thanks for the positive feedback.
      I’m still kicking around ideas about how I can make this a bit more sensitive to Dynamic SQL statements. In the meantime, I’m trying to get up to speed with Ruby-PLSQL so I can put some decent unit testing code up šŸ™‚

      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