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