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'

Baxter

DBMS_LOB.GET_LENGTH() may help – Lavena

Welcome to the wonderful world of variable length character encodings – Childhood

@Lavena I tried using select DBMS_LOB.GET_LENGTH(myblob) from table but I get an error. ORA-00904 invalid identifier. – Baxter

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;

Antisocial

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) – Meganmeganthropus

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

Answer 2

To convert blob to clob, try this:

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

Undergird

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.

Rotation

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

Answer 4

Only converting to CLOB:

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

Inspired by Craig without limitation on size.

Fiftieth

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. – Ko

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. – Equites