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

Question

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?

Answer 1

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.

Answer 2

I got this error: "ora-01406 fetched column value was truncated" when trying to bind / link oracle tables from Microsoft Access through ODBC.

If there is any table which name is greater than 30 characters, this error may be issued. Note that from Oracle Database 12.2, up to 128 characters are allowed, but the ODBC drivers seems no to support it yet.

Answer 3

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;

Answer 4

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;