Convert blob to clob

Question

I am using oracle 11g and I am trying to figure out the length of the text. I usually use the select (myvar) length from the table, but I cannot do this.

The table I want to query has a BLOB column that stores characters or photos. I want to know the number of characters that my BLOB column has.

I tried converting my BLOB to char using UTL_RAW.CAST_TO_VARCHAR2 (myblob) from the table, but these functions do not work correctly or maybe I'm wrong.

For example: My BLOB has the word Section, when I see this in the database in hexadecimal, I see Section. I don't know why it has these dots between each letter. Then I used this query

select UTL_RAW.CAST_TO_VARCHAR2(myblob) from table 

The result of this query is "S", so this is not the full 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 does not have 18 characters.

I tried to convert blob to varchar, although I think that my best bet would be clob, because the length of the text that it can save is longer than the limit that varchar has. I tried to do this by running this query (I'm not sure if this is correct, but this is what I found on the Internet)

 select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(myblob, 32767, 1)) from table 

This query also returns 'S'

Hope you can help me with this problem. thanks for advanced

Answer 1

For those who approach this topic and want to know how to convert blob to 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; 

Answer 2

 SELECT DBMS_LOB.GetLength( myblob ) length_in_bytes FROM table 

will return the length of the BLOB in bytes. It looks 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 used and the specific data that is stored, some characters may require 4 bytes memory, but it is relatively unlikely that you are dealing with any of these characters).

You can use the DBMS_LOB.ConvertToClob procedure to convert BLOBs to CLOBs (although since this is a procedure, you need to call it in the PL / SQL block). As part of this conversion, you will almost certainly need to specify a character set that is encoded with data. I suppose your application uses the UTF-16 character set, but this is just an assumption.

Answer 3

To convert blob to clob, try the following:

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