How do I export clob fields bigger than 40000

[kizziebutler]

I have some clob fields that need to extracted to csv file but some of these fields are bigger than 40000 in length. I have tried packages such as dbms_lob.substr(clobfield), but this is limited to 4000. Any suggestions!!!

 

[sem]

Try to use utl_file package.

Regards, Dima

http://seminihin.narod.ru

 

[kizziebutler]

could you give me an example of how this would work.

 

[sem]

Do you need an example or want me to do your work?
Examples on using utl_file (if you need them) are in Oracle manuals, but package specification itself is enough to start from; you know how dbms_lob.substr works; most probably know how to loop through cursor; thus I can't get what kind of example you need. But when you have a specific issue (unclear error or so), welcome to ask a specific question.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Dima,

I had intended to reply with a utl_file suggestion, but I believe it's limited to a maximum individual line length of 32,767 characters, and the question explicitly states 40,000 characters.

How say you Dima?

Regards

Tharg

Grinding away at things Oracular

 

[sem]

Yes, it's limited by 32K but I can hardly imagine a clob (a text!) without carriage returns. Thus the whole text may be splitted by lines and utl_file.new_line called after each chunk written by utl_file.put.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Kizzie,

after my little chat with Dima above, I have to say that I concur with him. Is the user really demanding an uninterrupted line of 40,000 characters? What useful purpose do they have for this?

Can you elucidate and/or refine the requirement for us, as I don't think the users are asking the right things of you.

If the line can be split, then the substr solution will do just fine.

Note that by explicitly adding the ascii characters for carriage return and line feed, you can even format the output lines.

Regards

Tharg

Grinding away at things Oracular

 

[kizziebutler]

I have written a pl sql using the utl_file package. I am still experiencing problems with the clob field, however I found that there are only three clob fields that reach the maximum of 40000 and the rest are about 32k or below (so I can work with clob fields less than 32 k). The clob field contains xml coding, and is extracted as an csv file to a weblogic, but I am still not happy with my coding. An example would be appreciated, I am happy to post the work I have completed.

 

[Thargy]

Kizzie,

it's great to hear that you've succeeded with utl_file - well done.

If you like, post your code and Dima can provide erudite comment, me I'll just look on and pretend I know what I'm talking about.

However, you must point out to your users that their requirements are unreasonable. If they really need such huge XML objects, then I humbly suggest that they haven't divided things down into enough strongly typed items. No matter how big a task is, it can always be divided down in the end.

Could you point out to the users that by exceeding the limits of the standard tools, they will incur enormous cost and reduce the reliability of code. As I understand XML, the whole point of a DTD is to avoid vast chunks of text, it's all broken down into pre-determined values etc.

I believe that this is not a technical issue, but a failure to make those setting the requirements really think about what they're saying.

Sorry to be such a purist, but I think you may be accepting too much hassle from your users.

Regards

Tharg

Grinding away at things Oracular