Friday, September 21, 2018

DBMS_OUTPUT Reports in Oracle SQL Developer

Although I've been working a lot with SQL Server databases lately, I've never entirely left the Oracle world.  For instance, I've used SQL Developer Data Modeler (SDDM) to design my database, and exported my models to a reporting repository in an Oracle XE database.  What kind of reporting?  Well, for one thing, Oracle SQL Developer has a very good report generator built into it, and comes with a number of pre-written reports, including some for SDDM.  Here is the report window:

You can also build your own reports.  Have a look at Jeff Smith's blog (http://www.thatjeffsmith.com/) for lots of great information about SQL Developer and SDDM.  Specifically, look at the bottom of this page for links to Jeff's posts about the reporting capability: https://www.thatjeffsmith.com/sql-developer/.  One good way to build your own report is to copy one of the built-in reports and modify it.

But, what if I want something that none of the built-in reports has, or I want to do formatting that the reporting tools don't do natively?  For instance, what if you want to print a report something like this?
  
This is a data dictionary for the HR sample schema formatted according to my client's wishes for data dictionaries.  There are very good reports from the Oracle data dictionary that are built into SQL Developer, and I like the master child formatting they have where you can click on the table name and see information about the table's columns, but this is more of a report that you can print off if you need to, or read into an Excel spreadsheet.

No worries.  You can write your own report in PL/SQL to produce HTML that you output with the DBMS_OUTPUT built-in package.  Then in SQL Developer you create it as a PL/SQL DBMS_OUTPUT style report:

The query for this one is:
BEGIN
  DATA_DICTIONARY;
END;
So how did I write this?  You COULD just hand write the PL/SQL, but since this is going to produce HTML, I like to write it as a PL/SQL server page.  I've written extensively about the many uses for PL/SQL server pages (PSPs) before, for instance, see this about using a PSP to produce Spreadsheet ML: https://it.toolbox.com/blogs/jjflash/spreadsheetml-in-a-pl-sql-server-page-part-1-of-2-022015.

Basically, a PSP is an HTML page with some PL/SQL embedded in it.  The PL/SQL is used for flow control like LOOPs and to embed data from the database into the page.  You compile it with the LOADPSP utility that comes with all Oracle databases, including Oracle XE.  And the compiled PSP is actually a stored procedure that uses the PL/SQL Web Toolkit to write to the web - this is the same underlying technology as is used by Oracle Application Express - i.e. APEX.  The specific toolkit procedure the compiled PSP uses is HTP.PRN which outputs all of the HTML.   But we aren't going to write to the web, we want to write our HTML to DBMS_OUTPUT.

So what I did was compile my PSP with LOADPSP, then edited the resulting stored procedure, named DATA_DICTIONARY.  I made three changes:

  1. Since the underlying SQL uses the USER_* data dictionary views, I wanted to make sure it shows the tables owned by the user that executes it.  So I added "AUTHID CURRENT_USER" to the procedure definition.  That makes it an Invoker's Rights procedure.
  2. I did a FIND/REPLACE to change all occurrences of HTP.PRN to DBMS_OUTPUT.PUT.
  3. I found out that when you are writing to the DBMS_OUTPUT buffer, nothing gets written until there is a call to DBMS_OUTPUT.PUT_LINE;  So I added "DBMS_OUTPUT.PUT_LINE('');" as the last line of the procedure.
Recompiled, and it all worked. Almost.  It seems that SQL Developer ignores your <head> section, including any <style> section you might have put there.  There are several possible work-arounds, but what I advise is to put any styling that you want into "style" attributes in the HTML elements to be styled.  Yes, I know this is against HTML best practices, but what can you do?

So I hope this inspires you to try the SQL Developer reporting features.  And if the need arises, write your own reports your way.

Friday, September 14, 2018

SDDM Script to Create SQL Server Journal Tables

In my previous post, I talked about how Oracle SQL Developer Data Modeler (SDDM) is extendable with scripts written in languages that support JSR-223.  One of those languages is Groovy (http://www.groovy-lang.org/) and I showed how to add Groovy to the JVM classpath used by SDDM.  You might want to visit that post to see how.  The reason that I needed to write a script was that I wanted to easily add Journal Tables and the triggers for updating them to my database DDL.  SDDM actually comes with a script for doing this, but the script writes the triggers in PL/SQL for Oracle databases.  As my readers know, the database I was designing was a Microsoft SQL Server database, and the triggers needed to be written in Transact-SQL (T-SQL).

About Journal Tables

So, first of all, you might ask, is what is a Journal Table?  A Journal Table is a table that captures an before or after image of every change to a row in the table that is being monitored, usually with a date/time to show when the change occurred.  The idea is that we be able to reconstruct a history of changes to the data in a table.  Because of the overhead, you probably don't want a journal table behind all of your tables, but when you need to track who did it, when was it done, and what exactly was changed, a journal table can be a good solution.  It would also help to recover from changes that shouldn't have been done.  There are other solutions like Oracle's flashback query capabilities, but some databases can't do that, and some tables require a little more precise control. By the way, it is often necessary to set permissions on journal tables more stringently than permissions on the tables being journaled, so that hackers can't cover their tracks.

I personally prefer to make these before images, so that you can see what the row looked like before the change.  This would imply that you need before UPDATE and before DELETE triggers that INSERT the row of the table being journaled as it exists before the change to the journal table, but no before INSERT since before the INSERT the row didn't exist.  But my development lead wanted an after image, which is fine, since SQL Server only has AFTER statement triggers.  Here is an example of a table to be journaled, and a journal table:
CREATE TABLE my_table (
  my_id        INTEGER,
  my_char_data VARCHAR(30)
);
CREATE TABLE my_table_jn (
  my_id        INTEGER,
  my_char_data VARCHAR(30),
  operation    VARCHAR(10),
  date_changed DATETIME
);
If I do (on September 10):
INSERT INTO my_table (my_id, my_char_data)
   VALUES (1,'Example 1');
The after INSERT trigger should do:
INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)
VALUES (1, 'Example1','INSERT', CONVERT(datetime,'09/10/2018',101));
If I do (on September 12):
UPDATE my_table SET my_char_data = 'Example2'
  WHERE my_id = 1;
The after UPDATE trigger should do:
INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)
VALUES (1, 'Example2','UPDATE', CONVERT(datetime,'09/12/2018',101));
If I do (on September 14):
DELETE my_table
  WHERE my_id = 1;
The after DELETE trigger should do:
INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)
VALUES (1, 'Example2','DELETE', CONVERT(datetime,'09/14/2018',101));
The data for DELETE is actually a before image, since after the delete there is no data.  By the way, triggers participate in the underlying transaction, so if the change to my_table is rolled back, so will the INSERT into my_table_jn.

The Script

As I said before, SDDM includes a script for adding code for Journal Tables to your DDL.  Though I couldn't use Oracle's script as written, it served as an excellent starting point for my version.  It also shows how you get access to the underlying SDDM data.  Here is the first part of the script:
/*
Writes CREATE commands for Journal Table and Triggers for SQL Server.
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script - as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
- model - relational model instance
- pModel - physical model instance
- table - the table in relational model
- tableProxy - table definition in physical model 
 */
