Skip Headers

Oracle Migration Workbench Reference Guide for Informix Dynamic Server 7.3 Migrations
Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT

Part Number A97251-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Oracle and Informix Dynamic Server Compared

This chapter contains information comparing the Informix Dynamic Server database and the Oracle database. It includes the following sections:

Database Security

This section includes information on issues of security with Informix Dynamic Server databases and Oracle databases.

Database Authentication

A fundamental difference between Informix Dynamic Server and Oracle is database user authentication. Informix Dynamic Server users are maintained and authenticated by the host operating system, whereas Oracle users are maintained by the database and can use several methods of authentication, usually through the database.

A user can connect to an Informix Dynamic Server database server through the operating system login information, however access to the databases the server supports is restricted by the sysuser table. The sysuser is maintained by each database and the database administrator.

Database as a Logical Partition

Multiple databases on a single Informix Dynamic Server database server are migrated to a single Oracle database. Schemas in different databases are owned by the same user.


Informix Dynamic Server has two special users, informix and root. A description of these users is as follows:

User name Description


Informix Dynamic Server software owner


Operating system super user

These users have database administrator access to all the databases supported by the Informix Dynamic Server database server. The two user names do not have to be listed in the sysusers table for any database. The Informix Dynamic Server plug-in creates the two user names in the Oracle database. Another special Informix Dynamic Server database user, which does not have to be an operating system user, is public.

You can grant the public database user system and object privileges and its database level privileges are entered in the sysusers table. The privileges granted to public are automatically available to every other database user. The Informix Dynamic Server plug-in migrates all the object privileges.

Oracle has the concept of a database group or role, where you can grant privileges. These privileges are made available to all other users in the database. It is also called PUBLIC.

The difference is that public is not listed as a database user and you cannot grant connect system privilege to public to enable any user logged on to the host operating system gain access to the database.

All Informix Dynamic Server object level privileges granted to public are migrated to Oracle. None of the three Informix Dynamic Server database privileges granted to public are migrated.

The Informix Dynamic Server plug-in could not detect what operating system users had access to the database. The Informix Dynamic Server plug-in only creates Oracle users for the users listed in sysusers in each of the Informix Dynamic Server databases selected for migration. Therefore, if you rely on granting connect, resource, or even dba to public as a method of allowing operating system users access to the database, then you must explicitly grant each of those users the appropriate database level privilege.

Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.

The schema migration topics discussed here include the following:

Schema Object Similarities

There are many similarities between schema objects in Oracle and Informix Dynamic Server. However, some schema objects differ between these databases. For specific information about schema objects, see the SQL Statements topic within the Oracle9i SQL Reference, Release 1 (9.0.1).

Table 2-1 shows the differences between Oracle and Informix Dynamic Server.

Table 2-1 Schema Objects in Informix Dynamic Server and Oracle  
Oracle Informix Dynamic Server













Temporary tables

Temporary tables


Cluster Index

Check constraint

Check constraint

Column default

Column default

Unique key

Unique key

Primary key

Primary key

Foreign key

Foreign key



PL/SQL Procedure

SPL Procedure

PL/SQL Function

SPL Function



AFTER triggers


BEFORE triggers


Triggers for each row

Triggers for each row




SERIAL datatype for a column





Schema Object Names

Reserved words differ between Oracle and Informix Dynamic Server. Many Oracle reserved words are valid object or column names in Informix Dynamic Server. Use of reserved words as schema object names makes it impossible to use the same names across databases. The Migration Workbench appends an underscore (_) to the name of an Informix Dynamic Server object that is an Oracle reserved word.

Neither Oracle nor Informix Dynamic Server is case-sensitive with respect to object names. Object names in Informix Dynamic Server are stored as lower case, while Oracle schema object names are stored as upper case.

Choose a schema object name that is the following:

Ensure that the object name is not a reserved word from either database.

For a list of Oracle reserved words, see the Oracle9i SQL Reference, Release 1 (9.0.1).

In non-ANSI-Compliant Informix Dynamic Server databases, schema object names are required to be unique across users. This behavior in Oracle is similar to Informix Dynamic Server ANSI-Compliant mode databases. Different users can create objects with the same name without any conflicts.

Informix Dynamic Server Database-level Privileges

For information on database-level privileges, see the Oracle Migration Workbench Release Notes.

Migrating Multiple Databases

The Migration Workbench supports the migration of multiple Informix Dynamic Server databases if they are on the same Informix Dynamic Server database server.

Table Design Considerations

This section discusses table design issues that you need to consider when converting Informix Dynamic Server databases to Oracle. This section includes the following:

Data Types

This section outlines conversion considerations for the following data type:


The Datetime precision in Informix Dynamic Server is to 5 decimal places, 1/100000th of a second. Oracle9i has a new data type TIMESTAMP which has a precision of 1/100000000th of a second. Oracle also has a DATE data type that stores date and time values accurate to one second. The Migration Workbench has a default mapping to the DATE data type.

For applications that require finer date/time precision than seconds, the TIMESTAMP data type should be selected for the datatype mapping of date data types in Informix Dynamic Server. The database stores point-in-time values for DATE and TIME data types.

As an alternative, if an Informix Dynamic Server application uses the DATETIME column to provide unique IDs instead of point-in-time values, you can replace the DATETIME column with a SEQUENCE in the Oracle schema definition.

