Re: How to export from Oracle to text using Perl

Suprising...

Did u try dump_csv at http://asktom.oracle.com

Search for DUMP_CSV in that site.

Ensoy !!!

-- 
HTH

Regards,
Ganesh R
"Tamar" <tamarr_at_atomica.com> wrote in message
news:7282f42a.0306110449.14a5cb06_at_posting.google.com...


> We are using Oracle 8.1.7 and were surprised to find that there is no

> utility to export entire tables to text files. I searched around and

> found that most people suggested using SQLPlus for this, but I find

> that its relatively slow and really not efficient with space, since

> you have to define the line size in advance.

>

> Using Perl with DBI and DBD we were able to write a very short script

> that not only dumps the entire schema, including CLOBs, but prepares

> it for import into MySql by adding "" around the fields, and escaping

> both " and \. The script is as follows:

>

> #!/usr/bin/perl

>

> use strict;

> use DBI;

> use DBD::Oracle qw(:ora_types);

>

> my $sid = "mysid";

> my $oracle_port = 1521;

> my $uid = "myid";

> my $passwd = "mypasswd";

> my $host = "myhost";

>

> my $dbh = DBI->connect("DBI:Oracle:host=$host;sid=$sid", $uid,

> $passwd, {'LongReadLen' => 400000}) or

>         die "Can't connect to database";

>

> my $startAll = time;

> my @tables = $dbh->tables();

> my $startTable;

>

> LINE: foreach my $table (@tables){

> if ($table=~s/SCHEMA_NAME\.//) {

> $startTable = time;

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

> $sth->execute();

> my $row;

> my $s;

> open TABLE, ">>$table.txt";

> while($row = $sth->fetchrow_arrayref){

> print TABLE join "\t", (map {($s = $_) =~ s/["\\]/\\$&/g;

> qq("$s")} @$row), "\n";

> }

> close TABLE;

> $sth->finish();

> print $table, " took " , (time-$startTable),  " seconds\n";

> }

> }

> print "total time : ", (time-$startAll), " seconds\n";