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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to oracle

Examples related to plsql

Examples related to clob