Entity-framework generated query throws ORA-12704 when using TO_CHAR or TO_NUMBER

user_15075007 0
Viewed 91

I currently face the problem, that I get an exception when executing a query generated by the entity framework.

The query worked until I've joined another table using .include() to the existing entity. Now, whenever I execute the query, I get an Oracle error ORA-12704 character set mismatch.

I narrowed the problem down to the following:

Before joining the table, the generated SQL is a simple query with some join statements. After joining another table, the generated SQL cointains two subqueries which are combined using UNION ALL. In one of the subqueries, a lot of helper-columns are selected.

They look like this:

SELECT 
    ... some other columns...
    TO_NUMBER(NULL) AS C1,
    TO_CHAR(NULL) AS C2,
    ...

If I remove those columns and also the corresponding ones in the other subquery, no error is thrown. When I replace the columns with NULL instead of TO_XXX(NULL), the query also works as expected.

Is there any way force the entity-framework not to use these problematic casts?

entity-framework oracle
1 Answers

The problem is caused with the usage of a NVARCHAR2 column in combination with the function TO_CHAR (that returns VARCHAR2 data type) as illustrated below

create table tab
(txt nvarchar2(10));

select txt from tab
union all
select to_char(null) from dual;
ORA-12704: character set mismatch

So your goal is to motivate the tool to generate a query that uses either TO_NCHAR(null) or cast(null as nvarchar2(10)) - both will work.

To do this, you need to add the following data-annotation to the property of the corresponding entity:

[Column(TypeName = "NVARCHAR2")]

The given TypeName must match with the type of the column in the database. After this addition, the entityframework will generate the correct casts. In this case, the following cast ist generated:

SELECT
   ...
   TO_NCHAR(NULL),
   ...

You should see no problem with the to_number(null) if the corresponding UNIONcolumn is of a number datatype.

user_48081220
Related