Why am I getting ORA-01401: inserted value too large for column - when I'm not inserting?

Question

Here is some SQL to set up with a very simple table.

CREATE TABLE CC_TEST2 
  ("CURRENCYID" NUMBER NOT NULL ENABLE, 
"NAME" NVARCHAR2(255)) ;


insert into CC_TEST2 (select 1,'Testing issue'from dual);
commit;

Then this recreates the issue

    SELECT (step.Name ||
    'Commentary of 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890            1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
    1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 12')
 as thing  FROM CC_TEST2 step

Any ideas?

I think it's something odd about nVarchar2? If I change the column type to varChar2 then it's OK. Sadly I can't change the column type of the actual production database where I'm getting the issue

Answer 1

If "NAME" NVARCHAR2(255) is changed to "NAME" VARCHAR2(255) (i.e by using varchar2) you won't get any issue. You can test the same at http://sqlfiddle.com/#!4/cefd8/2

Answer 2

There seems to be some strangeness with NVARCHAR2 and string concatenation.

See http://sqlfiddle.com/#!4/936c4/2

My understanding based on running the various statements in the SQL Fiddle is that the string constant on the right hand side of the concatenation operator || is also treated as an NVARCHAR2, and can be at most 1000 characters.