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)-

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.

Answer 2

Converting VARCHAR2 to CLOB

In PL/SQL a CLOB can be converted to a VARCHAR2 with a simple assignment, SUBSTR, and other methods. A simple assignment will only work if the CLOB is less then or equal to the size of the VARCHAR2. The limit is 32767 in PL/SQL and 4000 in SQL (although 12c allows 32767 in SQL).

For example, this code converts a small CLOB through a simple assignment and then coverts the beginning of a larger CLOB.

declare
    v_small_clob clob := lpad('0', 1000, '0');
    v_large_clob clob := lpad('0', 32767, '0') || lpad('0', 32767, '0');
    v_varchar2   varchar2(32767);
begin
    v_varchar2 := v_small_clob;
    v_varchar2 := substr(v_large_clob, 1, 32767);
end;

LONG?

The above code does not convert the value to a LONG. It merely looks that way because of limitations with PL/SQL debuggers and strings over 999 characters long.

For example, in PL/SQL Developer, open a Test window and add and debug the above code. Right-click on v_varchar2 and select "Add variable to Watches". Step through the code and the value will be set to "(Long Value)". There is a ... next to the text but it does not display the contents.

C#?

I suspect the real problem here is with C# but I don't know how enough about C# to debug the problem.

Answer 3

ALTER TABLE TABLE_NAME ADD (COLUMN_NAME_NEW varchar2(4000 char));
update TABLE_NAME set COLUMN_NAME_NEW = COLUMN_NAME;

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;
ALTER TABLE TABLE_NAME rename column COLUMN_NAME_NEW to COLUMN_NAME;

Answer 4

This is my aproximation:

  Declare 
  Variableclob Clob;
  Temp_Save Varchar2(32767); //whether it is greater than 4000

  Begin
  Select reportClob Into Temp_Save From Reporte Where Id=...;
  Variableclob:=To_Clob(Temp_Save);
  Dbms_Output.Put_Line(Variableclob);


  End;