How to convert CLOB to VARCHAR2 inside oracle pl/sql

Question

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+

I tried

  report_len  := length(report_clob);
  report      := TO_CHAR(dbms_lob.substr(report_clob, report_len, 1 ));
  report_clob := null;

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.

I even tried direct assignment

   report_clob := report 

getting the same result.

EDIT

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.

report and report_clob are out variable from the procedure. This procedure is called from C# code.

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.

And when I assign report:= 'some string....' then C# call works fine.

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.

Any more detail I will be happy to provide.

Answer 1

Quote (read [here][1])-

When you use CAST to convert a CLOB value into a character datatype or a BLOB value into the RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype.

So, something like this should work-

report := CAST(report_clob AS VARCHAR2(100));

Or better yet use it as CAST(report_clob AS VARCHAR2(100)) where ever you are trying to use the BLOB as VARCHAR [1]: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm