Solved

how to port Oracle database to PostgreSQL?

Posted on 2004-10-21
1,087 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
Question by:Lou1
    15 Comments
     
    LVL 9

    Accepted Solution

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

    Expert Comment

    by:rjkimble
    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
    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
    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
    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
    # 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
    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
    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
    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
    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
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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.
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.

    931 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now