Using Database Reporting to Ensure Standards Compliance

In JDeveloper 11gR1 we introduced a SQL-like interface for reporting on your database models. Watch this demo to review the basic functionality. In the latest release we've added a set of pre-built reports. These reports not only get you started with reporting but also illustrate how you can use database reporting to ensure that the objects in your offline database model comply with your team's naming standards.

I say SQL-like interface because the database model you develop offline in JDeveloper is stored in XML. However, we took the decision that as most database developers are familiar with SQL it made perfect sense to provide a familiar way to interrogate the model. For instance, the pre-built report to list all tables with no Primary Key is:

SELECT
T.SCHEMA '.' T.NAME "Table"
FROM
DB_PKCONSTRAINTS C RIGHT JOIN DB_TABLES T ON C.PARENT_ID = T.ID
WHERE
C.NAME IS NULL

As you get more familiar with the meta-model you might write your own SQL queries, but there is also a declarative UI to help you traverse the model and develop the query you want.
Below is an image of the wizard, showing how the FROM clause for the above query is selected. You can also select the objects, apply different JOINs, add a WHERE, GROUP BY and aliases using the declarative UI.

Here is an example of a more complex SQL query - to report on any tables that do not have the audit column CREATION_DATE*

SELECT T.NAME FROM DB_TABLES T
WHERE NOT EXISTS
SELECT 1 FROM DB_COLUMNS C WHERE C.PARENT_ID = T.ID
AND C.NAME = 'CREATION_DATE')


Now the world is your reporting oyster - how about creating a series of reports in a Standards Project - and distributing that project to every team that is doing database development? Here is one approach:

1. Check the Standards Project out of source control into the required application
2. In the Standards Project open the Project Properties, in Project Source Paths select the Offline Database node
3. Add the projects containing your database model project(s) - see image below
   


4. Open each report (double-click) and select the offline database you want to run against
5. Go to the Publish tab and enter the details of how and where you want the report to be save. In the example below I've chosen to save the report in the project that contains the database - so my audit reports can be shown to represent my offline DB Model.
   
6. Use the context menu of the report format to Run the report. In my example the HTML version of the report (AuditTableCheck) is now stored in Project1 along with the database, the report format (Check_Audit_Tables) is stored in the Standards project along with the other report formats.
   

Let me know how you get on - it would be good to build up a list of reports written by you that could be shared with others.

*For a full example of using this in a tutorial see this Oracle By Example