1

My source is in SQL Server and the target is Oracle. There are some tables having columns defined NTEXT in SQL Server and I created columns of NVARCHAR2(2000) which allows 4000 bytes, to store the data from the source.

When I pull the data defined NTEXT from SQL Server, I cast and substring the data to fit into 4000 bytes in the target. I'm using Data Stage by IBM to extract the source form SQL Server and the code below performs converting data type to varchar(4000) and extracting a substring with the specified length, 4000 bytes.

cast(substring([text],1,3950) as varchar(4000)) as "TEXT"

However, it often occurs an error ORA-12899 when it inserts into NVARCHAR2(2000) on Oracle which is sized 4000 bytes.

Error message: ORA-12899: value too large for column (actual: 3095, maximum: 2000).

First, it is hard to understand why it occurs the error even though the destination has a column sized 4000 bytes and I cut the data using SUBSTING already.

Second, I was wondering if I miss anything to handle the issue when my team does not consider CLOB on Oracle for those NTEXT type data.

Please help me to resolve this issue. I'm working on many tables and the error occurs often.

llearner
  • 37
  • 5
  • 1
    `substring(text, 1, 3950)` doesn't seem like you limit the string to 2000 symbols – astentx Feb 04 '23 at 07:14
  • FYI, the deprecated `ntext` data type allows for up to 2GB of characters; that's around 1 Billions characters. – Thom A Feb 04 '23 at 09:49
  • @Larnu That's right. `ntext` assigns 2 bytes per 1 character and the largest data uses over 5000 bytes on SQL Server while target column, `nvarchar2` , on Oracle only has 4000 bytes which means 2000 characters and it is lack of the space for the largest one now. – llearner Feb 06 '23 at 14:17
  • @astentx Yes, I also think the `substring(text, 1, 3950)` is not to limit the string up to 2000 characters. It shrinks the string up to 3950 characters and it could be up to 7900 bytes depending on the cases. I realized that `substring` sets the end of the position of the input string meanwhile `varchar` and `nvarchar2` set the maximum length of the bytes after posting my question. Thanks for replying to my question. – llearner Feb 06 '23 at 14:25
  • Also note that in 12c and above `(N)VARCHAR2` datatype was extended to be PL/SQL compatible and it allows up to 32k bytes with `MAX_STRING_SIZE=EXTENDED` [initialization parameter](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html). *It is not downgradable*. – astentx Feb 06 '23 at 19:04
  • @astentx Thank you for the information. I'll check out the page soon. – llearner Feb 07 '23 at 17:55

1 Answers1

3

nvarchar2 is limited to 2000 chars, which requires 4000 bytes. You have to specify the limit in chars (so, 2000, not 4000). Try changing your function to:

cast(substr([text],1,2000) as nvarchar2(2000)) as "TEXT"
Paul W
  • 5,507
  • 2
  • 2
  • 13
  • thank you so much for sharing the point with me to resolve the problem. I'll try it out and will see what is going to be for the largest data. Thanks again. – llearner Feb 06 '23 at 14:19