PL/SQL Unit testing in the builds

At the OTN Dev Day in Dallas last week, I asked how many people unit tested PL/SQL.  The response was a kind of group chuckle acknowledging it should be done but isn't being done.  This was not really surprising since it's the same response I get everywhere I ask.  The difference is that in this event the attendees did a hands on lab and made tests.  The exact same lab that is in the Oracle Learning Library.  After doing the lab people realized how easy it actually is to test their PL/SQL.

Now what's not in the lab is how to schedule them for an automated nightly run or as a part of a system build out.  For that, there is a command line to kick off a test.  In the sqldeveloper/bin, there's a ututil.sh and ututil.bat.  Simply run these and you get the syntax.

sqldeveloper/bin$ ./ututil.sh 

Oracle SQL Developer
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved. 

ututil -run ?
ututil -exp ?
ututil -imp ?

There's 3 choices. Export a test to an xml file, Import the test from the xml file, and Run the test. I'll focus on the run option.

./ututil.sh -run -?

Oracle SQL Developer
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved. 

ututil -run -test (-id <id>|-name <name>} -repo <connection name>
  -db <connection name>
ututil -run -suite (-id <id>|-name <name>} -repo <connection name>
  -db <connection name> 

 Since there's a command line, it can be integrated into existing build procedures very quickly.  There's a few switches needed to run the test or suite of tests.

First, the obvious one is of course the test or suite of tests.

Second, the what repository to connect to for the tests.  All tests are stored in a central repository.  This allows  developers or QA or any group to create and manage the tests in a central place.  The repository is also where all results are stored.

Third, what database to target for the running of the tests.  With all the tests in a central repository, they can be run against any database.  This allows the tests to run with ZERO setup on any targeted database.  All you need is a login and the testing can begin.  The results are also stored in the central repository, test runs against various systems can be compared.  Which makes it very easy to see what tests work on which systems and not on others.  This is useful for the cases where some development code works properly yet when deployed to an integration environment it does not.  The central repository can also make it easy to compare timings of the test runs.  For example, something I've heard many times is on a development system with limited data code runs very fast yet when deployed against a larger dataset it is less than desirable speeds.

OTN Developer Day VM March '11 Edition

The Database VM was just updated.  You can get it here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Quick reminder that this VM is only-a-sandbox.  All passwords are oracle, for that an other reasons this is never to be used for any purposed other than a sandbox.

The main changes in this VM are:
- New OBEs!
- Upgraded DB to 11.2.0.2
- SQL Developer 3.0 EA3
- Application Express Listener 1.1
- Application Express 4.0.2
- Times Ten 11.2


Also, Todd suggested I outline the steps to create this VM incase other wanted to make a custom one.  These are my steps for this.  If anyone has better suggestions, please let me know and I can use that in the next revamp.


1) Use Default wizards for new VM with 1 drive for the OS.

2) Install OEL

3) Remove some things like printing to save space.

4) Install the VirtualBox tools

5) Create a 2nd harddrive for the database install.  I mount this new drive to /home/oracle.  Then add the oracle user with that has his home directory.

6) Next clean up some space before exporting the VM by running these commands:
    dd if=dev/zero of=/bigemptyfile
    dd if=dev/zero of=/oracle_mount/bigemptyfile
- This zeros out the unused space which I've found helps with the export sizes.

7) Remove those files.

8) Shutdown and Export the appliance.

9) Share with anyone who'd like it.

After this the result is what you see on OTN, a 4.3G download that is everything setup and configured.  The best thing about this is that the VM is a sandbox so when it's messed up for any reason.  Delete and Reimport the VM and it's back to square one.

Application Express Views - Key discovery part 2

Yesterday, I showed how the new Foreign Key discovery works on a subset of dba views.  The only catch was that the end result had T_DBA_  prefixes for the names.  It's really easy to change that.

Once you import the views and convert them to tables, delete the views.  What you are left with is a bunch of tables prefixed with T_ .  Now right click on the relational model in the tree and choose Change Object Names Prefix.

This is a simple dialog, much like a search and replace.  Enter the old, the new, and what object types it applies to and everything is back to the base names you'd expect in a diagram.