Since the original script is written in Javascript, and mine is written in Groovy, I needed to change the syntax to Groovy, but much is the same or similar, including comments. Notice that SDDM hands you access points to the SDDM data - listed in the comments above.  But it doesn't tell you how to write to SDDM's log.  Fortunately, Dave Schleis provided the following code:
// get a handle to the application object
def app = oracle.dbtools.crest.swingui.ApplicationView
app.log("Creating DDL for Journal table for ${table.name}");
"ddl" is a variable to hold the code to be added to the ddl being exported for the table to be journaled.  In the original, this was a string variable, but strings in Groovy are immutable.  When you do "ddl = ddl + 'a string'" you are really creating a new string object.  So I changed it to a StringBuilder, which in Groovy and Java is an object to which you can append more data, without the waste of discarding old strings and creating new ones.
StringBuilder ddl;
String lname;
//journal table name suffix 
jnTabSuf = "_jn";
// trigger name suffix
jnAISuf = "_jn_ai";
jnAUSuf = "_jn_au";
jnADSuf = "_jn_ad";
prompt = model.appView.settings.includePromptInDDL;
useSchema = model.appView.settings.isIncludeSchemaInDDL();
if(model.storageDesign.open){
    if(useSchema){
        lname = tableProxy.longName;
    }else{
        lname = tableProxy.name;
    }
}else{
    if(useSchema){
        lname = table.longName;
    }else{
        lname = table.name;
    }
}
Here you will see a major advantage of using Groovy for your DDL Transformation (and other) scripts.  Groovy has a GString type, similar to strings in Java and Javascript, but you can embed variables in your GStrings.  In other languages you would have to concatenate strings.  This is a great space and time saver when the script is really code that writes code.
if(prompt){
    ddl = new StringBuilder("PRINT 'Creating Journal Table for ${lname};'\n");
}else{
    ddl = new StringBuilder("");
}
app.log("Creating Journal Table DDL.");
Most of the rest of the code is appending strings (GStrings) to the ddl variable.  Groovy overloads the "append()" method of StringBuilder to the "<<" operator, once again saving me a little time and space. Also notice that I'm using the triple quoted string in this section of code, which lets me use actual line feeds in place of the "\n" line feed character.  I didn't do this throughout, because I didn't want to fool with the original Javascript code more than necessary.
ddl <<
"""CREATE TABLE ${lname}${jnTabSuf}
  (${table.name}${jnTabSuf}_id INT IDENTITY(1,1) NOT NULL
  ,operation VARCHAR(10) NOT NULL
""";
cols = table.elements;
Here, I'm looping through the columns from the original table and writing the journal table with the same columns.  I don't add the date_changed column, because my tables already have a last_update_date column.  They also have a last_update_user_id.  You could probably add code that says to add these to the journal table if they don't exist.
cols.each {
    ddl <<
    "  ,$it.name $it.datatypeString";
    if (it.mandatory){
        ddl << " NOT NULL\n";
    }else{
        ddl << "\n";
    }
}
/* Primary key is non-clustered because queries of the Journal table will
 * usually be by the parent table's key.
 */
ddl <<
    "  ,CONSTRAINT ${table.name}${jnTabSuf}_pk\n" +
    "     PRIMARY KEY NONCLUSTERED (${table.name}${jnTabSuf}_id)\n" +
    " );\n" +
    "GO\n\n" + 
/* So instead, we create a clustered index on the parent table's PK.
 * No Foreign key, because we may delete rows of the parent table and still
 * keep the journal of changes to the now-deleted rows, including the datetime of
 * deletion.
 */
"CREATE CLUSTERED INDEX ${table.name}${jnTabSuf}_fki\n" +
    "  ON ${table.name}${jnTabSuf} (${table.name}_id);\n" +
    "GO\n"

if(prompt){
    ddl << "\nPRINT 'Creating Journal Triggers for ${lname};'\n";
}else{
    ddl << "\n";
}
So far, my code has been similar to the original, but my triggers are very different - SQL Server triggers are not at all like Oracle triggers. Leave a comment, if you want me to write a post to compare the way it works in SQL Server with triggers to do it in Oracle.
app.log("Creating Journal Table After Insert trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnAISuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnAISuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER INSERT AS\n" +
  "BEGIN \n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    )\n" +
  "  SELECT 'INSERT' AS operation\n"
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    FROM inserted;\n" +
    "END;\n" +
    "GO\n\n";
The rest of the code is pretty similar.
app.log("Creating Journal Table After Update trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnAUSuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnAUSuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER UPDATE AS\n" +
  "BEGIN \n" +
  "  UPDATE t\n" +
  "      SET t.last_update_date = CURRENT_TIMESTAMP\n" +
  "    FROM ${lname} AS t\n" +
  "    INNER JOIN inserted AS i\n" +
  "       ON t.${table.name}_id = i.${table.name}_id;\n\n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
  "    )\n" +
  "  SELECT 'UPDATE' AS operation\n"
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    FROM inserted;\n" +
    "END;\n"+
    "GO\n\n"
app.log("Creating Journal Table After Delete trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnADSuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnADSuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER DELETE AS\n" +
  "BEGIN \n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
  "    )\n" +
  "  SELECT 'DELETE' AS operation\n"
cols.each {
    if (it.name == "last_update_date") {
ddl <<
    "    ,CURRENT_TIMESTAMP AS $it.name\n";  
    } else {
        ddl <<
    "    ,$it.name\n";  
    }
}
ddl <<
    "    FROM deleted;\n" +
    "END;\n"+
    "GO\n"
The last step is to add the ddl variable that I've been building to the DDL that will be exported.  Since my variable is a StringBuilder, not a String,we just need to use its toString method.
ddlStatementsList.add(ddl.toString());

So that's it.  You are welcome to cut and paste this into your own SDDM project.  Hope this was useful.