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.

APEX Listener startup/shutdown script

David asked me for a way to keep the listener up and running when a box is rebooted.  There may very well be better ways to do this but this script can be used to start, stop, get status, and tail the log file.  Also it can be dropped in as an init.d script to have the Listener start when the box starts.  Since we don't have access to init.d for David's case, we are just going to put in a cron job to check status and start if not already going.  The start function checks to ensure it's not already running before trying to start it up.  You should be able to use this but just changing the location variables in the top of the file.

The one addition over a normal init.d is the log command I added ( which is very simple).  Just do ./listener.sh log and it will tail the log file.

#!/bin/sh
#
. /etc/rc.d/init.d/functions
NAME="Oracle Application Express Listener"
JAVA="/my/install/path/to/jdk/jre/bin/java"
APEXWAR="/my/install/path/to/apex_listener/apex.war"


OPTIONS="-Xmx1024m -Xms256m  -jar $APEXWAR"

LOGFILE=/tmp/apex_listener.log
PIDFILE=/tmp/apex_listener.pid
start() {
        echo -n "Starting $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                exit 2;
        else
                nohup $JAVA $OPTIONS 2>&1 > $LOGFILE  &
                RETVAL=$!
                echo Started PID: $RETVAL
                echo
                echo $RETVAL >>$PIDFILE
                return $RETVAL
        fi

}

status() {
        echo -n "Status $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                ps -ef | grep $PID
        else
                echo APEX Listener not running
        fi
}

stop() {
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo -n "Shutting down $NAME "
                echo
                kill $PID
                rm -f $PIDFILE
        else
                echo APEX Listener not running
        fi
        return 0
}

log() {
        tail -f $LOGFILE
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    status)
        status
        ;;
    restart)
        stop
        start
        ;;
    log)
        log
        ;;
    *)
        echo "Usage:  {start|stop|status|restart|log}"
        exit 1
        ;;
esac
exit $?