oracle-databaseplsqlunicodedatabase-link

ORA-01406 when copying data from one Oracle database to another with different Unicode


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?


Solution

  • 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.