Error on Select - ORA-00932: inconsistent datatypes: expected - got LONG :-932

I have a select statement that uses a LONG datatype in the where clause and is producing the following error
Error on Select - ORA-00932: inconsistent datatypes: expected NUMBER - got LONG :-932

I tried converting the LONG to a clob using the TO_LOB (see below) and I'm getting the following.

Error on Select - ORA-00932: inconsistent datatypes: expected - got LONG :-932

Can anyone help?

Thanks.

Mark

SELECT
      CONTHIST.USERID,
      to_char(current_date, 'DD-MM-YY') As Today,
      count( CONTHIST.ACCOUNTNO )
FROM
        CONTHIST, dual
WHERE
        trunc(CONTHIST.CREATEON) = trunc(sysdate)
        AND  
      to_lob(CONTHIST.NOTES) not like '%Content-type%'
        AND
      CONTHIST.USERID  IN  ('SMITHS1')
GROUP BY
        CONTHIST.USERID;
Mark_1976Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
There are many postings that shows bug here.
A good step is to upgrade the Oracle version
1.g. 9.2.0.1 ---->9.2.0.7

Also I can not understand why you use the 'dual' table.
0
Harish_RajaniCommented:
Did you try To_Char() ?


Rgds,
HR
0
Mark_1976Author Commented:
Hi Harish

If I use to_char I get this...

ORA-00932: inconsistent datatypes: expected CHAR got LONG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Harish_RajaniCommented:
Have you defined the NOTES field as LONG ?
0
Mark_1976Author Commented:
Hi Harish

The NOTES field is a LONG datatype. I'm not the one who defined the table I'm afraid...I'm just the one who has to report from it! ;-)
0
Harish_RajaniCommented:
I have a solution for you. I tested it and it works for me.

First I created a function:

Create or replace function conthist_long_varchar(row_id rowid)
Return varchar2
as
str varchar2(2000);
begin

select notes  into str from conthist
where rowid = row_id;

return str;
end conthist_long_varchar;


Now you can change the query to look as following:

SELECT
     CONTHIST.USERID,
     to_char(current_date, 'DD-MM-YY') As Today,
     count( CONTHIST.ACCOUNTNO )
FROM
       CONTHIST, dual
WHERE
       trunc(CONTHIST.CREATEON) = trunc(sysdate)
       AND  
     conthist_long_varchar(rowid) not like '%Content-type%'
       AND
     CONTHIST.USERID  IN  ('SMITHS1')
GROUP BY
       CONTHIST.USERID;


Good Luck!!



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csachdevaCommented:
Dear Mark,

Cause: One of the following:

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.
An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.
 
Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
an object not intended for normal use, then do not access the restricted object.

The LOBs can be treated as VARCHAR2 implicitly by oracle so a substr(field,1,4000) would've brought you the first 4000 chars. But not with long. You cant trim it. Thats it.

If you'r in design level thinking of creating a table with long, please be aware of the limitations oracle has posed on oracle.

 Only one LONG column is allowed per table.
* LONG columns cannot be indexed.
* LONG columns cannot appear in integrity constraints.
* LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements.
* LONG columns cannot be referenced by SQL functions (such as SUBSTR or INSTR).
* LONG columns cannot be used in the SELECT list of a subquery or queries combined by the set operators UNION, INTERSECT, or MINUS.
* LONG columns cannot be used in SQL expressions.
* LONG columns cannot be referenced when creating a table with a query (CREATE TABLE... AS SELECT...) or when inserting into a table or view with a query (INSERT INTO... SELECT...).
* A variable or argument of a PL/SQL program unit cannot be declared using the LONG datatype.
* Variables in database triggers cannot be declared using the LONG or LONG RAW datatypes.
* References to :NEW and :OLD in database triggers cannot be used with LONG or LONG RAW columns.
* LONG and LONG RAW columns cannot be used in distributed SQL statements.
* LONG and LONG RAW columns cannot be replicated
0
Harish_RajaniCommented:
Hi csachdeva,

Its useful information, and well sums up the perils of using LONG Datatype, which is strongly discouraged by oracle. You have very  precisely and concisely presented in your post.

Mark has already stated, that he is not the one who created the table, and so ofcourse he is not at a design stage.

As you had noted, The fact of matter is that you can not use func(LONG) with in a SQL Query. Here func() can be anything, substr(), to_char(), to_lob().
So there is an alternative, you force the LONG into varchar2 using the select ...into.. from statement, and then do the comparison on varchar2.  That is exactly what I have tried to do by writing a small function.


Rgds,
HR

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.