long列是不允许出现在 create table xx as select * from yyy 里,否则报如下错误:
SQL> create table mytest1(remark) as select remark from workflow_requestLog where rownum<10;
create table mytest1(remark) as select remark from workflow_requestLog where rownum<10
*
第 1 行出现错误:
ORA-00997: 非法使用 LONG 数据类型
oracle建议使用clob\blob\nclob来代替,如下:
create table mytest3(remark) as select to_lob(remark) from workflow_requestLog where rownum<10;
--可以这样创建,不过默认类型就成了clob
然后,使用dbms_lob.substr取出clob中的字符:
select dbms_lob.substr(remark,4000,1) from mytest3;
直接在long列上先转换为lob,在转换为varchar则是不允许的,如下:
SQL> select dbms_lob.substr(to_lob(remark),1,500) from workflow_requestLog where rownum<10;
select dbms_lob.substr(to_lob(remark),1,500) from workflow_requestLog where rownum<10
*
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 -, 但却获得 LONG