OTN Tech casts related to the DB VM image

There's going to be a pointer in the next version of the Database VM Image to some OTN Tech Casts.  This is list is from Todd in the OTN Team which anyone who knows Todd means this is no-nonsense straight with development.  I thought I should pass it along.  Here's the note from Todd:


A lot of different technologies and work has gone into the creation of this Database App Development VirtualBox image.

The selection criteria used was:

1. Technically relevant, not marketing
   
2. Interesting or enlightening
   
3. Pre-View, or a look behind the scenes of where dev and engineering are headed.
   

Most of the OTN TechCasts are 10-15min, the guests are all from development and engineering.

Database App Development :

• TechCast Live: Oracle Database Express Edition Goes to 11!
• TechCast Live Big Data - Exadata - and More
• TechCast Live Sqldev, Data Modeler, XE , Migration and More
• Apex 4.0
• TechCast Live: Embedded Data Storage with Oracle Berkeley DB
• TechCast Live: Oracle Berkeley DB and SQLite

Engineered Systems :

• Overview of the Oracle Database Appliance - Bob Thome
• TechCast Live Big Data - Exadata - and More

Oracle Linux :

• TechCast Live Ksplice, Oracle VM 3.0, Btrfs and DTrace on Linux
• TechCast Live: Linux and Virtualization with Wim Coekaerts

Virtualization :

• TechCast Live The Coolest Virtualization Products
• TechCast Live Ksplice, Oracle VM 3.0, Btrfs and DTrace on Linux
• TechCast Live: Inside the Sun Ray
• OTN TechCast - Desktop-to-Data Center Virtualization
• TechCast Live: Linux and Virtualization with Wim Coekaerts

Database Services in the Cloud :

• TechCast Live Introducing Oracle Public Cloud
• TechCast Live Managing the Cloud
• Overview of the Oracle Database Appliance - Bob Thome
• TechCast Live Inside the Oracle Database Cloud Service

Schema Diff - Part 2

After showing the new Schema Diff in SQL Developer 3.1 @ddelmoli asked about separate files per object diff instead of the monolith file of all changes.  In the next EA when it comes out, you'll get prompted for what kind of file(s) to make if any at all.  There's several new options

1. Single File - what's there now, 1 file with everything

Along with the 1 file per object is a generated file which runs all the files.
2. Separate Files - 1 file per object

3. Type Files - 1 file per type i.e. tables.sql,views.sql,...

4. Separate Directories - 1 directory for each object type with

5. Worksheet - A new worksheet is created and the sql is put in.

6. Clipboard - As it suggests, straight into the clipboard for pasting into anything else.

Please check out the exchange and log any requests you may have.  We do listen !

SQL Developer 3.1 EA1 - Drill Down Report

I got a question on twitter from @goryunov about how to do drill down reports.  I plan on doing a few posts on reports since so much of the dialogs have changed.  That make this the first of a few blogs on reports.

Step 1.  Right click on the User Defined Reports and choose New Report

Step 2. Create a "Departments" report by naming it and entering the SQL.  This dialog has drastically changed from what was in the previous versions.  More on that later.

Step 3. Create a "Employees By Department" report.  This report will use a bind variable from the Departments report which is the DEPARTMENT_ID column from the query.

Step 4. Now edit the Departments report and choose the Drill Down item in the left options.  Click "Add Report"  This makes a new row in the table.  The Name part is whatever name you like.  The second will be a drop list of all reports in the tool.  In this case, I scrolled down and choose my "Employees By Departments" report which is using the bind variable.

Step 5. That's all there is to it. Now when I run the Departments report there's a new menu option for all drillable reports defined in Step 4.

Step 6.  When I choose that menu option, the values require are passed along.

SQL Developer 3.1 EA1 - Schema Diff

It was great showing everyone last week at OOW what was coming.  The only thing better is giving people the software to start using.  Now that it's out, I'll do some blogging to get some of the new areas pointed out.

One of the major areas of work in SQL Developer 3.1 is the diff generation between 2 schemas.  This is completely rewritten from the ground up.  That's interesting but maybe more interesting to you is that it no longer requires the change management pack.  That means you are free to use this feature now without any worries of what is licensed and on which databases.

It's still in the normal place in the Tools menu.  The first screen now gives you more control over what you are going to want to consider is a difference.  For example, matching constraints based on the name or the definition.  If I have a check contraint in two databases both doing the same things but have different names, it will not be flagged if I choose contrants by definition.

