Conversion of CLOB data type to varchar

Please try this -

select to_char(SUBSTR(cdata,0)) from table1 where xxx_ID= xxx

Select allOpen in new window

The last length parameter is optional. If you skip that then you will all the data from the start position you have given.

You can read more about SUBSTR function from - https://www.techonthenet.com/oracle/functions/substr.php

Also if you need full data you DO NOT need substr. You need substr if you want to get some substring from a string.

select to_char(cdata) from table1 where xxx_ID= xxx

Select allOpen in new window

@Pawan:  I did try 1:select to_char(cdata) from table1 where xxx_ID= xxx got the below error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 8785, maximum: 4000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

Sorry i missed that it is CLOB data type.

Please try this

select dbms_lob.substr(cdata,32767,1) from table1 where xxx_ID = xxx

Select allOpen in new window

Edited my last comment.

I got error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

I think you need something like this ( from EE database )

https://www.experts-exchange.com/questions/27431493/ora-06502-Plsql-numeric-or-value-error-Character-string-buffer-too-small.html?anchorAnswerId=37108030#a37108030

Thanks for our help