CODEWITHSUNDEEP
oracle-database
Satria
Satria

Reputation: 326

ORA-12899: value too large for column - on export/import, both BYTE semantics, not CHAR

I have an Oracle DB on one Windows machine and perform an export of all tables + contents using SQL developer 4.0.2.15. File format is plain text SQL, ANSI ISO-8859-1. The line endings are done in UNIX style (1 byte). The table / column definitions are like:

"PRODUCTNAME" VARCHAR2(120 BYTE),

When I execute this .sql file on the target Windows machine, tables are created and inserts are done. But some items that have exactly 120 bytes in the PRODUCTNAME (including 1 line feed character) fail to insert on the target machine. I get:

ORA-12899: value too large for column "DBNAME"."TABLENAME"."PRODUCTNAME" (actual: 121, maximum: 120)

I don't understand why. I have no 2-byte characters in this particular string, and the export is definitely done using 0x0A as line break character (checked via Notepad++ status line and its HexView Plugin). I use drag&drop to open the .sql file in the target machine's SQL developer (same version). When I copy the productname with its line break into Notepad++ via clipboard, it count's 120 bytes of length.

I do not understand, why in Oracle developer the script counts this 1 extra character.

I have searched for this in google and found topics here on SO among others, but they don't help me or I don't fully understand them.

What do I miss? Please help!

Upvotes: 0

Views: 2534

Answers (1)

Littlefoot
Littlefoot

Reputation: 142768

Create a temporary table (say, TEST) having PRODUCTNAME VARCHAR2(200). Import data into it. Check rows that exceed length of 120 characters.

You might need to use the DUMP function, such as 

select dump(productname) from test

It'll show you actual length of data stored in that column. Just for example:

SQL> select ename, dump(ename) dump_e from emp where rownum = 1;

ENAME      DUMP_E
---------- ----------------------------------------
KING       Typ=1 Len=4: 75,73,78,71
                 -----
                 this!

SQL>

[EDIT: TRIM example]

SQL> with test (id, col) as
  2    (select 1, 'abc'    from dual union
  3     select 2, 'def '   from dual union
  4     select 3, ' ghi  ' from dual
  5    )
  6  select '#' || col       || '#' col,
  7         '#' || trim(col) || '#' new_col
  8  from test
  9  order by id;

COL      NEW_COL
-------- --------
#abc#    #abc#
#def #   #def#
# ghi  # #ghi#

SQL>

Upvotes: 1

Related Questions