Matching Your Database to Your Application

I’ve just been troubleshooting an ADF application that ran fine on one environment and not on another. After some searching, I discovered that a script had not been run on one of the environments so the database was different.

That reminded me of a simple database check that I often include in my applications: I simply calculate a hash value of all tables and views with an SQL statement like this:

select sum(
ora_hash(table_name)
+ ora_hash(column_name)
+ ora_hash(data_type)
+ ora_hash(data_length)
+ ora_hash(data_precision)
+ ora_hash(data_scale)
)
from   user_tab_columns
where table_name != 'PS_TXN';

I’m explicitly exclusing the PS_TXN table, because ADF might create this table to store application module passivations.

Every time the database changes, I calculate this simple checksum and store it in a constant in my application. At a central place in my application, I can then run the SQL and compare to the constant. If they don’t match, I’ll write a warning to the log. I don’t want the application to fail if my database does not match, but I do want to get a hint to check the database structure.

I place this check in the prepareSession() method in the Impl class for my root application module(s).

 

If you are working with ADF Development, I encourage you to sign up for my free ADF Mastery newsletter.