Progress
DataServer
for ORACLE Guide


ORACLE Size Limitations

The limitations that ORACLE and Progress place on the number of columns or fields per table are important issues to consider when you plan a migration:

When you use the Progress-to-ORACLE utility to create an ORACLE database, it adds columns to an ORACLE table to accommodate Progress functionality (see Table 5–5). Because ORACLE7 limits the size of a database table to 254 columns and ORACLE8 to 1,000 columns, you must plan for the additional columns generated by the utility when you create the database schema.

When you design the Progress database tables that you want to make compatible with ORACLE, leave enough columns free for the migration utility to use. Table 5–6 lists how many columns on an ORACLE table each Progress object requires.

Table 5–6: Columns Required by Progress Objects
Database Object
Number of Columns
Record ID
One per table
Case-insensitive indexes
One per indexed case-insensitive field
Array
One per array element
Sequence
One per sequence1
  1. A sequence does not require an additional column. For each sequence in the Progress database, the utility creates an ORACLE sequence object.

Use the following formula to calculate how many columns an ORACLE7 table has available for data:

columns available for fields = 254 - 1 - case-insensitive indexed fields - 
number of extent elements 

For example, a table that contains 253 fields will convert correctly only if none of the fields are extents and you have marked none of the indexed fields as case insensitive. If your table includes 2 field extents, and each extent has 5 elements, the utility needs 10 columns to unroll the field extents. The ORACLE7 table then has only 243 columns available for other fields:

243 = 254 - 1 - 0 - 10 

Column Names

ORACLE allows column names to be only 30 characters long. The Progress-to-ORACLE utility truncates the names of the Progress fields so that they meet this limitation. After running the utility, you can use the Progress Data Dictionary to access the schema holder and modify the field name.

The utility names objects it creates in the ORACLE database by adding symbols to ORACLE table or column names. It can generate a name that is longer than 30 characters, which causes ORACLE to return error 972 when you access that column. For example, to create a unique record identifier in the State table, the utility creates a column named STATE##PROGRESS_RECID.

Column Width

The Progress-to-ORACLE utility uses a field’s format information when it defines the field as an ORACLE column. Since Progress allows a field to hold more data than the field’s format will display, the utility might instruct ORACLE to create a column that is wider than the format indicates. For those fields with a display format of x(8), the utility automatically generates a VARCHAR(30) column. Before running the utility, adjust the display format of fields to accommodate all the data the fields contain.

If a column generated by the utility is not wide enough to hold the data, Progress does not load the remainder of the data for that table into the ORACLE database.

Follow these steps to change the width of the column:

  1. Use an editor to open the file with the .sql extension. The migration utility creates the .sql file, which contains information for the entire database.
  2. Find the section of the file that describes the column.
  3. Assign the column a larger value to accommodate the data.
  4. Extract the section of the file that describes the column and place it in another file named filename.sql. Be sure to include any index information for the column.
  5. Re-create the table by running SQL*Plus. Enter the following command at the system prompt:
  6. SQLPLUS < userID/password filename.sql 
    

    This command deletes the data definition for the table and re-creates it.

  7. Load the data.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095