1.创建测试表HOEGH
点击(此处)折叠或打开
-
SQL>
-
SQL> create table HOEGH(id number,name varchar2(30),loc varchar2(30));
-
-
Table created
-
-
SQL> insert into HOEGH values(1,\'Sun\',\'Huaguoshan\');
-
-
1 row inserted
-
-
SQL> insert into HOEGH values(2,\'Zhu\',\'Gaolaozhuang\');
-
-
1 row inserted
-
-
SQL> insert into HOEGH values(3,\'Sha\',\'Liushahe\');
-
-
1 row inserted
-
-
SQL> commit;
-
-
Commit complete
-
-
SQL>
-
SQL> select * from hoegh;
-
-
ID NAME LOC
-
---------- ------------------------------ ------------------------------
-
1 Sun Huaguoshan
-
2 Zhu Gaolaozhuang
-
3 Sha Liushahe
-
- SQL>
2.修改字段LOC类型报错
点击(此处)折叠或打开
-
SQL>
-
SQL> desc hoegh
-
Name Type Nullable Default Comments
-
---- ------------ -------- ------- --------
-
ID NUMBER Y
-
NAME VARCHAR2(30) Y
-
LOC VARCHAR2(30) Y
-
-
SQL>
-
SQL> alter table hoegh modify(loc blob);
-
-
alter table hoegh modify(loc blob)
-
-
ORA-22858: 数据类型的变更无效
-
- SQL>
3.删除原有字段LOC，新建blob字段
点击(此处)折叠或打开
-
SQL> alter table hoegh drop column loc;
-
-
Table altered
-
-
SQL> alter table hoegh add loc blob;
-
-
Table altered
-
-
SQL> desc hoegh
-
Name Type Nullable Default Comments
-
---- ------------ -------- ------- --------
-
ID NUMBER Y
-
NAME VARCHAR2(30) Y
-
LOC BLOB Y
-
- SQL>
疑问：
1.上述解决方案，被修改列的数据全部丢失，如果要保留数据，又该如何处理呢？
2.后来试了其他数据类型，例如，number类型改为blob类型也报错，不明白其中深层次的原因，还请路过的不吝赐教。