利用透明网关复制INFORMIX表到ORACLE导致列名为小写

采用CTAS方式，利用ORACLE的透明网关将INFORMIX数据库中的表迁移到ORACLE中后，
发现列都被置为了小写，如下：

 

SQL> desc PROJECTLIST
 Name Null? Type
 ----------------------------------------- -------- ----------------------------

 projectname CHAR(180)
 projectversion NOT NULL VARCHAR2(90)
 productid NOT NULL CHAR(90)
 times NOT NULL NUMBER(5)
 username CHAR(120)
 company CHAR(120)
 updatedate DATE
 primaryversion CHAR(90)
 flag1 CHAR(90)
 flag2 CHAR(90)
 flag3 CHAR(90)
 flag4 CHAR(90)
 flag5

这将导致下面的查询失败：

 

SQL> select projectname from PROJECTLIST where rownum=1;
select projectname from PROJECTLIST where rownum=1
       *
ERROR at line 1:
ORA-00904: "PROJECTNAME": invalid identifier

必须将里面用引号引起来才行：

SQL> select "projectname" from PROJECTLIST where rownum=1;

projectname
--------------------------------------------------------------------------------

TEST

除了通过ALTER TABLE RENAME COLUMN 的方法来修改之外，由于列名是记录到数据字典里的，我们可以通过修改数据字典信息来解决这个问题。

如下：

 

SQL> select object_id
  2 from dba_objects where object_name='PROJECTLIST';

           OBJECT_ID
--------------------

               53304

SQL> select name
  2 from sys.col$ where obj#=53304
  3 order by col#;

NAME
------------------------------------------------------------

projectname
projectversion
productid
times
username
company
updatedate
primaryversion
flag1
flag2
flag3
flag4
flag5

13 rows selected.


SQL> update sys.col$ set name=upper(name) where obj#=53304;

13 rows updated.

SQL> set pagesize 30
SQL> select name
  2 from sys.col$ where obj#=53304
  3 order by col#;

NAME
------------------------------------------------------------

PROJECTNAME
PROJECTVERSION
PRODUCTID
TIMES
USERNAME
COMPANY
UPDATEDATE
PRIMARYVERSION
FLAG1
FLAG2
FLAG3
FLAG4
FLAG5

13 rows selected.

SQL> commit;

Commit complete.

可以看到列名已经改回到大写。

 

再次执行查询：

 

SQL> select projectname from PROJECTLIST where rownum=1;
select projectname from PROJECTLIST where rownum=1
       *
ERROR at line 1:
ORA-00904: "PROJECTNAME": invalid identifier

SQL> alter system flush shared_pool;

System altered.

SQL> select projectname from PROJECTLIST where rownum=1;

PROJECTNAME
--------------------------------------------------------------------------------

TEST

 

第一次查询只所以报错是由于数据字典的信息已经缓存在row cache中，需要刷新一下Shared pool才行。