How to convert BLOB to CLOB?

Question

I'm using Oracle 11g and I'm trying to find out the length of a text. I 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 my BLOB column has.

I tried to convert my BLOB into a char using UTL_RAW.CAST_TO_VARCHAR2(myblob) from table, but this functions isn'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 don't 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, but 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'

Solution 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;

Solution 2

To convert blob to clob, try this:

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

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

Solution 4

Only converting to CLOB:

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

Inspired by Craig without limitation on size.

View more solutions

Comments

[Chris R. Donnelly]

Two things: 1. you need to create a temporary CLOB 2. you should probably short-circuit out a null if p_blob is null. begin if p_blob is null then return null; end if; DBMS_LOB.CREATETEMPORARY(l_clob, false); dbms_lob.converttoclob(... (edited for formatting)

[Brian McGinity]

Sorry for the down vote, it happened by accident and will not let me up vote until edited.

[Raja]

Found below error after executing above pattern; ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 3904, maximum: 2000) 22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)" *Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where the LOB size was bigger than the buffer limit for CHAR and RAW types.

[Jared Still]

Credit where credit is due for that example code: oracle-base.com/dba/miscellaneous/blob_to_clob.sql

[Jared Still]

Casting a blob to varchar2 is not a good idea without first checking the size. In PL/SQL the limit for a varchar2 is 32767 for single byte characters. In the database the limit is 4000 for single byte characters.