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

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?

CodePudding user response：

If you just want to copy all data from one table to another u don t need cursor u can do it with sql inside a procedure. Try it hope it helps...

PROCEDURE COPY_DATA AS
BEGIN
   INSERT INTO [database].[schema].destination_table (column_list)
   SELECT column_list
   FROM [database].[schema].original_table
   WHERE condition;
END;

CodePudding user response：

Select and insert the data row-by-row is basically the slowest way you can do it. Use this one:

INSERT INTO destination_table (my_id ,my_fld)
SELECT my_id ,my_fld 
FROM original_table@dblink;