Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to port Oracle database to PostgreSQL?

Posted on 2004-10-21
15
Medium Priority
?
1,212 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:Lou1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 9

Accepted Solution

by:
rjkimble earned 1000 total points
ID: 12372853
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
 

Author Comment

by:Lou1
ID: 12374889
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
 
LVL 22

Expert Comment

by:earth man2
ID: 12375438
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 9

Expert Comment

by:rjkimble
ID: 12375814
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
 

Author Comment

by:Lou1
ID: 12401120
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
 

Author Comment

by:Lou1
ID: 12401137
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
 
LVL 9

Expert Comment

by:rjkimble
ID: 12404282
Unfortunately, I'm not a Perl guy. If nobody answers here, you might consider posting that question under the Perl topic.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12406324
# 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
 
LVL 22

Expert Comment

by:earth man2
ID: 12406375
To check if DBD::Oracle is installed try
perl -MDBD::Oracle -e 0

This should give no output if OK
0
 

Author Comment

by:Lou1
ID: 12412304
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
 
LVL 9

Expert Comment

by:rjkimble
ID: 12412599
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
 
LVL 22

Expert Comment

by:earth man2
ID: 12415970
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
 

Author Comment

by:Lou1
ID: 12424391
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
 
LVL 22

Expert Comment

by:earth man2
ID: 12428487
Cygwin uses if my memory serve me correctly
/cygdrive/C/Oracle/Ora81

Try not putting space either side of = sign.
0
 

Author Comment

by:Lou1
ID: 12434199
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question