EnterpriseDB: Migration Oracle Tips by Burleson Consulting

This migration will be a one-time migration that will be accomplished via Developer Studio.  I will begin with a little bit of analysis, migrate and then code around any difficulties.

Starting with some advance analysis will help downstream.  I can see in advance what obviously won't migrate automatically and take steps to make it work.  I may miss some objects but the majority of them will be obvious.

In addition to finding what might not migrate easily, I will want to build my plan for migration.  I will not expect an entire schema/application to migrate in one step.  It's true that Developer Studio will allow you to do that, but only trivial applications will be that easy.

My first step in migration is to look at my tables to see if I will have any issues there.

Based on things like relationships, sizes and data types, the only table that I think may give me problems is the TT_XML_INTERFACE table.  This table includes the XML_DATA column, which is an XMLType data type.  XMLType is not currently supported by EnterpriseDB.

I expect that I will need to create my own table in EnterpriseDB to handle this.  That also means that I will need to plan how I will handle the XML requirements of my application.  If we look above at the code that actually extracts the XML data and inserts it into the table, we can see that it really is not that complex.

EnterpriseDB supports basic XML functionality as I described in Chapter 4.  I will handle my XML needs via the XML extensions.  That also means that my new TT_XML_INTERFACE table will be declared as:

CREATE TABLE traker.TT_XML_INTERFACE

(
  INTERFACE_ID   NUMBER(12)                     NOT NULL,
  DATE_RECEIVED  TIMESTAMP(6),
  XML_DATA       TEXT,
  PRIMARY KEY
 (INTERFACE_ID)
);

The only change here is that I will be storing my XML data in a TEXT column instead of an XMLType column.

Now I will migrate my tables from Oracle to EnterpriseDB.  For my first step, I need a connection from Developer Studio to the Traker Database.  Figure 9.3 displays that connection.

Figure 9.3: Add Traker Connection

The next step is to add a dedicated database in EnterpriseDB to store my TimeTraker application schema.  This step is not absolutely required.  I prefer to do this to keep my applications logically separated.  I will also be bringing over the Traker schema and that is really enough to accomplish my goal of separation.  I just prefer the application to reside in its own database.

Figure 9.4: Add TimeTraker Database

My next step would be to migrate the schema and tables.  I know at least one table will not migrate correctly.  What I will do is run an entire schema migration with the expectation that I will get errors.  A few errors now is not the end of the world.  The errors that I get will help me on the next steps.

Using Developer Studio, I select the Traker schema in the Oracle database and choose online migration, Figure 9.5.

Figure 9.5: Online Schema Migration

By choosing online migration, I am brought to the online migration window, Figure 9.6.  In the online migration window, I chose TRAKER_EDB as the destination database and then pressed the Run button.  As you can see, I got the expected error with the TT_XML_INTERFACE table.

Figure 9.6: Online Migration Results

I will fix that error by creating my new text based table as described above.  I also received other errors such as shown in Figure 9.7.

Figure 9.7: Additional Migration Results

Most of these errors will be fixed once the TT_XML_INTERFACE table is created.  One error that can be fixed easily is the TT_CALENDAR_ADMIN package that failed because a function, ADD_MONTHS, was used in a default for a parameter.  EnterpriseDB does not support the ADD_MONTHS function.

The easiest way, possibly, to fix that would be to remove the default.  That would take additional analysis and regression testing to make sure something wasn't broken downstream.

I prefer to fix a problem by treating the error not the symptom.  The problem isn't that I used add_months in a default, the problem is that add_months does not exist.  My approach to fixing this is to add add_months.

To do that, I create an add_months function:

CREATE OR REPLACE FUNCTION add_months(
     p_in_date IN DATE,
     p_in_months IN NUMBER)
RETURN date
AS
  v_date DATE;
BEGIN 

  EXECUTE IMMEDIATE
    'SELECT cast( to_date(''' ||
       to_char(p_in_date, 'DD-MON-YYY HH24:MI:SS') ||
      ''', ''DD-MON-YYY HH24:MI:SS'')' ||
      ' +  interval ''' || to_char(p_in_months) ||
      ' month'' as date)'
  INTO v_date;
  RETURN v_date;
END;

Very simply, I add a number of month intervals to a date using dynamic SQL and reproduce the add_months function.  I do this by creating an SPL function. 

I can test it with a few simple selects:

select sysdate, 

       add_months( sysdate, 1),
       add_months( sysdate, 2),
       add_months( sysdate, 3)

And I get the values I am expecting:

traker_edb=# select sysdate,
traker_edb-#        add_months( sysdate, 1),
traker_edb-#        add_months( sysdate, 2),
traker_edb-#        add_months( sysdate, 3)
traker_edb-# ;

     timestamp      |     add_months     |     add_months     |     add_months
