Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: TimeTraker
Oracle Tips by Burleson

The purpose of this chapter is to bring together the topics in previous chapters and provide the migration of a complete application from Oracle 10g to EnterpriseDB Advanced Server 8.1. 

This chapter will introduce TimeTraker, a time entry system.  The goal is to provide a non-trivial example while trying to not make the example overwhelming.  This application could have easily been an order entry system, a simple HR system or one of many other applications that are often used as examples.  I hope this example will introduce a new kink into the example universe (the exampleverse?).

TimeTraker accepts input from any system that can call a stored procedure or that can produce an XML document. TimeTraker is a pretty standard time tracking application in that it tracks resources – that's people; schedules, projects, tasks, etc and in that it provides a set of reports.  Reports are generated in both formatted output (text) and in Comma Separated Values (CSV) for loading into a spreadsheet program.

After introducing TimeTraker and describing the architecture, I will take a brief walk through the code. I will introduce all of the major components and provide some description of how they work.

I will then actually generate some data, get it into the system and provide reports for comparing with the later goal of migration.  Obviously, the goal is migration but I want to explain how the system works so that I can do a step-by-step post migration walk through. 

I am purposely creating the application so that it forces me to make some choices during the migration.  If I were to just accept all of the defaults of the Migration Toolset (Chapter 7), there would not be much purpose to an additional chapter.

The application will require schema changes as well as code changes.  I am purposely not including a front end to the application.  With the plethora of languages and tools that can access EnterpriseDB, there is no way I could do justice to that particular topic.

If you are writing your applications using standard practices (data encapsulation via stored procedures) and standard database interfaces (JDBC, ODBC, etc), your front end should require minimal changes when porting.  If you are hard-coding database specific code in your front-end, you may be required to make additional front-end changes.

A word of warning: This chapter will cover many topics and a lot of code.  If you are not prepared to have an Oracle database and an EnterpriseDB database running while you follow along, you may get lost.  This is probably the most complex chapter in this book.


In this section, I will outline the requirements for the TimeTraker application.  The main requirements for this application are to present a real world application that is not so complex as to be useless but that is still complex enough to provide a working example of migrating an application.

TimeTraker is written for Oracle 10g.  TimeTraker will compile on any 10g edition, including Enterprise, Standard, Standard Edition One and Express. 

TimeTraker is a simple system for tracking hours worked on tasks.  Tasks can be shared between projects and resources provide hours by task, not by project. 

Security in TimeTraker will be based on user roles.  An end-user may be an administrator, project manager or user.  Users can only add hours worked to projects and run reports.  Project managers can add resources, projects and tasks.  Administrators can change calendars, schedules and reports.

A typical flow of a project may look like the following graphic (Figure 9.1):

Figure 9.1: TimeTraker Flow

A project manager must be able to create a project, create tasks for a project and associate those tasks to a project.  A project manager must also be able to assign resources to work on tasks. 

Developers/users must be able to enter hours worked on tasks.

Everyone must be able to run reports.  Five reports will be available:

* Hours Worked By Resource

* Resources Assigned to Projects

* Projects and Tasks

* Project Hours Remaining By Project

* Project Hours Remaining By Resource

All five reports produce a text file suitable for display and a CSV file suitable for loading and manipulating via a spreadsheet

TimeTraker allows input directly or via an XML document.  Input will normally be via a GUI application.  The primary interface between the GUI and the database will be stored procedures. 

TimeTraker will also provide a calendar and resource schedule lookup for front-end GUI use.  Maintenance routines must be written for these lookups.

As far as requirements go, we now have input and output requirements as well as security.  A system any larger than this would require a book of its own so I will stop here for requirements.  The next step is to build a data model.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter