How to export from Oracle to text using Perl

From: Tamar <tamarr_at_atomica.com>
Date: 11 Jun 2003 05:49:33 -0700
Message-ID: <7282f42a.0306110449.14a5cb06@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"; Received on Wed Jun 11 2003 - 07:49:33 CDT