ORA-00997: illegal use of LONG datatype-CodePudding

I know there are a lot of similar question to this but mine is a bit different.

I am trying to do a stored procedure, migrating data from MsSQL to Oracle, and on this particular table the data type is varchar(2000) on MsSQL and nvarchar2(2000) on Oracle. I've used TO_LOB() to fix the error

[ORA-00932: inconsistent datatypes: expected CHAR got LONG]

since I found many use it as a solution. But then I encounter this error, where many recommends the use of TO_LOB() as a solution, except it's not.

I'm not a skilled or experienced user for Oracle and my knowledge is very little. Below is the example of the code I am using to create the procedure.

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;

CodePudding user response：

try using to_clob() it works withe below example...

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;

CodePudding user response：

I found a solution and it is pretty simple actually. Just put it in a loop!

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;