Forum Stats

  • 3,873,020 Users
  • 2,266,496 Discussions
  • 7,911,404 Comments

Discussions

ORA-43915 Character collation in Oracle 19c

Ariagna Bandala salazar
Ariagna Bandala salazar Member Posts: 34 Red Ribbon
edited Mar 31, 2022 11:55PM in General Database Discussions

I find myself migrating from version 12cR2 to version 19c of Oracle, the documentation was previously reviewed to find out if these versions were compatible and it was found that yes, it was possible to perform a direct migration, I already have a schema in version 19c and at doing tests I found a problem with a query that in version 12cR does not generate any error, said problem is the following:

ORA-43915: unable to determine collation: arguments have conflicting collations


In both versions the database instances that were created have the same configuration, which is:

Instance-level character collection: WE8MSWIN1252

Column level collation: BINARY

NLS_COMP at session level: LINGUISTIC

Session-level NLS_SORT: BINARY_CI


The statement is something like:

WITH PERSONA_FULL (

SELECT NOMBRE FROM PERSONA1

UNION

SELECT NOMBRE FROM PERSONA2

)

SELECT PERS_FULL.NOMBRE, CASE WHEN PERS.NOMBRE=' ' THEN ' ' ELSE PERS.NOMBRE END 

FROM PERSONA_FULL PERS 

LEFT OUTER JOIN PERSONA_FULL PERS_FULL ON PERS.NOMBRE=PERS_FULL.NOMBRE


Do you know how to avoid this problem or why it happens only in version 19c and in 12cR2 there is no problem?


The result of SELECT * FROM V$NLS_PARAMS is attached to the image


Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    select 'abc' collate binary from dual
    union all
    select 'abc' collate generic_m from dual;
    
    ORA-43915: unable to determine collation: arguments have conflicting collations
    

    Use same definition of columns in the table or explicitly cast to same collation in the query.

  • Ariagna Bandala salazar
    Ariagna Bandala salazar Member Posts: 34 Red Ribbon

    I understand that this would solve the problem, but the detail is that there are around 10,000 queries, which would cost too much, that in version 12cR2, since these queries are executed without any problem, that is why my question is more about obtaining some configuration option than not seeing in version 19c