[NBLUG/talk] Transferring data from Oracle to an open-source database

Eric Eisenhart eric at nblug.org
Wed May 24 09:29:05 PDT 2006

On Tue, May 23, 2006 at 09:17:13PM -0700, Lincoln Peters wrote:
> I have a project I've been working on for the database class at SSU.  
> It's starting to look like my project will be useful well beyond this 
> class, so I'd like to transfer it from the CS department's Oracle server 
> ("kirby") to my own server.  I'd rather not have to deal with the 
> licensing issues (not to mention cost) of running my own Oracle server, 
> so I'd like to migrate it to an open-source SQL database such as MySQL 
> or PostgreSQL.

What are you going to use the DB server for?  Oracle will let you run a
development-only, single-user version for free and has had that arrangment
for a long time.  They've also got a newer "Express Edition" thing that's
more limited in some ways, but has much less restrictive licensing
(redistributable in commercial products!) and is a lot easier to install. 
(restrictions are things like 4GB of data, 1 CPU used, 1GB of RAM used)


> What I'd like to find out is:
> * Which open-source database is preferred?  As far as I can tell, MySQL 
> is the most popular, but I've heard good things about PostgreSQL as well.

It depends.

Basically, PostgreSQL has fuller support for the ANSI SQL standard.  MySQL's
been catching up, especially if you use the InnoDB table format that gets
you transactions.  If you're doing complicated SQL with sub-SELECTs and
things like that, you're gonna want PostgreSQL.  Another example: PostgreSQL
uses sequences; MySQL uses a marker on a column that sets it as
auto-increment.  (you can emulate MySQL's behavior in PostgreSQL or Oracle
with a sequence and a trigger).  Heck, for that matter, PostgreSQL can do
triggers and I don't think MySQL does them yet.

On the other hand, having dealt with both, MySQL is a lot easier to manage
that PostgreSQL.  PostgreSQL has some weird ideas about things with trying
to match DB accounts to user accounts.

Also, PgSQL has (had? I haven't kept up) issues with CLOBs and BLOBs, and
the DB driver doesn't support placeholders, forcing DBI to emulate them
without the full security benefits of placeholders.

> * Based on my understanding of the Perl DBI (which I've studied but have 
> not yet used), it should be a simple matter to transfer all of the 
> existing records from the Oracle database into any other RDBMS supported 
> by the Perl DBI.  Assuming that I use either MySQL or PostgreSQL, will 
> this be a simple task?

Reasonably so, yes.  Especially if you're willing to write the new table
definitions by hand and you're only trying to script the transfer of data.

Something like this:

my $oradbh = DBI->connect(blah blah blah);
my $mysqldbh = DBI->connect(blah blah blah);

my $select_sql = 'select a,b,c from foo';
my $insert_sql = 'insert into foo (a,b,c) values (?,?,?);

my $selectsth = $oradbh->prepare($select_sql);
my $insertsth = $mysqldbh->prepare($insert_sql);
while (my @data = $selectsth->fetchrow_array()) {

Or even easier if you find something that does the work for you.  Say this:

(nothing quite so easy for Pg that I could find in under 30 seconds of
googling.  http://techdocs.postgresql.org/techdocs/oracle-to-pg-porting.php
is the best I could do...)
Eric Eisenhart
NBLUG Co-Founder, Scribe and InstallFest Coordinator
The North Bay Linux Users Group -- http://nblug.org/
eric at nblug.org, IRC: Freiheit at fn AIM: falschfreiheit

More information about the talk mailing list