This forum is currently locked. You can't register or post questions at this time. (read more)

Issue with Clob

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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);
/

commit;
/

select * from emp100;
/

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

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

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


begin

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);

end;

Regards,
Sampath.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Issue with Clob

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

Hi.

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

http://www.oracle-base.com/articles/mis ... -lists.php

Cheers

Tim...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron