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

Converting Identifiers Containing Reserved Words

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”.

Non-Reserved Words in Informix But Reserved Words in Oracle

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:

Non-Reserved in Informix but Reserved in Oracle
1 COMMENT
2 COMPRESS
3 IDENTIFIED
4 INITIAL
5 INTERSECT
6 MAXEXTENTS
7 MINUS
8 MLSLABEL
9 NOAUDIT
10 NOCOMPRESS
11 NOWAIT
12 NUMBER
13 OFFLINE
14 PCTFREE
15 ROWNUM
16 SUCCESSFUL
17 SYSDATE
18 UID
19 VALIDATE
20 VARCHAR2

More Information

About SQLines

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.

You could leave a comment if you were logged in.