将blob转换为clob [英] convert blob to clob

问题描述

我正在使用oracle 11g，并且试图找出文本的长度.我通常会从表中使用select length(myvar)，但是我不能这样做.

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.

我要查询的表有一个BLOB列，用于保存字符或照片.我想知道具有BLOB列的字符数.

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.

我尝试使用表中的UTL_RAW.CAST_TO_VARCHAR2(myblob)将BLOB转换为字符，但是此功能无法正常工作，或者我弄错了.

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.

例如: 我的BLOB的字词是Section，当我在数据库中以十六进制形式看到此字时，我看到了S.e.c.t.i.o.n..我不知道为什么每个字母之间都有这些点. 然后我使用了这个查询

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

此查询的结果为'S'，因此不是我的BLOB所用的完整单词，也不是我进行查询时的

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

结果为18，并且"Sections"一词不包含18个字符.

the result is 18 and the word Sections doesn't have 18 characters.

我试图将blob转换为varchar，尽管我认为最好的选择是clob，因为它可以保存的文本长度超过了varchar的限制.我尝试通过执行此查询来做到这一点(我不确定这是否正确，但我在互联网上找到的是

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

此查询还返回'S'

希望您能帮助我解决这个问题.感谢高级

I hope you can help me with this problem. thanks for advanced

推荐答案

对于任何来此线程并想知道如何将blob转换为clob的人来说.这是一个例子.

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;

这篇关于将blob转换为clob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！