文档介绍：INFORMIX VS ORACLE
一、系统架构
ds_init,ds在INFORMIX中代表instance下的某个database;而在ORACLE中代表某个user.
访问数据库进行相关操作时:ess database-name;ORACLE中用sqlplus user/passwd
g_user的获取方式不同(具体见下图)
UNIX
aoos901
Instance
Instance
aoos901
DB
ds_init
ds1
ds
ds_init
ds
ds1
DB使用者
Informix
Oracle
ds/ds
g_user from SYSUSERS
g_user=fgl_getenv('LOGNAME')
二、$FGLPROFILE
ORACLE环境会要求$FGLPROFILE设定如下,而INFORMIX不需做任何修改。
= "top"
= "ds_init"
= "ds_init"
= "ds_init"
= 1
三、两种SQL的差异
1. DATA TYPE
TIPTOP系统中会提供一个shell——i2odbsch ,一般存于$TOP/ora/bin下,专门用于将文件中informix的文法转换为oracle的文法。()
以下的表仅供参考,有些地方不太一样,比如:tiptop中char是转为varchar2,具体可以参考i2odbsch。
Data Types
Convert INFORMIX data types to ORACLE data types :
2. ROWID
INFORMIX中为INTEGER
ORACLE中为CHAR(18)
ROWIDs
INFORMIX rowids are INTEGERs while ORACLE rowids are provided as CHAR(18).
ORACLE ROWIDs are physical addresses of the rows.
Example of ORACLE 8 rowid : AAAA8mAALAAAAQkAAA
3. OUTER JOINS
IFX: A,OUTER B … WHERE A01=B01
ORA:A,B … WHERE A01=
Outer joins
INFORMIX SQL
SELECT ... FROM a, OUTER(b) WHERE =
SELECT ... FROM a, OUTER(b,OUTER(c)) WHERE = AND = AND =
ORACLE expects the (+) operator in the join condition
SELECT ... FROM a, b WHERE = (+)
SELECT ... FROM a, b, c WHERE = (+) AND = (+) AND = (+)
Example which does not work : "FROM OUTER(tab2), tab1 ".
2. Example : "WHERE = ".
3. Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"
Restrictions