I'm using oracle 11g and I'm trying to find out the length of a text. I will normally use select length(myvar) from table, but I can't do that.
The table which I want to query has a BLOB column that saves characters or photos. I want to know the number of characters that have my BLOB column.
I tried to convert my BLOB into a char using UTL_RAW.CAST_TO_VARCHAR2(myblob) from table, but this functions isnt't working correctly or maybe I'm making a mistake.
For example: My BLOB have the word Section, when I see this in the databse in the hexadecimal form I see S.e.c.t.i.o.n.. I dont'k know why it have those points in between each letter. Then I used the this query
select UTL_RAW.CAST_TO_VARCHAR2(myblob)
from table
The result of this query is 'S' so it's not the complete word that my BLOB has, and when I make this query
select length(UTL_RAW.CAST_TO_VARCHAR2(myblob))
from table
the result is 18 and the word Sections doesn't have 18 characters.
I was trying to convert the blob into a varchar, although I think my best choise would be a clob because the length of the text that it can save is more than the limit that varchar has. I tried to do that by making this query (I'm not sure if this is correct but is what I found in the internet)
select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(myblob, 32767, 1))
from table
This query also returns 'S'
I hope you can help me with this problem. thanks for advanced
For anyone coming to this thread and wants to know how to convert a blob to a clob. Here is an example.
create function clobfromblob(p_blob blob) return clob is
l_clob clob;
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createTemporary(lob_loc => l_clob
,cache => false);
dbms_lob.converttoclob(dest_lob => l_clob
,src_blob => p_blob
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offsset
,src_offset => l_src_offsset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
return l_clob;
end;