Issue with Clob

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Issue with Clob

Postby Sampath » Tue Apr 02, 2013 12:14 pm

Good Morning Tim,

Can you please help me on below issue.

I have to pass a comma seperated string to an IN clause in the WHERE clause of an UPDATE statement.The data type of the string is CLOB(contains long strings).
It thows the below exception.

ORA-00932: inconsistent datatypes: expected - got CLOB
ORA-06512: at line 10

Please find the below script.

create table emp100
(eno number,
ename varchar2(50),
esal number,
active number(1));
insert into emp100 values (1,'ABCD',5000,1);
insert into emp100 values (1,'PQRS',6000,1);
insert into emp100 values (1,'WXYZ',7000,1);


select * from emp100;

--Setting the active column to 0 using comma seperated string of salaries('5000,6000,7000')

--l_var varchar2(4000) := '5000,6000,7000';

l_var clob := '5000,6000,7000';


update emp100
set active = 0
where esal in (select regexp_substr(l_var,'[^,]+', 1, level) from dual
connect by regexp_substr(l_var, '[^,]+', 1, level) is not null);


Posts: 39
Joined: Wed Aug 12, 2009 3:40 pm

Re: Issue with Clob

Postby Tim... » Tue Apr 02, 2013 12:44 pm


If you need dynamic IN lists you need to consider something like this: ... -lists.php


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:
Site Admin
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest