ORA-00932: inconsistent datatypes: expected - got NCLOB because of UNION

[abxeps]

Hi,

Using LINQ Union with CLOB fields produces ORA-00932: inconsistent datatypes: expected - got NCLOB.
It looks like a serious limitation, how can this be solved ?

EFCore 3.1.4
Devart 9.11.980

Best regards,

[Shalex]

Please refer to viewtopic.php?t=39708. If this doesn't help, send us a small test project with the corresponding DDL/DML script for reproducing the error.

[abxeps]

I sent you an email with the test project in order to reproduce the problem.

[Shalex]

This issue is caused by Oracle specifics of processing LOB columns. For example, even a simple query like

Code: Select all

SELECT a.EXTRAPROPERTIES
FROM ABPROLES a
UNION
SELECT b.EXTRAPROPERTIES
FROM ABPROLES b

also returns the error ORA-00932: inconsistent datatypes: expected - got NCLOB.

Oracle documentation stresses that "You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. " (you can find more information here: https://docs.oracle.com/database/121/AD ... ADLOB45148)

Since the workaround that we offered in the previous email does not suite you, we can offer two more workarounds.

1) Truncate the NCLOB value in SELECT statements and convert it to NVARCHAR2. For example:

Code: Select all

SELECT DBMS_LOB.SUBSTR(a.EXTRAPROPERTIES)
FROM ABPROLES a
UNION
SELECT DBMS_LOB.SUBSTR(b.EXTRAPROPERTIES)
FROM ABPROLES b

However this solution may be not compatible with your application logics.

2) If possible, change the data type of the EXTRAPROPERTIES columns, for example, to NVARCHAR2 or VARCHAR2.
If length limits of these data types suite you, it's better to switch to these data types.

However, you need to perform the following actions in order to do it:
a) Change data type of the column in the Oracle table ( here you can find a description of migration from CLOB to VARCHAR2, and you can migrate from NCLOB to NVARCHAR2 in a similar way: https://stackoverflow.com/questions/198 ... archar2sql).
b) Change mapping in the application by adding .HasColumnType("nvarchar2") and/or .HasMaxLength(2000) to the corresponding columns.
с) If the application was already deployed, and it uses Code-First Migrations, add a migration to it with SQL operations performing the logics of data type change.

JIC:
* DBMS_LOB.SUBSTR may truncate LOB value silently, so this is not an appropriate solution for default behavior.
* We will consider the possibility to add an option for using DBMS_LOB.SUBSTR. There is no timeframe for the feature at the moment.