title: How to convert BLOB to CLOB?
body: <p>I'm using Oracle 11g and I'm trying to find out the length of a text. I normally use <code>select length(myvar) from table</code>, but I can't do that.</p>
  <p>The table which I want to query has a <code>BLOB</code> column that saves characters or photos. I want to know the number of characters that my <code>BLOB</code> column has.</p>
  <p>I tried to convert my <code>BLOB</code> into a char using <code>UTL_RAW.CAST_TO_VARCHAR2(myblob) from table</code>, but this functions isn't working correctly or maybe I'm making a mistake.</p>
  <p>For example:
  My <code>BLOB</code> have the word <code>Section</code>, when I see this in the databse in the hexadecimal form I see <code>S.e.c.t.i.o.n.</code>. I don't know why it have those points in between each letter.
  Then I used the this query:</p>
  <pre><code>select UTL_RAW.CAST_TO_VARCHAR2(myblob) 
  from table
  </code></pre>
  <p>The result of this query is <code>'S'</code> so it's not the complete word that my <code>BLOB</code> has, and when I make this query:</p>
  <pre><code>select length(UTL_RAW.CAST_TO_VARCHAR2(myblob))
  from table
  </code></pre>
  <p>the result is <code>18</code>, but the word <code>Sections</code> doesn't have 18 characters.</p>
  <p>I was trying to convert the <code>BLOB</code> into a <code>VARCHAR</code>, although I think my best choise would be a <code>CLOB</code> because the length of the text that it can save is more than the limit that <code>VARCHAR</code> 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):</p>
  <pre><code>select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(myblob, 32767, 1))
  from table
  </code></pre>
  <p>This query also returns <code>'S'</code></p>