In the following examples, the original design does not allow the DATETIME precision to exceed seconds in the Oracle table. The examples assume that the DATETIME column is used to provide unique IDs. If millisecond precision is not required, the table design outlined in the following example is sufficient:

Table 2-2 Original Table Design
Informix Dynamic Server Oracle
CREATE TABLE example_table
(datetime_column    datetime        
not null,
text_column         text            
varchar(10)     null)
CREATE TABLE example_table
(datetime_column    date            not null,
text_column         clob            null,
varchar_column      varchar2(10)    null)

The design shown in Table 2-3 allows you to insert the value of the sequence into the integer_column. This allows you to order the rows in the table beyond the allowed precision of one second for DATE data type fields in Oracle. If you include this column in the Informix Dynamic Server table, you can keep the same table design for the Oracle database.

Table 2-3 Revised Table Design
Informix Dynamic Server Oracle
CREATE TABLE example_table
(datetime_column    datetime        
not null,
integer_column      int             
text_column         text            
varchar_column      varchar(10)     

CREATE TABLE example_table
(datetime_column    date            not null,
integer_column      number          null,
text_column         clob            null,
varchar_column      varchar2(10)    null)

For Informix Dynamic Server, the value in the integer_column is always NULL. For Oracle, the value for the field integer_column is updated with the next value of the sequence.

Create the sequence by issuing the following command:

CREATE SEQUENCE datetime_seq

Values generated for this sequence start at 1 and increment by 1.

Many applications do not use DATETIME values as UNIQUE IDs, but still require the date/time precision to be higher than seconds. For example, the timestamp of a scientific application may have to be expressed in milliseconds, microseconds, and nanoseconds. The precision of the Informix Dynamic Server DATETIME data type is 1/100000th of a second; the precision of the Oracle DATE data type is one second. The Oracle TIMESTAMP data type has a precision to 1/100000000th of a second. However, the precision recorded is dependent on the operating system.

IMAGE and TEXT Data Types (Binary Large Objects)

The physical and logical storage methods for BYTE and TEXT data in Informix Dynamic Server is similar to Oracle BLOB data storage. A pointer to the BYTE or TEXT data is stored with the rows in the table while the IMAGE or TEXT data is stored separately. This arrangement allows multiple columns of IMAGE or TEXT data per table. Oracle may store IMAGE data in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBs and CLOBs may or may not be stored in the row depending on their size. If LONG or LONG RAW appears, only one column is allowed.

If the Informix Dynamic Server TEXT column is such that the data never exceeds 4000 bytes, convert the column to an Oracle VARCHAR2 data type column instead of a CLOB column. An Oracle table can define multiple VARCHAR2 columns. This size of TEXT data is suitable for most applications.

Check Constraints

You can define check constraints in a CREATE TABLE statement or an ALTER TABLE statement in Informix Dynamic Server. You can define multiple check constraints on a table. A table-level check constraint can reference any column in the constrained table. A column can have only one check constraint. A column-level check constraint can reference only the constrained column. These check constraints support complex regular expressions.

Oracle defines check constraints as part of the CREATE TABLE or ALTER TABLE statements. A check constraint is defined at the TABLE level and not at the COLUMN level. Therefore, it can reference any column in the table. Oracle, however, does not support complex regular expressions.

Schema Migration Limitations for Informix Dynamic Server

The schema migration limitations are separated into the following categories:


The Migration Workbench captures all dbspaces on the Informix Dynamic Server, even though you may not require all dbspaces. You can delete the dbspaces, as appropriate, from the Source Model. If you delete the root dbspace, the next time you start the Migration Workbench, the sizing information shows up as zero (0).

Mapping for Informix Dynamic Server Database Level Privileges to Oracle System Privileges

Before migration ensure that the sysmaster database exists for the database server you are migration from. Oracle does not support the migration of Default DATETIME literal.

The Informix Dynamic Server CONNECT privilege maps to the following Oracle system privileges:

The Informix Dynamic Server RESOURCE privilege maps to the following Oracle system privileges:

The Informix Dynamic Server DBA privilege maps to the Oracle system All Privileges privilege.


nformix database users granted the CONNECT database level privilege do not have the privilege to create tables, procedures or triggers. However, CONNECT users may be the owner of these object types, created for them by more privileged users.

The Migration Workbench creates schema objects connected to the Oracle database as the owner of the object. Any attempt to create an object without the appropriate privilege generates an error. Therefore, the Informix Dynamic Server plug-in maps users, that have the Informix Dynamic Server CONNECT privilege, to Oracle with the system privileges to create tables, procedures and triggers. To revoke the privileges from the users after migration execute the following:

revoke create table, create procedure, create trigger from <user>;

You cannot migrate Informix Dynamic Server DBA users with the WITH ADMIN OPTION for any of the system privileges. The Informix Dynamic Server DBA cannot grant the privileges to other users.


The following limitations apply to the Defaults schema object:


Migrate indexes, then migrate unique constraints and primary key constraints. If you do not migrate the schema objects in this order, a system generated index is created for unique constraints and primary keys. This causes the CREATE INDEX statement to fail.

Check Constraints

Check constraints within Informix Dynamic Server are not parsed to Oracle syntax. The user should ensure that they can successfully execute all check constraints listed in the Oracle Model. For more information, see Bug 1644309 in the Oracle Bug Database.

Check Constraint Owners

If a user creates a check constraint on another users' table, the check constraint is created in the Oracle Model and the check constraint is owned by the owner of that table.

Data Types

This section provides descriptions of the differences in data types used by Informix Dynamic Server and Oracle databases. This section contains the following information:


You can map data types from Informix Dynamic Server to Oracle with the equivalent data types listed in Table 2-4. You can define how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.


The Informix Dynamic Server BYTE datatype stores any type of binary data and has a maximum limit of 2^31 bytes (2G). The comparable Oracle datatypes are LONG RAW and BLOB.

Oracle LONG RAW stores variable-length raw binary data field used for binary data up to 2G in length. Although you can insert RAW data as a literal in an INSERT statement (a hexidecimal character represents the bit pattern for every four bits of RAW data, 'CB' = 11001011), there are several restrictions on LONG and LONG RAW columns, for example, only one LONG columns is allowed per table and LONG columns can not be indexed. The LONG RAW datatype is provided for backward compatibility with existing applications. For new applications, Oracle recommends the use of BLOB and BFILE datatypes for large amounts for binary data.

Oracle9i and Oracle8i BLOB, and other Oracle9i and Oracle8i LOB types, CLOB, NCLOB, and BFILE, have a much greater storage capacity than LONG RAW, storing up to 4G of data and Oracle9i and Oracle8i tables can have multiple LOB columns.

Oracle LONGs support on sequential access, while Oracle9i and Oracle8i LOBs support random piece wise access. Although Oracle9i and Oracle8i SQL cannot directly manipulate LOBs, you can access LOBs from SQL through the Oracle9i and Oracle8i supplied DBMS_LOB PL/SQL package. The DBMS_LOB package provides many functions and procedures to append the contents of one LOB to another, compares contents or parts of contents, copies contents, reads from and writes to LOBs, and also returns part of a LOB from a given offset and length.

For example, with Informix Dynamic Server you can select any part of a BYTE column by using subscripts:

select cat_picture[1,75] from catalog where catalog_num = 10001;

A similar request in Oracle9i and Oracle8i follows:

blob_loc BLOB;
binchunk RAW;

SELECT cat_picture INTO blob_loc FROM catalog WHERE catalog_num = 10001;
binchunk := dbms_lob.substr(blob_loc, 75, 1);  


The Informix Dynamic Server CHAR datatype stores any sequence of letters, numbers, and symbols. It can store single byte and multibyte characters. A character column has a maximum length of n bytes, where 1<=n<=32767. If n is not specified, 1 is the default. If a character string is less than n bytes, then the string is extended with spaces to make up the length. If the string value is longer than n bytes, the string is truncated without raising an error.

The comparable Oracle datatypes are:

Informix Dynamic Server CHAR(n) datatypes can be up to 32767 bytes in length. Columns defined as CHAR with a length <= 2000 can be migrated to the Oracle CHAR datatype and functionality contains nearly the same functionality. Both are fixed-length character strings and if you insert a shorter string, the value is blank-padded to the fixed length. If, however, a string is longer, Oracle returns an error.


Oracle compares CHAR values using blank-padded comparison semantics. For more information, see the Comparison Sematics topic.

Oracle VARCHAR2 can hold data up to 4000 bytes in length. Oracle Corporation recommends you us a migration to VARCHAR2 when you are migrating Informix Dynamic Server CHAR columns that store more than 2000 bytes of data but less than or equal to 4000. VARCHAR2 is a variable length datatype and uses non-padded comparison semantics.

If Informix Dynamic Server tables have CHAR(n) columns defined with n > 4000 then the only option is to migrate to Oracle LONG or CLOB.

The LONG datatype can store variable-length character data up to 2G in length. LONG columns can be used in SELECT lists, SET clauses of UPDATE statements, and VALUES clause of INSERT statements. The LONG datatype is provided for backward compatibility and CLOB should be used for storing large amounts of character data. There are several restrictions on LONG datatypes, such as the following:

The CLOB datatype is just one of the LOB datatypes supported by Oracle. LOB datatypes differ form LONG datatypes in several ways:

LOB datatyes can be stored in-line within a table, or out-of-line within a tablespace, using a LOB locator, or in an external file -- a BFILE datatype. It is not currently supported by the Migration Workbench.

Using PL/SQL to manipulate LOBs, VARCHAR2s in PL/SQL can store up to 32767 bytes of data, so handling large Informix Dynamic Server CHAR datatypes should be reasonably efficient when stored in Oracle as CLOBs.

clob_loc CLOB;
some_text VARCHAR2(32767);
text_len INTEGER;
INSERT INTO page_info (page_num, page_text) VALUES (101, empty_clob);
SELECT page_text INTO clob_loc FROM page_info where page_num = 101;
text_len := LENGTH(some_text);
DBMS_LOB.WRITE(clob_loc, text_len,1, some_text);

You can use subscripts on BYTE columns. Subscripts can also be used on CHAR, VARCHAR2, NCHAR, NVARCHAR, and TEXT columns. The subscripts indicate the starting and ending character positions that define each column substring. With the DBMS_LOB package functions, you can choose to receive all or part of the CLOB, using READ and SUBSTR.

Collation Order

Data stored in CHAR columns is sorted based on the order of the code-set for the character set of the database, irrespective of the current location. Sorting in Oracle is based on the Oracle NLS settings. If the Oracle NLS specify a different sort order than the character code set, a sort-by code set can be enabled to ensure that the expected result remains the same. For more information, see the NCHAR(n) topic.

Multibyte Character Sets

Just as is the case for Informix Dynamic Server CHAR and VARCHAR datatypes, the length of Oracle CHAR and VARCHAR2 datatypes is specified in bytes. If the database character set is multibyte, make sure to calculate the appropriate space requirements to allow for the maximum possible number of bytes for a given number of characters.

Comparison Sematics

Informix Dynamic Server comparison semantics for the CHAR datatype and the Oracle CHAR datatype are the same. If you are migrating CHAR(n) columns that have a length where n such that 2000< n <= 4000 to then see the VARCHAR section for more details on comparison semantics for VARCHAR2.

Empty Strings

Informix Dynamic Server CHAR (and VARCHAR) columns can store an empty string, i.e. no data with a length zero. Even though a CHAR column may appear blank-padded, its length is 0. The empty string is not the same as NULL, which indicates that the value is undefined and of unknown length. However, Oracle does not have the concept of an empty string. Therefore, Oracle inserts empty strings as NULL.

You should check the application code and logic for unexpected behavior, such as empty strings migrating to NULL.


CHARACTER is a synonym for CHAR.


Informix Dynamic Server CHAR and NCHAR datatypes both store the same type of data, a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between the datatypes is the order the Informix Dynamic Server database server sorts the data. CHAR columns are sorted on code set, the numeric values of the characters defined by the character encoding scheme. NCHAR columns are sorted based on the locale-specific localized order.

Oracle can sort CHAR data based on both the code set and the local-specific order.

The Migration Workbench for Informix Dynamic Server migrates both the Informix Dynamic Server CHAR and NCHAR datatypes to the Oracle CHAR datatype.

Collation Order

The Oracle NLS settings, either by default or as configured by the DBA, define the exact behavior of the sorting order. The NLS settings can be made at the database/init.ora, environment, and session levels.

A locale-specific sort is as a known as a linguistic sort in Oracle. You can use a linguistic sort by setting one of the Oracle collation parameters, NLS_SORT. The following is an example of a linguistic sort:

NLS_SORT = French

A code set sort is known as a binary sort in Oracle. If an application, for some reason, needs to sort data in a CHAR column based only on the code set, then the application can set the NLS_SORT to be a binary sort. The following is an example of a binary sort:



Oracle does have a built-in NCHAR datatype, as well as NVARCHAR2 and NCLOB datatypes. These three datatypes can be used to store fixed-width and variable-width multibyte character set data as specified by the NATIONAL CHARACTER SET setting in the CREATE DATABASE command.

The National Character Set is an alternative character set to the Database Character Set. It is particularly useful in databases with a variable-width multibyte database character set because NCHAR, NVARCHAR2, and NCLOB can store fixed-width multibyte characters. Storing fixed-width multibyte characters enhances performance by allowing optimized string processing on these columns. An Oracle database can not be created with a fixed-width multibyte character set but the National Language datatypes allow storage of fixed-width multibyte character set data. The properties of a fixed-width character set may be more desirable for extensive processing operations or for facilitating programming.


The Informix Dynamic Server VARCHAR datatype stores varying length single-byte and multibyte character strings of letters, numbers. and symbols. The maximum size of this column is m, which can range from 1 to 255. The minimum reserved space is r. This is optional and defaults to 0 if not specified. The minimum reserved space can range from 0 to 255.

The comparable Oracle datatype is VARCHAR2(n) that also stores variable-length character strings. An Oracle VARCHAR2(n), however, can have a maximum string length of between 1 and 4000 specified for n.

Specifying a minimum reserved space is useful if the data in a row is initially small but is expected to grow at a later date. If this is the case then, when migrating Informix Dynamic Server tables that contain VARCHAR columns consider increasing the PCTFREE value in the storage clause for these tables in the Oracle database. If this column is used in an index, then the PCTFREE values for the corresponding index storage should also be considered. For indexes based on VARCHAR columns, Informix Dynamic Server allocates the maximum storage.

Comparison Semantics

Informix Dynamic Server VARCHAR values are compared to other VARCHAR values and to character values in the same way that character values are compared. The shorter values are blank-padded until the values have equal lengths, then they are compared for the full length.

Oracle VARCHAR2 comparisons are made using non-padded comparison semantics. Trailing blanks are important and are included in the comparison. Two values are only equal if they have the same characters and are of equal length.

Oracle CHAR comparison uses blank-padded comparison semantics, similar to the way Informix Dynamic Server compares CHAR and VARCHAR data. If two values have different lengths, Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values, character by character, up to the first character that differs. So two values that are different only in the number of trailing blanks are considered equal.

This is important behavior for the migration of the applications. It is possible for some comparisons on Informix Dynamic Server VARCHAR columns may fail when migrated to Oracle VARCHAR2 columns where trailing blanks are involved. To offset this, you may need to use RTRIM() on all columns in a comparison to strip off the trailing blanks.

Collating VARCHAR

The main difference between the NVARCHAR and VARCHAR datatype is the difference in collation sequencing. NVARCHAR character collation order depends on the database server locale, while the collation of VARCHAR characters depends on the code set. For more information on how these collation methods are implemented in Oracle and the impact on Informix Dynamic Server, see the NCHAR(n) topic.


Oracle has a built-in VARCHAR datatype that is currently synonymous with the VARCHAR2 datatype. However, VARCHAR is reserved for future use. In a later version of Oracle, the definition of VARCHAR may change and since VARCHAR2 is fully supported, the VARCHAR2 datatype is used to store variable-length character strings to avoid any possible changes from the current behavior.


The Informix Dynamic Server CHARACTER VARYING datatype is the ANSI-compliant format for character data of varying length. The Informix Dynamic Server VARCHAR datatype supports the same functionality and is treated as one in the database server. This datatype is treated the same as the VARCHAR datatype and migrates to the Oracle VARCHAR2 datatype. For more information, see the VARCHAR(m,r) topic.


The Informix Dynamic Server NVARCHAR(m,r) datatype stores data of varying length, similar to VARCHAR, except that it compares data in the order that the locale specifies.

The Informix Dynamic Server NVARCHAR(m,r) datatype is migrated to the Oracle VARCHAR2(n) datatype.

For more information on migration issues, see the VARCHAR(m,r) and NCHAR(n) topics.


The DATE datatype stores the calendar date and the default display format is mm/dd/yyyy where mm is the month (01-12), dd is the day of the month (01-31) and yyyy is the year (0001-9999).

The DATE values are stored as integers thus DATE can be used in arithmetic expressions. For example, subtracting a DATE value from another DATE value returns the number of days that have elapsed between the two dates.

Subtracting two Oracle DATE datatypes from each other returns the number of days between the two dates. If only calendar dates are stored in Oracle, then the default time of 12:00:00AM (midnight) is also stored, so any subtraction results in a whole number indicating the number of days between the two dates. For the month, Informix Dynamic Server accepts a number value of either 1 or 01 for January, and so on. Similarly, for the day, Informix Dynamic Server accepts either 1 or 01 for the first day of the month. This is also true in Oracle.


The Informix Dynamic Server DATETIME datatype stores an instant in time expressed as a calendar date and time of day. The precision that a DATETIME value is stored can be chosen, with the precision ranging from a year to a fraction of a second. DATETIME in effect is a family of 28 datatypes.

The Oracle DATE datatype matches just one of the 28 datetime types, DATETIME YEAR TO SECOND.

The Informix Dynamic Server plug-in stores DATETIME values as Oracle DATE values, losing the FRACTION part of DATETIME. If you need to keep the fraction part of DATETIME, before migration, add a new column to the table and store the fraction part as a DECIMAL with the appropriate precision migrated to the appropriate NUMBER datatype.

Any DATETIME table columns that do not store a particular precision use the Oracle defaults. The defaults for Oracle DATE are the first day of the current month and 12:00:00AM (midnight).

For applications that need to manipulate various DATETIME precisians, the SQL code needs to be changed. For example, if a column is defined as MONTH TO DAY, and contains two values, date1: March 10 and date2: February 18 shown as (mm/dd) 03/10 and 02/18. If these values are stored in Oracle DATE (if the current year is 1999, the values would be date1: 1999/03/10 12:00:00 and date2:1999/02/18 12:00:00 respectively. The expression date1 - date2 UNITS DAY returns 20 days. However, if the year was 2000, then the expression date1 - date 2 UNITS DAY returns 21 days.

Using a combination of TO_DATE and TO_CHAR and appropriate date format masks, the year the DATE was stored can be replaced with the current year for use in the expression. The following is an example of this combination:

'MM-DD-YYYY'), 'MM-DD'), 'MM-DD'), 'MM-DD-YYYY') from dual; 

