ORA-00932: inconsistent datatypes: expected NUMBER got LONG

[hm]

I am running alfresco on Oracle 10G XE (Express/Free Edition). I am able to log into the application and create spaces etc.

In the Oracle web client when I look at the data in the 'node_properties' table I see the following message instead of the data:

report error:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY

I beleive this is becuase we are passing Java Null?

When I query this table using slq*plus I see 1943 rows.

Here is the application version:

System Information
Current User: admin
Version: Open Source - v1.1.2

[derek]

Hi,

I am not sure what you mean by "Java Null".  Null values are allowed in the columns and we use them liberally.

The fact that one client fails while the other succeeds suggests to me that the failing client is executing an illegal query.

Long datatype has some restrictions , one of them is ;
LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

from http://datawarehouse.ittoolbox.com/groups/technical-functional/oracle-db-l/760953.

At any rate, as long as you are able to perform queries using SQLPLUS, it should be fine.  The schema has already had to be modified specifically to handle Oracle restrictions that get applied to column types.  As we don't ever select specific values from the LONG BINARY column, it is probably just the Oracle web client doing a DISTINCT or something.  Try SQLPLUS with

SQL> select distinct ( serializable_value) from node_properties;
select distinct ( serializable_value) from node_properties
                  *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
‍‍‍‍‍‍‍

Regards