how to port Oracle database to PostgreSQL?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ok, this does look like what i need. i downloaded the program, but how do i use it now? there are basically 3 different files: ora2pg.pl, ora2pg.conf, and ora2pg.pm. In the configuration file ora2pg.conf i made the necessary modifications so that it would connect to my Oracle database. do i need to do any more changes. and how do i run the thing?

You need to have perl, You just need the DBI, DBD::Pg and DBD::Oracle perl module to be installed see http://www.perl.org and http://www.perl.org/app.html
try
vi ora2pg.conf
perl -version
chmod +x ora2pg.pl
./ora2pg.pl ora2pg.conf

Are you going to be running the script under Windows or under Linux/Unix? If Windows, you may want to consider downloading the ActiveState Perl distribution:

http://www.activestate.com/Products/ActivePerl/

Alternatively, you can get the core distribution at:

http://www.perl.com/CPAN/ports/win95/Standard/

You can get the DBI, DBD::Pg and DBD::Oracle modules from CPAN:

http://cpan.org/modules/by-module/DBI/
http://cpan.org/modules/by-module/DBD/

Instructions for installing modules can be found here:

http://cpan.org/modules/INSTALL.html

ASKER

i am running it under Windows. I've installed ActiveState Perl and i also installed the DBI and DBD:Oracle modules, although i don't think i installed them right since when i try to run a small Perl program to connect to an Oracle db, i get this error:

can't locate object method "connect" via package "DBI"

here is my program:

#!/usr/bin/perl
use lib "/DBI/DBI-1.45/DBI";

#database connection variables;
my $dbname = "dbname";
my $user = "username";
my $password = "password";

#connection string for the database, change this with change of db;
$connect_string = "dbi:Oracle:$dbname";

#the actual connection string for the database;
$dbh = DBI->connect($connect_string, "$user", "$password") --> ERROR HERE
or die "Could not open database: " . $DBI::errstr;

#disconnecting the database handle;
$dbh->disconnect
or print ERROR "Error disconnecting: $DBI::errstr";

print "end program\n";
exit;

any ideas what's worng here?

ASKER

btw, i've read the readme files that came with DBI and DBD:Oracle, but didn't find them helpful. i am kinda new at this, i've never had to instal Perl and mess PostgreSQL before so i guess the language in the above mentioned files was a bit out my league.

Unfortunately, I'm not a Perl guy. If nobody answers here, you might consider posting that question under the Perl topic.

# need http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBD-Oracle-1.16.tar.gz
# perl Makefile.pl
# make install

#!/usr/bin/perl
use DBI;
my $dbname = "dbname";
my $user = "username";
my $password = "password";
my $connect_string = "dbi:Oracle:$dbname";
my $dbh = DBI->connect($connect_string, $user, $password ) or die "Could not open database: " . $DBI::errstr;
$dbh->disconnect or print ERROR "Error disconnecting: $DBI::errstr";
print "end program\n";
exit;

To check if DBD::Oracle is installed try
perl -MDBD::Oracle -e 0

This should give no output if OK

ASKER

earthman2,

i already did that, but i must have done it wrong. anyway, here is exactly what i did:

1. i installed CygWin and when i launch it i am in directory "C:\Documents and Settings\myusername"
2. i installed ActiveState Perl. i think that went in "C:\Perl"
3. i installed DBI 1.45 in directory "C:\Documents and Settings\myusername\cygwin", i.e. i unpacked the module files and extracted them in that directory and then did:
perl makefile.pl
make
make test
make install

4. i installed DBD Oracle in directory DBI-1.45, i.e. i unpacked the module files and extracted them in "C:\Documents and Settings\myusername\cygwin\DBI-1.45" and did:
perl makefile.pl
make
make test
make install

i don't remember if i got any errors while doing 3 and 4.

btw, i did "perl -MDBD::Oracle -e 0" and it toild me "can't locate Oracle.pm in @INC(........)". so i guess i did not install it properly.

btw, i was told yesterday that Ora2Pg comes witrh PostgreSQL. is that true? if so, how do i launch it from PostgreSQL?

Let me put in my two cents. If you're installing perl modules under Cygwin, you're going to have to run the Cygwin version of perl. Forget ActiveState Perl in that case. You might be better off uninstalling it. Then run your Perl stuff from the Cygwin prompt.

Ora2pg comes as the perl script in the $PGHOME/contrib/oracle directory. No magic bullet there you still need to install the Perl guts.

On my linux system stuff ends up in the following directory.
/usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi/DBD/Oracle

Prior to make process you should define environment variable ORACLE_HOME correctly.

ASKER

ok, so how do i set the ORACLE_HOME variable under CygWin? i tried, at the command prompt:

set ORACLE_HOME = "c:/Oracle/Ora81",

set $ENV{ORACLE_HOME} = "c:/Oracle/Ora81", and

perl -e '$ENV{ORACLE_HOME} = "c:/Oracle/Ora81";',

but none worked.

btw, earthman2, i did

"perl -MDBI -e 0" at the command prompt and it gave no output, so i am guessing at least my DBI module is installed correctly.

Cygwin uses if my memory serve me correctly
/cygdrive/C/Oracle/Ora81

Try not putting space either side of = sign.