Step Three: Design, Create, and Load the Oracle Staging Area

The staging area’s purpose is to create a data schema that will allow quick mapping from the source database or the flat files to the destination database. This will create a straightforward load into Oracle by mapping data and tables one-to-one. By using this method, you can use Oracle’s SQL*Loader tool.

The DCT uses the flat File Layout, which is defined by the FFL tool, to create the staging area for the migration automatically. After the copy of the old source data exists in Oracle, the DCT will use the SQL Loader Generator (LDRG) to generate SQL*Loader scripts that will load flat files from the old system into the staging area.

Along with tables for the staging area, DCT can also create conversion views. These views are helpful because they show data in the staging area as the data will appear later in the final target Oracle database. At this point, you are using the conversion mapper (CM) of the DCT to specifically map columns from the DCT to their converted counterparts in the final Oracle database.

Step Four: Development of Conversion Package and Final Load

The conversion package for the DCT is the bridge between the staging area and the old system. It is able to keep track of complex mappings between the two systems (see Figure 7.15).

Figure 7.15.  The conversion model represents the logic that will map data from an older (source) database into the Oracle (destination) database.

In the development process, the DCT will generate a PL/SQL package that will be the heart of the conversion process. This PL/SQL package consists of the following components:

•  Global object definitions and specifications. This defines the calling parameters of the conversion package and the global variables that will be used.

•  Main procedure. This procedure will contain the main cursor loop. You use a cursor because the DCT is looping through every row in the source table that exists in the staging area and changing that row for either insert, update, or delete in the new target database.

•  Group procedures. These procedures provide the actual brute-force mapping that might need to be done between data elements. For instance, if your source system has a date format of 251295 (DDMMYY) and you want the format 25-DEC-1995 (DD-MON-YYYY) for your new system, you need to perform SQL operators like TO_DATE and TO_CHAR on the old data to convert it into the new data.
There are four types of group procedures:

Main

Update

Insert

Delete

•  Initialization code for package. This is usually not used because the package is normally executed by a human operator in a tool like SQL*Plus.

By packaging the logic that will move data from the Oracle staging area to the target Oracle database, you have more ability to structure and keep track of data movement.

The order in which group procedures are executed can be specified with the DCT’s PL/SQL template. This is extremely useful if you want to update records that already exist in the Oracle database and insert only records that exist in the older source database. This scenario is common with a new system that has been run in parallel with the older system and already contains most of the data (see Figure 7.16).

Figure 7.16.  By specifying the order of your data conversion, you can use PL/SQL to insert new records into Oracle and update older records.

Regardless of the magic that the DCT can perform in organizing the conversion tasks, the “conversion developer” (the person writing the conversion routines) needs to be able to use the SELECT, INSERT, UPDATE, CREATE VIEW, and CREATE PROCEDURE functions within SQL and Oracle’s PL/SQL. The final PL/SQL that will be placed in the DCT procedure shells can be very complex and will probably utilize many SQL extensions to convert the data. It is important to assign technical people to this aspect of Oracle’s migration methodology.