Oracle DATE Arithmetic

Subtraction of DATE returns days. Because each date contains a time component, most results of date operations include a fraction. The fraction indicates a portion of one day. For example, 1.5 days is 36 hours.

The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31 day month.

You cannot add dates, but another Oracle function available for date arithmetic is ADD_MONTHS(date,n). To add days to a date, add a number constant to the date.


Evaluate the logic of the addition or subtraction. Remember you can have months that are 28, 29, 30, or 31 days and you can have years that are 365 or 366 days.


Currently, there is no corresponding Oracle datatype for the Informix Dynamic Server INTERVAL datatype.

The Informix Dynamic Server INTERVAL datatype can be defined as one of 18 different precisions, YEAR TO YEAR, YEAR TO MONTH, MONTH TO MONTH, DAY TO DAY and so on right down to FRACTION TO FRACTION(f). These are divided into two classes, YEAR TO MONTH, and DAY TO FRACTION.




Informix Dynamic Server default precision for YEAR





If the largest qualifier value and the smallest qualifier value are not the same, then the INTERVAL column is migrated to CHAR(30).

Manipulating Oracle DATE with Informix Dynamic Server INTERVAL Values

Numeric constants can be added or subtracted from the Oracle DATE datatype -- to that Informix Dynamic Server DATE and DATETIME datatypes are mapped -- and are treated in terms of days. Therefore any operations involving the second class of Informix Dynamic Server INTERVAL, DAY TO FRACTION must be expressed as a fraction in terms of days. For example,


should be expressed as

SYSDATE + 10.5

To handle addition and subtraction of the first class of INTERVAL, YEAR TO MONTH, the INTERVAL needs to be expressed in terms of months and passed as a parameter, along with the date.

The Oracle function ADD_MONTHS(date,n) can be used for arithmetic:


should be expressed as


You do have the option to migrate all INTERVAL columns as CHARACTER(30) preserving all details, including subsecond information. However, the application must manipulate this data appropriately, using TO_DATE() and others.


Informix Dynamic Server DECIMAL datatype can take two forms:

DECIMAL(p) floating point

DECIMAL(p) floating point stores decimal floating point numbers up to a maximum of 32 significant digits.

The total number of significant digits is p. This is optional, DECIMAL is treated as DECIMAL(16). DECIMAL(p) has an absolute values range of between 10-130 and 10124.

In an ANSI-compliant Informix Dynamic Server database, DECIMAL(p) defaults to DECIMAL(p,0). If only p is specified, s is actually stored as 255 in the catalog tables.

In Oracle, Informix Dynamic Server DECIMAL(p) floating point values are always stored as NUMBER. It has 38 significant digits since it is not possible to restrict the total number of significant digits for storing a floating-point number. Oracle can store negative and positive values in the range 1.0x10-130 and 9.9...9x10125, which is 38 nines followed by 88 zeros. NUMBERS are stored in scientific notation. Leading and trailing zeros are not stored.

