How can I convert a large BLOB to a varchar in command object? I can only display 4000 characters from BLOB in my crystal reports. I tried to fetch the BLOB field to put into a VARCHAR2 field, but, if I select more than 4000, It is throwing an error - ORA:06502 PL/SQL: numeric or value error: raw variable length too long.
Here is the query I executed for the blob datatype conversion in crystal reports and works fine but I am losing half of the document. If I put 4001, I get the error message above.
SELECT package_id,
(dbms_lob.substr(STORED_DOCS(IMPORT_PACKAGE_STORE. STORED_DOCUMENT),4000)) as stored
FROM
IMPORT_PACKAGE_STORE
where package_id=1
Here's the function STORED_DOCS...
create or replace function STORED_DOCS(B BLOB)
return clob is
c clob;
n number;
begin
if (b is null) then
return null;
end if;
if (length(b)=0) then
return empty_clob();
end if;
dbms_lob.createtemporary(c,true);
n:=1;
while (n+32767<=length(b)) loop
dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
n:=n+32767;
end loop;
dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
return c;
end;
/
My question is: Is there any way to fetch more than 4000 char BLOB field into a VARCHAR2 field.?
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 18 Apr 2011 at 6:06am
No, there's not. You might have to break this up into multiple blobs of no more than 4000 character each. This is probably a database limitation more than a Crystal limitation.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum