oracle - convert blob to clob

Question

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

Answer 1

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;