Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

convert blob to clob

Tags:

oracle

blob

clob

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'

like image 262
user1739469 Avatar asked Oct 11 '12 21:10

user1739469


People also ask

What is DBMS_LOB Createtemporary?

In earlier times you were always forced to use DBMS_LOB package. Then in Oracle 10g a feature called "SQL semantics for LOB" was introduced and now you can use the simplified syntax. In CREATETEMPORARY you can specify the duration of the LOB in SQL semantic you cannot.

What is DBMS_LOB?

The DBMS_LOB module provides the capability to operate on large objects. In the following sections describing the individual procedures and functions, lengths and offsets are measured in bytes if the large objects are BLOBs. Lengths and offsets are measured in characters if the large objects are CLOBs.


2 Answers

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;
like image 134
Rene Avatar answered Sep 16 '22 21:09

Rene


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.

like image 37
Justin Cave Avatar answered Sep 18 '22 21:09

Justin Cave