title: SELECT as much data from CLOB to VARCHAR2 as possible, with multibyte chars in the data
body: <p>Multi-byte characters had caused me a lot of pain.</p>
  
  <p>Any suggestion for this problem?</p>
  
  <p>I have a CLOB field that <em>might</em> contains some multi-byte characters, and I need to select in SQL and convert this field into a string for downstream process, currently I am using:</p>
  
  <pre><code>SELECT DBMS_LOB.SUBSTR( description, 4000, 1 ) FROM table
  </code></pre>
  
  <p>But the 4000 in above command is in length of characters, rather than bytes. So I had to change to 3000 to handle any multi-byte characters that might have crept into the data else buffer size error will occur.</p>
  
  <p>The problem is for records that do not contain multibyte character, it might unnecessarily truncated more data than it need to.
  (The 4000 is the string limitation, we can/had to live with that.)</p>
  
  <p>Is there a way to do something in equivalent of:</p>
  
  <pre><code>SELECT DBMS_LOB.SUBSTR( description, 4000bytes, 1 ) FROM table
  </code></pre>
  
  <p>That way I can get as much data out as possible. </p>
  
  <p>Note: I am not allowed to create temp tables/views, not using PL/SQL, only SQL SELECT...</p>