Since Oracle can store floating-point numbers with a greater precision than Informix Dynamic Server, there should be no loss of precision after the migration to Oracle.

DECIMAL(p,s) fixed-point

The precision is p with a range 1 to 32. The number of digits to the right of the decimal place is s. Numbers < 0.5x10-s have the value 0.

In Oracle, DECIMAL(p,s) maps to NUMBER(p,s).


The MONEY datatype is always a fixed-point number with a maximum 32 significant digits.



The Informix Dynamic Server MONEY datatype is represented as DECIMAL. The Informix Dynamic Server MONEY(p,s) datatype maps to Oracle NUMBER(p,s).


The Informix Dynamic Server INTEGER datatype is mapped to NUMBER(10).

Range Boundaries

The Informix Dynamic Server INTEGER datatype can store values in the range -2,147,483,647 to 2,147,483,647. If a value to be inserted is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -9,999,999,999 to 9,999,999,999 to be inserted without raising an error. If mapped, INTEGER columns should enforce the original range, then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -2,147,483,647 to 2,147,483,647.


Informix Dynamic Server stores INTEGER as a signed binary integer and requires 4 bytes per value.

Oracle stores numeric data in variable length format, in scientific notation. The smallest storage space Oracle uses to represent an INTEGER is 2 bytes, 12 bytes is the maximum storage space required. The storage space for the value depends on the number of significant digits.