--------------------+--------------------+--------------------+--------------------
 27-JAN-07 10:16:16 | 27-FEB-07 10:16:16 | 27-MAR-07 10:16:16 | 27-APR-07 10:16:16

(1 row)

This method is how I tend to approach all problems, including migrating applications.

Now that I have fixed the add_months issue and I have created my TT_XML_INTERFACE table, I will rerun the migration.  Just to be sure everything is clean, I will drop the traker_edb database in EnterpriseDB.  I then recreate the database, use Developer Studio to manually create a traker schema (highlight Schemas and right-click, choose Add Schema).

After I have manually created the Traker schema, I create my add_months function and my TT_XML_INTERFACE table.  I then choose the Traker schema in Oracle and rerun the online migration.  I still get the XMLType error but that's ok this time because I have already manually created the TT_XML_INTERFACE Table.

Looking at the output, I am getting an error in TT_REPORT_OUTPUT, Error Creating Package Body TT_REPORT_OUTPUT: ERROR: syntax error at or near ";".  On investigating it, I see that the code did not make it over.  I pull the code for the package body, from a text file, into a SQL Interactive window.

Although not vastly intuitive or at first obvious, the problem appears to be that the package is using forward declarations of internal procedures.  If you remember from Chapter 8, EnterpriseDB does not currently support forward declarations.  There are two possible fixes for this: put the internal procedures at the top of the package or declare the internal procedures in the specification.  Either approach eliminates the need for forward declarations. 

I believe that having internal, private procedures is a good practice.  I will re-order my procedures so that the internal procedures are at the top.  In some instances, where procedures are calling each other, you must put the declaration in the specification.  Fortunately, I did not run into that situation here.

Once I have my procedures reordered and EnterpriseDB can actually parse my code, I get a new error:

Executed:                      Sat Jan 27 10:33:09 EST 2007
            Elapsed Time:               Total: 0, SQL query: 0, Building output: 0 
            ERROR: EDB-00201: container 'UTL_FILE' does not exist

I forgot, EnterpriseDB does not currently support UTL_FILE.  However, an enterprising Oracle dweebish and author has written his own version of UTL_FILE and it is included in Chapter 8. 

After running the UTL_FILE code in Chapter 8 that creates the UTL_FILE schema and code, I then recompile TT_REPORT_OUTPUT.  I get another error.

            Executed:                      Sat Jan 27 10:55:16 EST 2007
            Elapsed Time:               Total: 0.015, SQL query: 0, Building output: 0.015 
            ERROR: type utl_file.file_type is not scalar

The issue here is with my record declaration:

  TYPE local_file_type IS RECORD (
    txt_file UTL_FILE.FILE_TYPE,
    csv_file UTL_FILE.FILE_TYPE );

EnterpriseDB does not like a record definition of non-scalar data types.  If I review the definition of UTL_FILE.FILE_TYPE:

CREATE TYPE utl_file.file_type AS (file TEXT);

I would think that I can easily replace the UTL_FILE.FILE_TYPE with TEXT but that is not true.  That would raise data type conversion issues down the line.  This particular error really annoys me.  This is a bug and forces me to write less readable and less maintainable code.  I can work around it though. 

Rather than use a record type, I will just create two scalar variables:

    g_txt_file UTL_FILE.FILE_TYPE;
    g_csv_file UTL_FILE.FILE_TYPE;

Anywhere I am referencing the record type, such as:

  PROCEDURE open_files(
    p_report_name IN VARCHAR2 ) 

  IS 
    v_file UTL_FILE.FILE_TYPE;
    v_file_name VARCHAR2(255);
  BEGIN 

    v_file_name := replace(p_report_name,' ', '_') ||
                   user ||
                   to_char(sysdate, 'YYYYMMDDHH24MISS');                  

    g_files.txt_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.txt',
                  'w',
                   32000);

    g_files.csv_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.csv',
                  'w',
                  32000);   

  END;

Would now use the scalar values:

  PROCEDURE open_files(
    p_report_name IN VARCHAR2 )
  IS 
    v_file UTL_FILE.FILE_TYPE;
    v_file_name VARCHAR2(255);
  BEGIN

    v_file_name := replace(p_report_name,' ', '_') ||
                   user ||
                   to_char(sysdate, 'YYYYMMDDHH24MISS');

    g_txt_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.txt',
                  'w',
                   32000);
    g_csv_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.csv',
                  'w',
                  32000);

  END;

It's a minor change that is easily remediated using search and replace but it annoys me none-the-less.  After making the changes, I recompile the code and it compiles without error. 

The final set of errors has to do with the reporting views.  The migration moved what it could.  I will need to rewrite at least one of the views anyway because I am using analytics that EnterpriseDB does not currently support.  I find it easier to compile the views from scripts manually (at least the first time) to be sure they will all compile correctly.

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