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?

What do you mean by "different Unicode"? Does the table have any `CLOB` data type column? — Wernfried Domscheit, Mar 04 '22 at 13:17
Are the tables really equal? `CHAR(15)` is a **very** unusual data type. `VARCHAR2(15)` would be more common. — Wernfried Domscheit, Mar 04 '22 at 13:20
Those are two identical tables, Destination table have Unicode utf-8 — JiboOne, Mar 04 '22 at 13:30
@WernfriedDomscheit I changed char(15) to varcgar2(15) but nothing changed. Error is here. — JiboOne, Mar 04 '22 at 13:33
And the source table? In which program do you run the SQL? And what so you mean by "special character"? — Wernfried Domscheit, Mar 04 '22 at 13:47
@WernfriedDomscheit this value have a problem on production environment "61921623 " I don't know exactly which symbol have a problem. — JiboOne, Mar 04 '22 at 13:52
@WernfriedDomscheit I use plsql developer to test the procedure, but on production env. scheduled job is calling this procedure. — JiboOne, Mar 04 '22 at 13:53

score 1 · Accepted Answer · answered Mar 07 '22 at 13:08

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.

answered Mar 07 '22 at 13:08

JiboOne

1,438
4
22
55

1

Thank you so much for this! I've been running in circles because all other solutions were pointing into wrong directions. This finally solved my issue. – Joel May 11 '23 at 12:41

Toni Antunović · Answer 2 · 2022-03-04T13:15:39.953

0

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;

edited Mar 04 '22 at 13:15

answered Mar 04 '22 at 13:09

Toni Antunović

551
3
5

As I mentioned in my post I've already tried Insert/Select statement and it worked. but I have to use Loop in my case. – JiboOne Mar 04 '22 at 13:14

score 0 · Answer 3 · answered Mar 04 '22 at 13:16

0

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;

answered Mar 04 '22 at 13:16

Wernfried Domscheit

54,457
9
76
110

I have to use LOOP, I've just written a pseudo code. There is some login inside LOOP. – JiboOne Mar 04 '22 at 13:29

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

3 Answers3