Inserting Fractions

If you insert 7.2 and 7.8 into Informix Dynamic Server INTEGER datatype, fractional parts are truncated, therefore the values 7 and 7 are stored.

If you insert 7.2 and 7.8 into Oracle NUMBER(10), fractional parts are rounded, therefore the values are stored as 7 and 8.

It may be necessary to check application code and logic to ensure there is no unexpected behavior. This is because it is assumed that fractional parts of any number are automatically truncated when inserted into the Informix Dynamic Server database.


The Informix Dynamic Server INT datatype is a synonym for INTEGER


The Informix Dynamic Server SMALLINT datatype is mapped to NUMBER(5).

Range Boundaries

The Informix Dynamic Server INTEGER datatype can store values in the range -32,767 to 32767. If a value is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -99,999 to 99,999 to be inserted without raising an error. If mapped, INTEGER columns should enforce the original range then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -32,767 to 32767.


Informix Dynamic Server SMALLINT datatype values take up 2 bytes per value.

Oracle stores a values in an NUMBER(5) datatype with a minimum of 2 bytes and a maximum of 4 bytes

Inserting Fractions

For information on differences in behavior for Informix Dynamic Server INTEGER and Oracle NUMBER, see the Inserting Fractions topic.


The Informix Dynamic Server SERIAL datatype creates a column in a table that auto-increments an INTEGER value every time a row is inserted into the table. By default, if the column is simply defined as SERIAL, the column begins inserting with the value 1. Other starting values can be set by defining the column. For example, SERIAL(1000)creates a column that begins inserting with the value 1000. The starting number cannot be 0 and the maximum value SERIAL can reach, or be initially set to, is 2,147,483,647. After reaching the maximum value, the SERIAL column resets to 1. Only one SERIAL column may be defined for an Informix Dynamic Server table. The SERIAL datatype is not automatically a unique column, a unique index must be created for this column to prevent duplicate serial numbers.

The Migration Workbench for Informix Dynamic Server maps the Informix Dynamic Server SERIAL datatype to an Oracle NUMBER(10) datatype and flags the column as an auto-increment column. The Migration Workbench also creates a NOT NULL CONSTRAINT on that column, as is the case with Informix Dynamic Server SERIAL columns.

The Migration Workbench creates an Oracle sequence and an Oracle trigger on the table that contained the SERIAL column. The trigger fires every time a row is inserted into the table. It gets the next value in the sequence and inserts it into the field.

For example, the following JOBS table was migrated to Oracle and the JOB_ID column was originally defined as an Informix Dynamic Server SERIAL datatype:


               JOB_DESC VARCHAR2 (50) NOT NULL,
               MIN_LVL NUMBER (5),
               MAX_LVL NUMBER (5)) 
TABLESPACE PUBS; REM REM Message : Created Sequence: clerk.SEQ_11_1 REM User : system CREATE SEQUENCE clerk.SEQ_11_1 START WITH 1 / REM REM Message : Created Sequence Trigger: clerk.TR_SEQ_11_1 REM User : system CREATE TRIGGER clerk.TR_SEQ_11_1 BEFORE INSERT ON clerk.JOBS FOR EACH ROW BEGIN SELECT clerk.SEQ_11_1.nextval INTO :new.JOB_ID FROM dual; END; /

The Oracle trigger and sequence is created after a table with a SERIAL column is migrated. The sequence is created using the option START WITH 1. If the data for this table is not moved automatically by the Migration Workbench, the sequence starts inserting with 1.

If the table data is selected to be moved automatically by the Migration Workbench while database table objects are created, the Migration Workbench creates the trigger and sequence after the data has been moved. Before the sequence is created the Migration Workbench selects the maximum value from the SERIAL column (for example, 1231) and add 1 to this value and use it as the START WITH value in the CREATE SEQUENCE statement; as follows:


In the resulting Oracle database inserts to the table continue to auto-increment by one a value for the old serial column every time a row is inserted into the table.

Additional Oracle Sequence Options for Informix Dynamic Server SERIAL Migrations

The Migration Workbench uses the following command to create the sequence:

CREATE SEQUENCE sequence_name START WITH integer;

The Oracle CREATE SEQUENCE command has several options, the only option that is used is the START WITH option.

Many of these options have defaults that are what would be required to replicate the Informix Dynamic Server SERIAL datatype. However, there are a couple of settings that can be altered on the SEQUENCE to make it behave even more closely to the Informix Dynamic Server SERIAL datatype.

Option Description

START WITH integer

Specify the start sequence value.

For more information, see Resetting the Start Value.


Specify the interval between sequence numbers. If this value is negative, then the sequence descends. For Informix Dynamic Server, use the Oracle default of 1.

MAXVALUE integer

NOMAXVALUE is the default setting. For Informix Dynamic Server, set this to 2147483647 to override the Oracle default value.


Specify NOMAXVALUE to indicate a maximum value of (10^27)-1, twenty eight 9's in a row, for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE integer

Specify the sequence minimum value. For Informix Dynamic Server, indicate 1 as the value so that if the sequence ever restarts, it restarts with this value.


Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10^26) for a descending sequence. This is the default. For Informix Dynamic Server, use the default because the default INCREMENT BY value of 1, we get a default minimum value of 1.


Specify CYCLE to indicate that the sequence continues to generate values after reaching either maximum or minimum value. After an ascending sequence reaches maximum value, it generates minimum value.


For Informix Dynamic Server, the column resets to 1 after reaching 2147483647.


Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching maximum or minimum value. This is the default. For Informix Dynamic Server, override this default Oracle behavior to CYCLE.

CACHE integer

Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access.


Specify NOCACHE to indicate that values of the sequence are not preallocated.

If both CACHE and NOCACHE are omitted, Oracle caches 20 sequence numbers by default. For Informix Dynamic Server, since MINVALUE is 1, MAXVALUES is at least 2,147,483,647 and INCREMENT is 1, then there should be no problems with the default. If the table is a target of high activity then, the CACHE values may have to be reviewed along with FREELISTS, INITTRANS, MAXTRANS, and others.)

In Oracle, all the options that were used to create a sequence can be altered except for START WITH.

Resetting the Start Value

To restart an Oracle sequence at a different number, you must drop and re-create it.

The following Table 2-5 shows that Informix Dynamic Server changes the next value to be used in a SERIAL column, provided 1000 is not less than the current maximum for the column.

Table 2-5 Serial Column Comparison
Informix Dynamic Server Oracle
SERIAL(1000) );

MAXVALUE 2147483647 CYCLE;

Some Exceptional Cases

Occasionally the migrated SERIAL column does not behave as it would in Informix Dynamic Server.

Example 1

If the last number values inserted into a table are deleted from the table, the migrated tables sequence begin before the next.

If the table was created as follows:

CREATE TABLE table_with_serial_col ( col1 SERIAL, col2 CHAR(5) )

and after several inserts on the table, as follows:

INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 1]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 2]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 3]

the definition is changed, as follows:

ALTER TABLE table_with_serial_col MODIFY ( col1 SERIAL(1000) )

If the database is migrated at this point, execute the following command on Informix Dynamic Server:

INSERT INTO table_with_serial_col VALUES ("XXX");    

results in the new row with a value of 1000 for col1. If you execute the same command in the migrated Oracle environment, the new row would have a value of 4 for col1.

Example 2

Another possibility follows:

INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 1]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 2]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 3]

DELETE FROM table_with_serial_col WHERE col1 = 3;

If the database is migrated at this point, execute the following command on Informix Dynamic Server:

INSERT INTO table_with_serial_col VALUES ("XXX");    

results in the new row with a value of 4 for col1. If you execute the same command in the migrated Oracle environment, the new row would have a value of 3 for col1.

It is possible that this would have no effect on the execution of the application or the integrity of the data. The only dependency is that this value is unique and auto-incremental, but it may be useful to check the application logic if situations similar to the examples could occur.

How to examine current Informix Dynamic Server SERIAL values

