SQL> desc liuwenhe.DPT_DEATAIL_20150718
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT_ID NOT NULL NUMBER(38)
DETAIL LONG
URL VARCHAR2(1000)
SQL> desc pd_zh_cn.tb_goods;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
CLASSIFY_ONE NUMBER(10)
CLASSIFY_TWO NUMBER(10)
CLASSIFY_THREE NUMBER(10)
CLASSIFY_FOUR NUMBER(10)
SOURCE_STATION_ID VARCHAR2(20)
SHOP_ID NOT NULL NUMBER(10)
BRAND_ID NUMBER(10)
IS_ONLINE NUMBER(2)
TITLE NOT NULL VARCHAR2(4000)
IMG_URL VARCHAR2(4000)
DETAIL_ADDRESS VARCHAR2(4000)
执行更新:
update pd_zh_cn.tb_goods a set a.detail_address=
(select b.DETAILfrom liuwenhe.DPT_DEATAIL_20150718 b where b.product_id=a.id);
报错:‘ORA-00997: 非法使用 LONG 数据类型’
通过sys.dbms_metadata_util.long2varchar函数,将long属性转换为varchar2
update pd_zh_cn.tb_goods a set a.detail_address=
(select (sys.dbms_metadata_util.long2varchar(1000, 'liuwenhe.DPT_DEATAIL_20150718', 'DETAIL', ROWID))
from liuwenhe.DPT_DEATAIL_20150718 b where b.product_id=a.id);
小结 oracle应该尽量避免使用long类型,取而代之 blob