The end result is a model of all the APEX_ views for 4.0.

Oracle Application Express Views

Data Dictionary Posters and Automatic FK discovery

We have all had posters on the wall at some point that are the data dictionary.  That is if you made it to the booths before they ran out at Oracle Open World or other events.  There's a few new features in the FREE Oracle SQL Developer Data Modeler that went production this week that can help avoid the need for the posters.  

First we have to import from the data dictionary, the data dictionary.

I'm filtering to just the DBA_T* views.  This filter is feature #1 that helps in prior releases it was individually selecting the objects to import, select all, and select none.  This helps to narrow down and then do a select all to the views you want to see.

Now that we have the views imported, the next step is to convert the views to tables, Feature #2.  Since views don't have FKs we swap them over to tables.  The end result of this wizard will create tables all prefixed with T_  .  This is done because the views remain in the model.  There's ways around this like bulk renaming the views from DBA_ to V_DBA_ then the tables could take the base name but that's for another day/blog.

Now that we have tables, we just have to put a primary key on the main driving table.  In this case, tablespace_name.

At this point we have a model which has a few tables and only one of which has a primary key.  This is the same place we could be if we import some tables from the first step in the import wizard.

Now for the main event , feature #3,  right click on the relational model in the menu and choose "Discover Foreign Keys"

You will get a list of the foreign keys that have been found. Click Ok.

The end result is a model of the data dictionary on the subset of views/tables with the proper lines connecting.

Modeler Custom Transformations

This is a follow up to the transformation I mentioned earlier where I mentioned how to write custom transformation in javascript for the design.  This is based on a java's pluggable script which means almost anything can be plugged in instead of javascript.  See here for details on the available languages.

This is the beginning of what I plan on building up over time which is a library of functions for use to make custom script very easily.  This is an example of adding the standard who columns to every table in the model.    

The first two functions are to delete and add columns to a table.

// import actual java classes for use later
importPackage(javax.swing);
// variable to keep a status message for later
var msg="";
/*
  Delete function takes in the table and name of the column to delete
*/

function deleteColumn(table,colName){ 
    columns = table.getElements();
 
 // iterate columns looking for the one to remove
    for (var i = 0; i < columns.length; i++) {
 if ( columns[i].getName().toUpperCase() == colName ){
   columns[i].remove();
   msg += "Deleted from "+ table.getName() + " : " + colName + "\n"; 
        }
  }
}
/*
  checkOrCreate function takes in the table and name of the column to add. This checks for the existence of the column before addin
*/

function checkOrCreate(table,colName,typeName,typeSize){ 
 hasCol = false;
 columns = table.getElements();
 
 for (var i = 0; i < columns.length; i++) {
 column = columns[i];
 if ( column.getName().toUpperCase() == colName ){
   hasCol=true;
 }
 }
  // if the column is not present add it
  if (! hasCol ) {
      newCol = table.createColumn();
      newCol.setName(colName);
      newCol.setUse(1);
   // lookup the logical datatype based on the name
   type = model.getDesign().getLogicalDatatypeSet().getLogTypeByName(typeName);
      newCol.setLogicalDatatype(type);
      if (typeSize != null  ) {
        newCol.setDataTypeParameter("size",typeSize);
      }
      msg += "Added to "+ table.getName() + " : " + colName+ "\n";
 }  
}

// grab all the table in the model as an array
tables = model.getTableSet().toArray();

for (var t = 0; t < tables.length;t++){
 // remove all these from the table
 deleteColumn(tables[t],"CREATED_BY");
 deleteColumn(tables[t],"CREATED_ON");
 deleteColumn(tables[t],"UPDATED_BY");
 deleteColumn(tables[t],"UPDATED_ON");
 
 // add them back with the specified datatypes
 checkOrCreate(tables[t],"CREATED_BY",   "VARCHAR",200);
 checkOrCreate(tables[t],"CREATED_ON",   "DATE");
 checkOrCreate(tables[t],"UPDATED_BY",   "VARCHAR",200);
 checkOrCreate(tables[t],"UPDATED_ON",   "DATE");
}

// notify the user what happened
JOptionPane.showMessageDialog(null, msg);

The end result of running this will be a dialog that shows all the actions performed.