title: How to convert CLOB to VARCHAR2 inside oracle pl/sql
body: <p>I have a clob variable, need to assign it to varchar2 variable. The data inside clob var is less than 4000 (i..e varchar2's maxsize) oracle10+</p>
  
  <p>I tried </p>
  
  <pre><code>  report_len  := length(report_clob);
    report      := TO_CHAR(dbms_lob.substr(report_clob, report_len, 1 ));
    report_clob := null;
  </code></pre>
  
  <p>but it turns report into long value which I see while debugging. Also when I call this sql (proc) from my C# code. It complains saying buffer too small, because I send parameter as per varchar, but the above conversion might be turning it into long value.</p>
  
  <p>I even tried direct assignment</p>
  
  <pre><code>   report_clob := report 
  </code></pre>
  
  <p>getting the same result.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Ok, to answer the questions below please see:
  I debug using test script in PL/SQL developer. report variable is varchar2(4000). When I step after 2nd line. report shows to be a long value and it just says (Long Value) . cant even see the contents.</p>
  
  <p>report and report_clob are out variable from the procedure. This procedure is called from C# code.</p>
  
  <p>There is an exception string buffer too small in C# when I call this procedure. I have given 5000 as size of report variable in C# sufficient to receive 4000 max characters value from the procedure. So I guess problem doesn't lie there.</p>
  
  <p>And when I assign report:= 'some string....' then C# call works fine.</p>
  
  <p>So my investigation says that report := transform (report_clob) is making report become long value or some such thing (weird) which makes C# code problematic to handle larger value in 5000 varchar out parameter.</p>
  
  <p>Any more detail I will be happy to provide.</p>