Error ORA-12704 for some columns

Description

Hi,

I've added a new document table (with DocStatus, DocAction, Posted, etc.)
Then i wanted to add this new table in the RV_Unposted view.

And it was impossible as the DocStatus (and DocAction) columns don't have same type.

To illustrate :
C_Invoice : DOCSTATUS NVARCHAR2(2 CHAR)
myTable : DOCSTATUS CHAR(2 BYTE)

So, when trying to add it in the view, it throws a ORA-12704:
character set mismatch.

Note the problem could happen if you synchronize a column, its type will be CHAR(2) =>NVARCHAR2(2 CHAR) and the view will then failed to create.

I think that 'old' columns (C_Invoice, C_Payment, ...) should be updated ?

WDYT ?

Regards,

Nicolas

Don't know if it is the same on postgre

Environment

None

Activity

Nicolas Micoud 
December 6, 2013 at 10:23 AM

Seems ok for me.

RV_Unposted view is ok after those changes :

  • Synchronize colum C_Invoice.DocStatus (CHAR(2) => VARCHAR(2))

  • Add new column DocStatus on an existing table (created as VARCHAR(2)) ; table added in RV_Unposted

Anyone else using oracle around ?

Regards,

Nicolas

Carlos Ruiz 
December 4, 2013 at 6:03 PM

Discussed on IRC meeting
http://www.globalqss.com/wiki/index.php/IDempiere/FullMeeting20131204

so - a possible fix is to change line 611
return "NVARCHAR2(" + fieldLength + ")";
must be
return "VARCHAR2(" + fieldLength + ")";

pending confirmation

Nicolas Micoud 
December 2, 2013 at 8:30 AM

#1 also :

Here an extract of the sql generated by the creation of a table with DocAction and DocStatus columns :
[...] , DocAction CHAR(2) DEFAULT 'CO' NOT NULL, DocStatus NVARCHAR2(2) DEFAULT 'DR' NOT NULL, [...]

Seems it is related to line 620 in DisplayType.
If the column is a button and if it doesn't end with _ID, then its type is CHAR.
We should add something like if (columnName.equals("DocAction") return NVARCHAR(fieldlength);

WDYT ?

Regards,

Nicolas

Nicolas Micoud 
November 30, 2013 at 8:29 PM

Hi,

#2 some CHAR columns that should be converted to NVARCHAR.
(New colums are cretaed correctly)

Regards,

Nicolas

Carlos Ruiz 
November 30, 2013 at 5:25 PM

Nicolas, I didn't understand completely the reported issue:

is there a case in current code creating CHAR(2) columns? -> if yes, I would think is a bug as CHAR(>1) has been something leading to annoying hard-to-find bugs

or, your report is that in current database there are some CHAR columns that must be converted to NVARCHAR?

Regards,

Carlos Ruiz

Fixed

Details

Assignee

Reporter

Labels

Priority

Created November 30, 2013 at 1:24 PM
Updated March 11, 2020 at 5:16 PM
Resolved December 11, 2013 at 1:47 PM