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

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

Assignee

Carlos Ruiz

Reporter

Carlos Ruiz

Labels

Tested By

None

Fix versions

Affects versions

Priority

Blocker
Configure