Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: blobs Post Reply Post New Topic
Author Message
assured
Newbie
Newbie


Joined: 17 Apr 2011
Location: United Kingdom
Online Status: Offline
Posts: 1
Quote assured Replybullet Topic: blobs
    Posted: 17 Apr 2011 at 11:41pm
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.?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.031 seconds.