SQL Server迁移Oracle存储过程：用TO_LOB后遇ORA-00997错误求助

问题分析 #

你遇到的ORA-00997: illegal use of LONG datatype错误，根源是错误使用了TO_LOB()：TO_LOB()的作用是将LONG类型转换为CLOB类型，但你的目标列data2是NVARCHAR2(2000)字符类型，强行将数据转为LOB后插入字符列，会触发Oracle对LONG/LOB类型的使用限制。

另外，SQL Server的varchar(2000)通过dblink可能被映射为Oracle的LONG类型，这也是之前出现ORA-00932的原因，但直接用TO_LOB()不符合目标列的类型要求。

解决方案 #

不需要将数据转为LOB，而是直接将dblink过来的数据显式转换为NVARCHAR2类型，适配目标列的类型：

方案1：使用TO_NCHAR()转换 #

修改存储过程中的SELECT语句，用TO_NCHAR()将源数据转为Unicode字符类型：

CREATE OR REPLACE PROCEDURE default_proc
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE default_table';
   INSERT INTO default_table (data1, data2, data3, data4)
   SELECT data1, TO_NCHAR(data2) data2, data3, data4
   FROM default.table@dblink;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);
END default_proc;

方案2：使用CAST()强制转换 #

如果TO_NCHAR()无效，尝试用CAST()直接指定目标类型：

CREATE OR REPLACE PROCEDURE default_proc
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE default_table';
   INSERT INTO default_table (data1, data2, data3, data4)
   SELECT data1, CAST(data2 AS NVARCHAR2(2000)) data2, data3, data4
   FROM default.table@dblink;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);
END default_proc;

方案3：双重转换适配LONG映射 #

如果dblink确实将SQL Server的varchar(2000)映射为Oracle LONG类型，先转成VARCHAR2再转NVARCHAR2：

CREATE OR REPLACE PROCEDURE default_proc
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE default_table';
   INSERT INTO default_table (data1, data2, data3, data4)
   SELECT data1, CAST(TO_CHAR(data2) AS NVARCHAR2(2000)) data2, data3, data4
   FROM default.table@dblink;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);
END default_proc;

关键说明 #

• NVARCHAR2是Oracle的Unicode字符类型，和SQL Server的varchar（UTF-8编码）或nvarchar匹配，直接转换即可，无需转为LOB。
• 避免在字符类型列的插入操作中使用TO_LOB()，该函数仅适用于将LONG类型迁移到CLOB/BLOB列的场景。

内容的提问来源于stack exchange，提问作者Diaruys