I love Perl, sed, vi, awk, etc; but SQL, SQL*PLUS, PL/SQL, and Java are
easier when using Oracle.





-----Original Message-----
From: tboss
[mailto:oracledba-ezmlmshield-x7904466.[Email address protected]
Sent: Tuesday, August 03, 2004 10:16 AM
To: LazyDBA Discussion
Subject: Re: Exporting data from tables into a CSV delimited file


I have a perl-based solution, if you like perl. And who doesn't
like perl! :-)

The advantage this has over simple PL-SQL solution is that it
encapsulates all the values with ' ' marks, so the import won't
get confused if you have text w/ embedded commas.

#!/usr/local/bin/perl
use DBI;

sub maketablecsv {
$table='yourtablehere';

open(OUT,">$table.csv");

$sql = "select ";

$dbh = DBI->connect ('DBI:Oracle:SID', 'scott', 'tiger');

$sql = "select * from $table";
$sth = $dbh->prepare($sql);
$sth->execute;

while (@row = $sth->fetchrow_array) {
$count=@row;
for ($i=0;$i<$count;$i++) {
print OUT "'$row[$i]'";
print OUT "," unless ($i == $count-1);
}
printf OUT "\n";
}

$sth->finish;
$dbh->disconnect;
close(OUT);
}

hope this helps, boss

>
> Set colsep ','
> Spool comdem.csv
> Select * from cat;
> Spool off
>
>
> -----Original Message-----
> From: Edwin Uy
> [mailto:oracledba-ezmlmshield-x45061172.[Email address protected]
> Sent: Tuesday, August 03, 2004 10:59 AM
> To: LazyDBA Discussion
> Subject: Exporting data from tables into a CSV delimited file
>
> Hi all,
>
> SQLLDR is used to import data from delimited files into the tables but
> what is used to extract data from the tables and into a delimited file
> ... I remember someone posted this into the FORUM before but don't think
> he got a response about it ... I can write a PLSQL script and spool into
> a file, but am sure there is something better than that ... just can't
> remember which one .... aargggghhhh
>
> Cheers,
>
>
> EDWIN ONG UY
> \\|//
> (o o)
> ^"^"^"^"^"^oOOo"^(_)^"oOOo^"^"^"^"^"^"^"^"^"^"^
>
> "Choose a job you love, and you will never have to work a day in your
> life." --Confucius
> "How do I work? I grope." -- Albert Einstein
> "Experience is the best teacher, enrolling in the class is the biggest
> problem." -- Anonymous
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page