Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-30
10
Medium Priority
?
20,584 Views
Last Modified: 2012-05-05
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;
0
Comment
Question by:Mark_1976
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 16332037
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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16332044
Did you try To_Char() ?


Rgds,
HR
0
 

Author Comment

by:Mark_1976
ID: 16332085
Hi Harish

If I use to_char I get this...

ORA-00932: inconsistent datatypes: expected CHAR got LONG
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16332607
Have you defined the NOTES field as LONG ?
0
 

Author Comment

by:Mark_1976
ID: 16332690
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
 
LVL 4

Accepted Solution

by:
Harish_Rajani earned 2000 total points
ID: 16332842
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
 
LVL 4

Expert Comment

by:csachdeva
ID: 16348951
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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16348982
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question