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.?