Get CLOB data in a text/xml file

Question

i have a table in a oracle database in which one of the field is a clob field. the clob field is having several xml file. now if i want to export that xml file into a text file on unix how do i do it?

NOTE: i dont have any gui like toad installed for the server and i have to only use the unix environment for it. could you please tell me how to do it?

Answer 1

You could use the package UTL_FILE and use PL/SQL to write directly from the database to a file.

Answer 2

Assuming the machine you want the file on is not the database server itself, you should probably write a program to do this. In Perl, for example, it's quite simple:

use DBI;
my $db = DBI->connect('dbi:Oracle:tnsname','user','password',{RaiseError=>1});
my $txt = ($db->selectrow_array('select my_clob_col from my table where ...'))[0];
$db->close;
open my $fh, '>out.txt' or die $!;
print $fh $txt;
close $fh;

Crude but it will work for simple cases. The point is that in any modern database client API, fetching a CLOB will automatically return you a string value which you can then do what you want with.