how to port Oracle database to PostgreSQL?

ok, this is what's going on. i have this VB.NET application with Oracle 8.1.7 back end, but now i have to switch the whole thing to PostgreSQL. how do i do that? it's a big database, about 135 tables, a lot of views, triggers, a few stored procedures and functions. i understand there are tools that do that. any recommendations?

Thanks.
Lou1Asked:
Who is Participating?
 
rjkimbleConnect With a Mentor Commented:
This might be what you're looking for:

http://www.samse.fr/GPL/ora2pg/

It appears that you may have to adjust the triggersa and functions by hand. Still, it should give you a pretty good start on your effort.
0
 
Lou1Author Commented:
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?
0
 
earth man2Commented:
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

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
rjkimbleCommented:
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

0
 
Lou1Author Commented:
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?
0
 
Lou1Author Commented:
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.
0
 
rjkimbleCommented:
Unfortunately, I'm not a Perl guy. If nobody answers here, you might consider posting that question under the Perl topic.
0
 
earth man2Commented:
# 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;
0
 
earth man2Commented:
To check if DBD::Oracle is installed try
perl -MDBD::Oracle -e 0

This should give no output if OK
0
 
Lou1Author Commented:
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?
0
 
rjkimbleCommented:
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.
0
 
earth man2Commented:
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.
0
 
Lou1Author Commented:
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.
0
 
earth man2Commented:
Cygwin uses if my memory serve me correctly
/cygdrive/C/Oracle/Ora81

Try not putting space either side of = sign.
0
 
Lou1Author Commented:
ok, so i finally figured out how to set the ORACLE_HOME variable. what worked was:

 export ORACLE_HOME=/cygdrive/C/Oracle/Ora81

then i also set the ORACLE_SID, ORACLE_USERID, and LD_LIBRARY_PATH variables, but now i have another problem. here is my listing:

  $ perl makefile.pl
Using DBI 1.45 (for perl 5.008005 on cygwin-thread-multi-64int) installed in /us
r/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/

 Configuring DBD::Oracle ...

>>>     Remember to actually *READ* the README file!
        Especially if you have any problems.

Using Oracle in /cygdrive/C/Oracle/Ora81
I'm having trouble finding your Oracle version number... trying harder
Oracle version 8.1.0 (8.1)
Found oci directory
Using OCI directory 'oci'

Checking for functioning wait.ph


System: perl5.008005 cygwin_nt-5.1 inspiron 1.5.10(0.11642) 2004-05-25 22:07 i68
6 unknown unknown cygwin
Compiler:   gcc -O2 -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -pipe -I/usr/loc
al/include
Linker:     /usr/bin/ld
Sysliblist:


Warning: If you have problems you may need to rebuild perl with -Uusemymalloc.

LD_RUN_PATH=/cygdrive/C/Oracle/Ora81/lib:/cygdrive/C/Oracle/Ora81/rdbms/lib
Using DBD::Oracle 1.16.
Using DBD::Oracle 1.16.
Using DBI 1.45 (for perl 5.008005 on cygwin-thread-multi-64int) installed in /us
r/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/
Writing Makefile for DBD::Oracle