For Informix Dynamic Server, set the SERIAL value to the values of sysmaster:systabinfo(ti_serialv) where sysmaster:systabinfo(ti_partnum) is the partnum of the table with the serial column.

select c.dbsname, a.owner, a.tabname, d.ti_serialv
  from systables a, syscolumns b, sysmaster:informix.systabnames c,   
sysmaster:informix.systabinfo d
 where (b.coltype = 6  OR b.coltype = 262)
   and a.tabid = b.tabid
   and a.tabid > 99
   and a.owner = c.owner
   and a.tabname = c.tabname
   and c.dbsname = "<DATABASENAME>"
   and c.partnum = d.ti_partnum;

Replace <DATABASENAME> as appropriate.

In the Oracle environment, use the following SQL statements to get the next sequence number to be generated for each sequence:

SQL> SELECT (<sequence_name>.CURRVAL+1) FROM DUAL;

Replace <sequence_name> as appropriate.

Data Storage Concepts

This chapter provide a description of the conceptual differences (and in many cases, similarities) in data storage for Informix Dynamic Server and Oracle9i and Oracle8i databases.


The following are recommendations:

  1. The conceptual differences in the storage structures do not affect the conversion process directly.

  2. Both Oracle and Informix Dynamic Server have a way to control the physical placement of database objects:

    • IN dbspace for Informix Dynamic Server

    • TABLESPACE for Oracle.
  3. Storage information can be preserved when converting to Oracle. The decisions made when defining the storage of the database objects for Informix Dynamic Server should also apply for Oracle. Especially important are the initial object and physical object placement.

An Oracle database server consists of a shared memory area, several processes that access the database and maintain data integrity and consistency, the Oracle Instance, and a database that stores the data.

An Informix Dynamic Server database server also consists of a shared memory area, several process to access the data and maintain data integrity and consistency, however a single Informix Dynamic Server database server can support several separate databases.

A Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that link a database to the physical disks that hold the data. A tablespace is created from one or more data files. Data files are files in the file system or an area of disk space specified by a raw device. A tablespace can be enlarged by adding more data files.

An Oracle database consists of a least a SYSTEM tablespace, where the Oracle tables are stored. It can also consist of user defined tablespaces. A tablespace is the logical storage location for database objects. For example, you can specify where a particular table or index gets created in the tablespace.

The size of a tablespace is determined by the amount of disk space allocated to it. Each tablespace is made up of one or more data files.

Data Storage Concepts Table

Table 2-6 Data Storage Concepts in Informix Dynamic Server and Oracle  
Informix Dynamic Server Oracle


Physical disk space is allocated in terms of chunks. A chunk can be from a file system or raw disk space.

The root dbspace is mapped to a chunk specified by a raw device name or the full path name of a file in a file system, through the initialization file, on_config.

Data Files

One or more data files are created for each tablespace to physically store the data of all the logical structures in a tablespace.

Page and Blobpage


A chunk has its space divided into pages, each with a specified number of bytes. Any I/O must be performed in page units.


A blobpage stores BYTE and a TEXT data within a blobspace. The size of blobpage is a unit of disk allocation selected by the user who creates the blobspace, and can vary from blobspace to blobspace.

Data Block

One data block corresponds to a specific number of bytes of physical space on disk. The database block size can be specified when creating the database.


Extent is the allocation of disk space to a database object in units of physically contiguous pages and cannot span chunk boundaries. All database objects have space allocated in increment of one extent. For a single table, extents can be located in different chunks of the same dbspace. Within an extent, all data pertains to a single tblspace.


An extent is a specific number of contiguous data blocks, obtained in a single allocation.


The total diskspace allocated to a table includes pages allocated to:

  • data

  • indexes

  • storage of blob data (BYTE or TEST) in the dbspace (excluding pages storing blob data in separate blobspace)

  • tracking page usage within the table extents


A segment is a set of extents allocated for a certain logical structure. The extents of a segment may or may not be contiguous on disk, and may or may not span datafiles.

Physical Log

A unit of contiguous disk pages containing "before images" of data that has been modified during processing.

Logical Log

Logical log file is the name of each of these additions of space. This log records logical operations during on-line processing. All transaction information is stored in the logical files as a database is created with the transaction log.

Redo Log Files

Each database has a set of two or more redo log files. All changes made to the database are recorded in the redo log. Redo log files are critical in protecting a database against failures.

Root dbspace

The root dbspace stores information about all databases created.

System Tablespace

Oracle Control Files

Each database has a control file. This file records the physical structure of the database, such as the database name, name and location of the database data files and redo logs.

Dbspace and Blobspace


Database objects are stored in a dbspace, which is a minimum of one piece of physical disk or chunk.

BYTE and TEXT (Binary Large Objects, or BLOBs) data can be stored in a dbspace, but performance may suffer if the BLOBs are larger than two dbspace pages.

The ROOT dbspace is the name of the first dbspace created. Specific pages and internal tables in the ROOT dbspace describe and track all other dbspaces, blobspaces, and tblspaces.


A blobspace provides a storage area for TEXT and BYTE data using a larger and more efficient space allocation mechanism more suited to large objects as opposed to storing them in a dbspace with more traditional data types.


A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. A database typically has one system tablespace an one or more user tabletops.

Go to previous page Go to next page
Copyright © 1998, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index