Cannot save valid strings in oracle - ORA-12899: value too large for column

Description

Test case:

in server https://test-oracle.idempiere.org/webui/

Log in as GardenAdmin
Open "Document Sequence" window
Create a new record
Fill name with the following string:
- Sequência this is sequence in portuguese cannot save 60 char
  - This string is exactly 60 characters, and it has one accented portuguese character ê
Save
The program throws error -> Database Error.: DBExecuteError:ORA-12899: value too large for column "IDEMPIERE"."AD_SEQUENCE"."NAME" (actual: 61, maximum: 60)

Environment

None

Activity

Show:

Carlos Ruiz

August 6, 2020, 10:08 AM

Copy link to comment

Hi ,

As a followup or our mattermost conversation I kept researching about this and as you said in the end
"perhaps, your original proposal to use varchar2(10 char) is still the safest ..."

It turns out that it is, this is what I found:

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/NLS_LENGTH_SEMANTICS.html#GUID-221B0A5E-A17A-4CBC-8309-3A79508466F9

Note:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.

https://software.databasedevelop.com/article/13590883/NLS_LENGTH_SEMANTICS%c2%a0+%3d+CHAR.%c2%a0+after+Oracle+Instance+is+done+and+to+create+new+database.

Therefore, if you can, change the application to not depend on the NLS_LENGTH_SEMANTICS parameter altogether by using the explicit semantics keyword in column and variable definitions. This is the recommended approach. It makes the value of the parameter irrelevant and leads to self-documenting DDL where you do not have to guess what the expected semantics is when reading the DDL statement.

Consequently, I reopened the PR 202, it can be integrated, the work about the other related tickets will be pushed in different pull requests.

Regards,

Carlos Ruiz