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

Only converting to CLOB:

select TO_CLOB(UTL_RAW.CAST_TO_VARCHAR2(YOURCLOB)) from DUAL;

Inspired by Craig without limitation on size.

Answer 2

To convert blob to clob, try this:

SELECT TO_CLOB(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(MYBLOB,2000))) 
FROM MYTABLE;

Answer 3

SELECT DBMS_LOB.GetLength( myblob ) length_in_bytes
  FROM table

will return the length of the BLOB in bytes. It sounds like the character data in your BLOB is probably encoded using the UTF-16 character set so the number of bytes is probably twice the number of characters (depending on the version of Unicode that is being used and the specific data being stored, some characters might require 4 bytes of storage but it is relatively unlikely that you're dealing with any of those characters).

You can use the DBMS_LOB.ConvertToClob procedure to convert a BLOB to a CLOB (though since this is a procedure, you'll need to call it in a PL/SQL block). As part of that conversion, you'll almost certainly need to specify the character set that the data is encoded in-- my assumption is that your application is using the UTF-16 character set but that's just an assumption.

Answer 4

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;