Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
Considering that
TEST_SCRIPT
is a
CLOB
why when I run this simple query from SQL*PLUS on Oracle, I get the error:
ORA-00932: inconsistent datatypes: expected - got CLOB
I have been reading a lot of questions about the same error but none of those is running a direct query from SQLPLUS
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE TEST_SCRIPT = 'something'
AND ID = '10000239'
Full example:
SQL> create table ims_test(
2 test_category varchar2(30),
3 test_script clob,
4 id varchar2(30)
5 );
Table created.
SQL> insert into ims_test values ('test1','something','10000239');
1 row created.
SQL> UPDATE IMS_TEST
2 SET TEST_Category = 'just testing'
3 WHERE TEST_SCRIPT = 'something'
4 AND ID = '10000239';
WHERE TEST_SCRIPT = 'something'
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got CLOB
–
–
–
–
–
You can't put a CLOB in the WHERE clause. From the documentation:
Large objects (LOBs) are not supported in comparison conditions.
However, you can use PL/SQL programs for comparisons on CLOB data.
If your values are always less than 4k, you can use:
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE to_char(TEST_SCRIPT) = 'something'
AND ID = '10000239';
It is strange to search by a CLOB anyways.. could you not just search by the ID column?
–
–
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE to_char(substr(TEST_SCRIPT, 1, 9)) = 'something'
AND ID = '10000239';
I just ran over this one and I found by accident that CLOBs can be used in a like query:
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE TEST_SCRIPT LIKE '%something%'
AND ID = '10000239'
This worked also for CLOBs greater than 4K
The Performance won't be great but that was no problem in my case.
–
presumably because oracle can't handle a clob in a temporary table.
Because my values were longer than 4K, I couldn't use to_char()
.
My work around was to select it from the final select
, ie
with cte as (
select
mytable1.myIntCol
from mytable1
select myIntCol, myClobCol
from cte
join mytable2 on ...
where ...
Too bad if this causes a performance problem.
The problem may lie in selected null values in combination with a CLOB-type column.
select valueVarchar c1 ,
valueClob c2 ,
valueVarchar c3 ,
valueVvarchar c4
of Table_1
union
select valueVarchar c1 ,
valueClob c2 ,
valueVarchar c3 ,
null c4
of table_2
I reworked the cursor.
The first cursor is composed of four non-null columns.
The second cursor selects three non-null columns.
The null values were injected into the cursorForLoop .
Generally to solve this problem, just use TO_CHAR, example if you convert convert date to 'DD/MM/RRR hh24:mi:ss' format.
TO_CHAR(date_value ,''DD/MM/RRRR hh24:mi:ss'')
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.