Oracle limit of 2000 in NVARCHAR leads to different behaviour compared to other databases

Camunda 7 Topics Discussion & Questions
1

We run camunda with Oracle 11g and had yesterday an error on inserting into ACT_HI_TASKINST DESCRIPTION_ field, which is of NVARCHAR2(2000).

The main problem we have now is, that we run tests with jbehave and h2, which allows VARCHAR(4000), so the tests were green, but production crashed.

Is there a way to configure the engine, that the size of the description is checked, for example in configuring h2 as VARCHAR(2000)?
This problem is not just in the description of ACT_HI_TASKINST, but in every field, that uses NVARCHAR2(2000) instead of VARCHAR(4000).

Any idea would be appreciated.

Regards
Patrick

2

Hi Patrick,

you’re right there are differences in the CREATE scripts. I think NVARCHAR2(2000) is set to 2000 because the max byte value is 4000 in oracle,
see the [docs] (https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i14946) and the discussion

It is not possible to configure the engine to check the size of the strings before they are inserted.
I would suggest that you use Oracle 11g in your test suite as well, since it is used in your product system.
But you’re also able to adjust the create scripts and change the value from VARCHAR(4000) to VARCHAR(2000).

Best regards,
Chris