[oracle] How to convert CLOB to VARCHAR2 inside oracle pl/sql

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.

This question is related to oracle plsql clob

The answer is


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. PLSQL Developer Long Value

C#?

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


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;

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


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;

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to plsql

How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence PLS-00428: an INTO clause is expected in this SELECT statement What is the max size of VARCHAR2 in PL/SQL and SQL? PLS-00201 - identifier must be declared Default Values to Stored Procedure in Oracle How to call Oracle MD5 hash function? Proper way of checking if row exists in table in PL/SQL block PLS-00103: Encountered the symbol when expecting one of the following:

Examples related to clob

Difference between CLOB and BLOB from DB2 and Oracle Perspective? Search for a particular string in Oracle clob column Error : ORA-01704: string literal too long Extract data from XML Clob using SQL from Oracle Database ORA-00932: inconsistent datatypes: expected - got CLOB How to convert CLOB to VARCHAR2 inside oracle pl/sql Java: How to insert CLOB into oracle database Disabling contextual LOB creation as createClob() method threw error How to query a CLOB column in Oracle Most efficient solution for reading CLOB to String, and String to CLOB in Java?