Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CLOB Datatype (Assgin more than 32k fails)

mohdmunawarJun 2 2009 — edited Jun 3 2009
Dear All
Can anyone tell me why i am getting this error if i assign more than 32k character to clob variable in pl/sql
but i can assign it from table to a variable

Pl/sql 1(ORA-06502: PL/SQL: numeric or value error: character string buffer too small)

declare
c clob;
v varchar(32767);
begin
for i in 1..90000 (just assuming it as 90k)
loop
v:=v||'x';
if length(v) > 31000 then
c:=c||v; -- here iam getting error while assigning character to clob if it is more than 32k
v:=null;
end if;
end loop;
c:=v;
end;

Pl/sql 2 (works fine when assgin from table)
declare
c clob;
begin
select clob_data into c from x; -- clob data is more than 32k;
end;

But it works fine with database 9i rel2 but in 11g i am facing this problem.

Regards
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 1 2009
Added on Jun 2 2009
6 comments
2,958 views