Reserved Words - Informix to Oracle Migration

Informix and Oracle have reserved words that cannot be used as an identifier (table or column name i.e.).

But the reserved words in Informix and Oracle are different, and when you migrate a table from Informix to Oracle, the table name or a column that is non-reserved word in Informix can be a reserved word in Oracle.

Informix:

  -- COMMENT is not a reserved word in Informix
  CREATE TABLE cities
  (  
      name VARCHAR(70),
      comment VARCHAR(100)
  );
  # Table created.

Oracle:

  -- But COMMENT is a reserved word in Oracle
  CREATE TABLE cities
  (  
      name VARCHAR2(70),
      comment VARCHAR2(100)
  );
  # ERROR at line 4: 
  # ORA-00904: : invalid identifier

You can rename or quote the column name in Oracle:

Oracle:

  -- Quote COMMENT column in Oracle
  CREATE TABLE cities
  (  
      name VARCHAR2(70),
      "comment" VARCHAR2(100)
  );
  # Table created

Note that after you quoted the name, you must specify exactly the same case when referring to the column in all SQL statements:

  SELECT "comment" FROM cities;
  # no rows selected
 
  SELECT "Comment" FROM cities;
  # ERROR at line 1:
  # ORA-00904: "Comment": invalid identifier
 
  SELECT "COMMENT" FROM cities;
  # ERROR at line 1:
  # ORA-00904: "COMMENT": invalid identifier

So you have to normalize the references to the quoted column in all SQL statements, and cannot mix “comment”, “Comment” and “COMMENT”.

List of words that are non-reserved in Informix and can be used as identifiers, but reserved in Oracle and must be quoted or renamed during the migration from Informix to Oracle:

• Informix to Oracle Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - April 2013.