ORA-00997:非法使用长数据类型

Question

我知道有很多类似的问题，但我的问题有点不同。

我正在尝试执行一个存储过程，将数据从MsSQL迁移到Oracle，而在这个特定的表上，数据类型是MsSQL上的varchar(2000)和Oracle上的nvarchar2(2000)。我使用TO_LOB()修复了错误

ORA-00932:不一致的数据类型:期望CHAR变长

因为我发现很多人用它作为解决方案。但随后我遇到了这个错误，许多人建议使用TO_LOB()作为解决方案，但事实并非如此。

我不是Oracle的熟练或有经验的用户，我的知识也很少。下面是我用来创建这个过程的代码示例。

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_LOB(data2) data2, data3, data4
   FROM default.table@dblink
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);
END default_proc;

Answer 1

我找到了一个解决方案，其实很简单。把它放在一个循环里！

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

Answer 2

尝试使用to_clob()，它在下面的示例中工作..。

create table test (col varchar2(2000));

 insert into test
   select to_clob('I agree with that, unfortunately many sites are blocked, like message boards and forum/blog sites/etc. Im actually looking first at asktom and this forum before I proceed with google. Which actually points me to the TO_LOB function, which in our environment throws an error message shown above.Follow up question: This TO_LOB function looks simple and easy to use but as shown above in my post it throws an error message. Have I missed something or is there something in our environment that affected the behavior of this function?') from dual union all
   select to_clob('I agree with that, unfortunately many sites are blocked, like message boards and forum/blog sites/etc. Im actually looking first at asktom and this forum before I proceed with google. Which actually points me to the TO_LOB function, which in our environment throws an error message shown above.Follow up question: This TO_LOB function looks simple and easy to use but as shown above in my post it throws an error message. Have I missed something or is there something in our environment that affected the behavior of this function?')    from dual;
   
   
   select * from test;