BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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:


  INTERFACE_ID   NUMBER(12)                     NOT NULL,
  XML_DATA       TEXT,

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:

     p_in_date IN DATE,
     p_in_months IN NUMBER)
  v_date DATE;

    '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;

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 ) 

    v_file UTL_FILE.FILE_TYPE;
    v_file_name VARCHAR2(255);

    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',

    g_files.csv_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.csv',


Would now use the scalar values:

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

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

    g_txt_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.txt',

    g_csv_file := utl_file.fopen('REPORT_OUTPUT',
                  v_file_name || '.csv',


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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter