Re: please help: extracting clob data contains XML

"jend" <dejen321_at_yahoo.com> wrote in message news:cc028093.0209251649.348e174e_at_posting.google.com...

> i can successfully extract and write out to files with DBMS_LOB and
> UTL_FILE packages, but many of the xml_map were cut off because it
> exceeds the max linesize (32767 or something i am guessing since it
> always cuts exactly at 32768 character) but some of the xml_map are
> like 40-50k. So is there a way to get the entire xml out?
>
> i am thinking there are some options:
>
> 1) trim all the spaces in the xml_map? would this even help and how
> do i do this w/ clob ?
>
> 2) break up and substr the clob by 32767 chunks but i can't find any
> documentation on how to do this? any dbms_lob packages does this?

Maybe I'm missing something but I tought this could be done with dbms_lob.read. It can read a specified amount of a clob from a specified offset. So you loop till you've got it all. Check the dbms_lob manual.

> 3) is the 32767 linesize even the problem? or is there any
> enviroment variable I can manipulate to let me get the entire xml

32767 is the maximum size of the readbuffer. It is also the maximum size of a line that utl_file can handle (that is, if you use the overloaded version of utl_file.fopen where you can specify the max linesize. Doc Id : 1026951.6 on Metalink should get you further if needed). Hard to say what happens in your case.

Hope this helps.
Regards, Joost

--
Joost Bataille
University of Amsterdam ICT centre