C# – ORA-00932: inconsistent datatypes: expected DATE got NUMBER

cnetodacoracle

I am using Oracle Data Access from .net and my query is as

command.CommandText = "select * from table1 where expirydate =:EXPIRYDATE";
command.Parameters.Add("EXPIRYDATE", OracleDbType.Date, DateTime.Today,ParameterDirection.Input);

var results = command.ExecuteScalar();

I get the following error " ORA-00932: inconsistent datatypes: expected DATE got NUMBER"

If I change my query to:

command.CommandText ="select * from table1 where expirydate =
to_date(:EXPIRYDATE,'DD/MM/YYYY')";

I get no results.

Thanks in advance.

Best Answer

Most likely reason for this error is that the order of the parameters in your query does not match the order you add them to the Parameters collection. Oracle Data Access pretends to bind them by name, but actually binds them by order.

Related Solutions

Sql – ORA-00932 inconsistent datatypes expected char got number

You can't sum a character value and all the returned values in a CASE statement must be the same datatype.

If you transform your SUM to a character using TO_CHAR() this still won't work as you're not grouping correctly, see this SQL Fiddle.

The easiest way to do this would be to return a 0 rather than 'INVALID', sum over the entire case statement and change this back to 'INVALID' if it's 0. It's not quite the same... don't store numbers in character columns?

select case when a = 0 then 'INVALID'
            else to_char(a)
       end
  from ( select sum( case when upper(txt) <> lower(txt) then 0
                          else to_number(txt)
                     end ) as a
           from class )

Here's a SQL Fiddle to demonstrate.

Sql – ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Because TO_CLOB(LONG) converter/constructor needs physical pointer for storage (potentially 4GB...),

This should work for you (tested on 11gR2):

CREATE TABLE DBO.MY_ALL_VIEWS AS 
SELECT DV.owner, DV.view_name, TO_LOB(DV.text) AS text
FROM ALL_VIEWS DV;

SELECT count(*) 
FROM DBO.MY_ALL_VIEWS 
WHERE REGEXP_LIKE(TEXT,'(company)+','i');

Best Answer

Related Solutions

Sql – ORA-00932 inconsistent datatypes expected char got number

Sql – ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Related Topic