***  If you have problems...
     read all the log printed above, and the README and README.help files.
     (Of course, you have read README by now anyway, haven't you?)


lvassilev@CM12228989 /cygdrive/c/cygwin/lib/perl5/site_perl/5.8.5/cygwin-thread-
multi-64int/DBD
$ make
Skip blib/lib/DBD/Oracle.pm (unchanged)
Skip blib/lib/DBD/DBM.pm (unchanged)
Skip blib/lib/DBD/test1.pl (unchanged)
Skip blib/lib/DBD/Sponge.pm (unchanged)
Skip blib/lib/oraperl.ph (unchanged)
Skip blib/arch/auto/DBD/Oracle/dbdimp.h (unchanged)
Skip blib/lib/DBD/Proxy.pm (unchanged)
Skip blib/arch/auto/DBD/Oracle/ocitrace.h (unchanged)
Skip blib/lib/Oraperl.pm (unchanged)
Skip blib/lib/DBD/File.pm (unchanged)
Skip blib/arch/auto/DBD/Oracle/Oracle.h (unchanged)
Skip blib/lib/DBD/NullP.pm (unchanged)
Skip blib/lib/DBD/ExampleP.pm (unchanged)
Skip blib/lib/DBD/Oracle/GetInfo.pm (unchanged)
cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm
gcc -c  -I/cygdrive/C/Oracle/Ora81/oci/include -I/cygdrive/C/Oracle/Ora81/rdbms/
demo -I/usr/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/ -DPERL
_USE_SAFE_PUTENV -fno-strict-aliasing -pipe -I/usr/local/include -DUSEIMPORTLIB
-O2   -DVERSION=\"1.16\" -DXS_VERSION=\"1.16\"  "-I/usr/lib/perl5/5.8.5/cygwin-t
hread-multi-64int/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"8
.1.0\" Oracle.c
gcc -c  -I/cygdrive/C/Oracle/Ora81/oci/include -I/cygdrive/C/Oracle/Ora81/rdbms/
demo -I/usr/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/ -DPERL
_USE_SAFE_PUTENV -fno-strict-aliasing -pipe -I/usr/local/include -DUSEIMPORTLIB
-O2   -DVERSION=\"1.16\" -DXS_VERSION=\"1.16\"  "-I/usr/lib/perl5/5.8.5/cygwin-t
hread-multi-64int/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"8
.1.0\" dbdimp.c
dbdimp.c:19:20: stdafx.h: No such file or directory
dbdimp.c: In function `ora_db_login6':
dbdimp.c:283: warning: unused variable `o'
dbdimp.c:284: warning: unused variable `l'
dbdimp.c:315: warning: cast to pointer from integer of different size
dbdimp.c:329: warning: cast to pointer from integer of different size
dbdimp.c:339: warning: cast to pointer from integer of different size
dbdimp.c:343: warning: cast to pointer from integer of different size
dbdimp.c:386: warning: unused variable `rsize'
dbdimp.c: In function `dbd_rebind_ph_char':
dbdimp.c:1121: warning: cast from pointer to integer of different size
make: *** [dbdimp.o] Error 1

lvassilev@CM12228989 /cygdrive/c/cygwin/lib/perl5/site_perl/5.8.5/cygwin-thread-
multi-64int/DBD
$ make test
gcc -c  -I/cygdrive/C/Oracle/Ora81/oci/include -I/cygdrive/C/Oracle/Ora81/rdbms/
demo -I/usr/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/ -DPERL
_USE_SAFE_PUTENV -fno-strict-aliasing -pipe -I/usr/local/include -DUSEIMPORTLIB
-O2   -DVERSION=\"1.16\" -DXS_VERSION=\"1.16\"  "-I/usr/lib/perl5/5.8.5/cygwin-t
hread-multi-64int/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"8
.1.0\" dbdimp.c
dbdimp.c:19:20: stdafx.h: No such file or directory
dbdimp.c: In function `ora_db_login6':
dbdimp.c:283: warning: unused variable `o'
dbdimp.c:284: warning: unused variable `l'
dbdimp.c:315: warning: cast to pointer from integer of different size
dbdimp.c:329: warning: cast to pointer from integer of different size
dbdimp.c:339: warning: cast to pointer from integer of different size
dbdimp.c:343: warning: cast to pointer from integer of different size
dbdimp.c:386: warning: unused variable `rsize'
dbdimp.c: In function `dbd_rebind_ph_char':
dbdimp.c:1121: warning: cast from pointer to integer of different size
make: *** [dbdimp.o] Error 1

lvassilev@CM12228989 /cygdrive/c/cygwin/lib/perl5/site_perl/5.8.5/cygwin-thread-
multi-64int/DBD
$ make install
gcc -c  -I/cygdrive/C/Oracle/Ora81/oci/include -I/cygdrive/C/Oracle/Ora81/rdbms/
demo -I/usr/lib/perl5/site_perl/5.8.5/cygwin-thread-multi-64int/auto/DBI/ -DPERL
_USE_SAFE_PUTENV -fno-strict-aliasing -pipe -I/usr/local/include -DUSEIMPORTLIB
-O2   -DVERSION=\"1.16\" -DXS_VERSION=\"1.16\"  "-I/usr/lib/perl5/5.8.5/cygwin-t
hread-multi-64int/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"8
.1.0\" dbdimp.c
dbdimp.c:19:20: stdafx.h: No such file or directory
dbdimp.c: In function `ora_db_login6':
dbdimp.c:283: warning: unused variable `o'
dbdimp.c:284: warning: unused variable `l'
dbdimp.c:315: warning: cast to pointer from integer of different size
dbdimp.c:329: warning: cast to pointer from integer of different size
dbdimp.c:339: warning: cast to pointer from integer of different size
dbdimp.c:343: warning: cast to pointer from integer of different size
dbdimp.c:386: warning: unused variable `rsize'
dbdimp.c: In function `dbd_rebind_ph_char':
dbdimp.c:1121: warning: cast from pointer to integer of different size
make: *** [dbdimp.o] Error 1

lvassilev@CM12228989 /cygdrive/c/cygwin/lib/perl5/site_perl/5.8.5/cygwin-thread-
multi-64int/DBD
$ perl -MDBD::Oracle -e 0
Can't load '/cygdrive/c/Oracle/Ora81/bin/Oracle' for module DBD::Oracle: dlopen,
 Win32 error 126 at /usr/lib/perl5/5.8.5/cygwin-thread-multi-64int/DynaLoader.pm
 line 230.
 at -e line 0
Compilation failed in require.
BEGIN failed--compilation aborted.
0
All Courses

From novice to tech pro — start learning today.