At the end of the wizard, there is a new tree representation of the differences between the schemas.  The tree shows everything possible.  Unchecking the "Show Equal Objects" will restrict to just the ones with changes.  Then in the lower portion of the screen, you get the standard diff window that highlights the changes in the objects.

Clicking the script tab you'll see the alter statement.

The finally clicking the SQL icon in the toolbar will generate the script to sync the two connections.

Oracle Express Edition 11.2 is here!

Oracle XE 11.2 is now available on Windows 32 and Linux 64.  It can be downloaded here.  I'll be shortly putting together another VirtualBox image so it can be used on other platforms.  This is also a great database to try out the Learning Library labs on.

In case anyone doesn't know, Oracle Express Edition is the free database.  The limitation has gone up to 11.

3 Upcoming OTN Developer Days

There's a few more of the OTN Database Developer Day free training events coming up.

The first is next week in Reston,VA. This is the full four track event.  If you are able to make it, it's a great chance to talk to some of the development staff that run the event.  Mike Hichwa will be giving the keynote.

The agenda for this even can be found here: http://www.oracle.com/us/dm/h2fy11/77471-wwmk11054222mpp004c004-oem-452411.html?ssSourceSiteId=otnen


The second and the most widely available is going to be on September 13th at 9am Pacific.  This  will be our first virtual one version of the event we've been doing live for over a year.  The virtual event is great for people that can't make it to the live ones.  You can register here:
https://oracle.6connex.com/portal/database/login/?langR=en_US&mcc=SQLDevblogVDDDB9132011

The agenda for the day is here: https://oracle.6connex.com/upload/124/483/1314393443040_67_.pdf

The lastly is a Database only track coming up on September 20th in Calgary.  For this day, we are only running the 1 track and it's database focused.  There's Application Express, Times Ten, XML DB, and of course SQL Developer and Modeler.

The agenda for this even can be found here: http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=140137&src=7328803&src=7328803&Act=14


As always, all these events use the Database App Development VM. If you've not heard of it before, it's a VM that we build which is preconfigured with all the software and material for most of the labs ( minus .NET and jdev )  It's an 11.2.0.2 EE version of the database with Application Express 4 ( yes we're updating it ), SQL Devleoper and Modeler 3, Times Ten 11r2, the APEX Listener, and all the labs themselves.  Even if you can't make one of the events give the labs a try yourself but getting the VM from here:  http://www.oracle.com/technetwork/community/developer-vm/index.html

Apex Listener + WLS + Serving static files from /i/

A common thing that people ask about is setting up the images directory in weblogic. The documentation explains how to generate an i.war file then deploy it.  This work great until the need arises to also host additional files like custom javascript or css.  This is a short video of how I setup the /i/ for my servers.  This setup lets any content in the directory be served out.  The one assumption is that you already have web logic setup and running.  In my case, the http server is on port 8080.  

Here's the xml file that is in the web.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://

java.sun.com/j2ee/dtds/web-app_2_3.dtd">

<web-app> 

</web-app>

New and Improved!

   The SQL Developer Exchange , http://sqldeveloper.oracle.com,  has been out there almost since the beginning of the tool.  It's our way to allow users to log requests and have other vote on them.  Then we each release pick off some and put them into the product.  It's been basically unchanged since day 1.  This week I've built a new set of screens which hopefully look better and are easier to use.   We have implemented over 400 requests from users.  The exchange is have a bit of a facelift and if anyone would like to test out the new Exchange go here https://apex.oracle.com/pls/apex/f?p=43135:1 .  This is the same tables and data underpinnings so if you log or rate a request it will show in both place.

SQL Developer 3.0, XE 11.2 Beta, Developer Days

It is said things happen in threes.

1) SQL Developer 3.0 was release on Tuesday.  Some of the major new/revamped things include migration from Sybase,  Data Miner, Spatial, and Scheduler.  There's a longer list of new things we added which can be read here.  If you have not already, it's downloadable here.

2) OTN Developer Days in Toronto.  We had a great crowd in Toronto.  Chicago is the next stop on April 25th registration is here .  This is a great way to talk with people from development.  We have folks from TimesTen, XML, Java, PHP, Jdev, and .NET all at the event.

3) Rounding out the week.  Oracle XE 11.2 Beta.  After a long wait, it's finally arrived.  It can be downloaded here .  The documentation can be found here. There is a dedicated forum here for any questions or issues you may find.

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.