Conversion of CLOB data type to varchar

jasmeen kaur
jasmeen kaur used Ask the Experts™
on
Hi ,

One my column in database stores a large xml file which I have to extract
I tried the below query

select to_char(SUBSTR(cdata,0,4000)) from table1where xxx_ID= xxx

I get only 4000 characters obviously. ow can I get the whole data.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this -

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

Open 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

Open in new window

Author

Commented:
@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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Open in new window

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last comment.

Author

Commented:
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:
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Information Technology Specialist
Commented:
I'm assuming your dbms is Oracle (from the use of the substr function). If so, to get the entire xml, you might try -
select xmltype(cdata) from table1 where xxx_id = xxx
Once the data is in xmltype datatype, you can apply the various Oracle xml functions to extract the data.

Author

Commented:
Thanks for our help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial