Conversion of CLOB data type to varchar

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!
jasmeen kaurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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 -

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

jasmeen kaurAuthor 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
           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 ExpertCommented:
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

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Pawan KumarDatabase ExpertCommented:
Edited my last comment.
jasmeen kaurAuthor 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"
Pawan KumarDatabase ExpertCommented:
awking00Information Technology SpecialistCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jasmeen kaurAuthor Commented:
Thanks for our help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.