I have two identical tables: original_table, destination table in two different Oracle database.
-- Oracle 1
create table original_table
(
my_id NUMBER(11) not null,
my_fld CHAR(15),
)
-- Oracle 2
create table destination_table
(
my_id NUMBER(11) not null,
my_fld CHAR(15),
)
I'm copying data from original_table to destination_table using the procedure and a database link. Here is a pseudocode version.
PROCEDURE COPY_DATA AS
BEGIN
FOR c_cursor IN (SELECT my_id ,my_fld FROM original_table@dblink)
LOOP
INSERT INTO destination_table
VALUES (c_cursor.my_id, c_cursor.my_fld);
END LOOP;
END;
Sometimes Oracle throws ERROR, when special character is inserted in original_table.my_fld column.
ORA-01406: fetched column value was truncated
This is because those two databases have different Unicode and I'm selecting data in LOOP. I tried to write select-insert statement outside of LOOP and it worked fine.
Can you tell me how to fix this problem?
I used UNISTR function for my string field.
FOR c_cursor IN (SELECT my_id ,UNISTR(my_fld) FROM original_table@dblink)
LOOP
INSERT INTO destination_table
VALUES (c_cursor.my_id, c_cursor.my_fld);
END LOOP;
It fixed the problem.