ORACLE - Update datatype NVARCHAR2 to VARCHAR2

Hi @Hiep Lq,

https://github.com/idempiere/idempiere/pull/375

on my database LOTCHARSOVERWRITE, LOTCHAREOVERWRITE is changed
on code, only first commit of https://idempiere.atlassian.net/browse/IDEMPIERE-3862#icft=IDEMPIERE-3862 is apply, it hasn't migrate sql not sure what commit did that change

Thanks @Hiep Lq

I don't see that error in my DB - but seems there can be something wrong.

Reopening this ticket to investigate - there is probably something missing here - in my test database I see the columns SERNOCHARSOVERWRITE
LOTCHARSOVERWRITE
LOTCHAREOVERWRITE
SERNOCHAREOVERWRITE
are still NCHAR in the table M_ATTRIBUTESET

i has this issue guess reason by this ticket

because i apply some commit (not full) of this ticket i also hasn't test database to check update latest
@Carlos Ruiz can you confirm it's fixed on latest (in case it's issue by this ticket)?

Hi @Nicolas Micoud, @Heng Sin Low, can you please take a look at the pull request 207

I did tests and everything was running fine in the oracle with all the columns as VARCHAR2.

The ticket https://idempiere.atlassian.net/browse/IDEMPIERE-4178#icft=IDEMPIERE-4178 seems unnecessary now, but harmless to have it.

Regards,

Carlos Ruiz

My findings:

the actual default idempiere seed in 7.1z has:

• 3395 NVARCHAR2

• 392 VARCHAR2...BYTE

• 0 VARCHAR2...CHAR

Wherever I look it says that in the end NVARCHAR2 is not heavily recommended, the use case for NVARCHAR2 is "if you want to have unicode for some columns and the rest of the database use a different characterset" - and that's not our case

This page for example:
https://stackoverflow.com/questions/4401043/Oracle-text-will-not-work-with-nvarchar2-what-else-might-be-unavailable
describe issues with NVARCHAR2 (not confirmed), like:

• Oracle Text will not work with NVARCHAR2

• There are plenty of third party utilities and libraries that simply don't support NCHAR/ NVARCHAR2 columns

• Many built-in packages will only accept (or return) a VARCHAR2

• VARCHAR2...CHAR can use up to 4 bytes per char, NVARCHAR2 can use up to 2 bytes per char, this could be problematic for asian languages

• there are cases where Oracle's implicit type conversion rules prevent indexes from being used sometimes

So, moving towards NVARCHAR2 is less work in theory, but I think a better movement would be to move definitely to VARCHAR2 the whole database and set the NLS_LENGTH_SEMANTICS to CHAR

Because of comments from @Heng Sin Low onn github, I think we need to analyze the impact of the opposite case.

What about moving all VARCHAR2 to NVARCHAR2 ... WIP ... will add comments

Today I found a new issue with the varchar2 vs nvarchar2 columns.

• NVARCHAR2 supports a maximum length of 2.000

• VARCHAR2 supports "in theory" a maximum length of 4.000

• I say "in theory" because in reality the length of NVARCHAR2 is defined in international chars - while the length of VARCHAR2 is defined in BYTES

• that means - I can insert the value 'aaa' into a column VARCHAR2(3), but I cannot insert the value 'ááá'

• to be able to insert the value 'ááá' I need to define the column VARCHAR2(3 CHAR) - or NVARCHAR2(3)

• so, in our system we have an issue as we simply define the column VARCHAR2(length) but some values cannot be saved from the UI when they contain accented characters

• a possible solution would be to define VARCHAR2(3 CHAR) - but that means the maximum is not really 4.000, it becomes a number between 2.000 a 4.000 depending on the number of accented characters you use (which is very bad as in the end the maximum length of the accepted string is unknown until you try to save it)

Committed fix for the ColumnEncryption
https://github.com/idempiere/idempiere/commit/a1a0aad

and also some fixes to FinReport in
https://github.com/idempiere/idempiere/commit/d239941

I think this ticket is not critical now - the workaround is easy - use NVL instead of COALESCE in oracle for the columns with different character set.

One warning reviewing the code.

In case this ticket is implemented there are two occurrences of COALESCE..TO_NCHAR that must be taken care:
MProjectType.getSqlBarChart:240
MRequestType.getSqlBarChart:429

Will add this warning in the description of ticket also to keep it on sight

xref
https://mattermost.idempiere.org/idempiere/pl/tdhqr7bfhtrj3xsgpehpa17okr