Well, how the hell do I do that?
Here’s how:
1. Install perl (if you don’t already have it):
- apt-get install perl
- perl -MCPAN -e shell
- m DBI
- get DBD::Oracle
2. Install oracle instant client on your linux box (if you don’t already have it):
- apt-get install libaio1 (http://ubuntuforums.org/showthread.php?t=169492)
- apt-get install alien (we’ll get rpms from oracle)
- Download instant client (I did basic, dev & sqlplus). I had to do this without wget because of oracle’s sign-in requirements.
- alien oracle-instantclientxxxx-basicxxxxxx.rpm
- alien oracle-instantclientxxxx-devexxxxxx.rpm
- alien oracle-instantclientxxxx-sqlplusxxxxxx.rpm
- dpkg -i *.deb
3. Configure tnsnames
- Located by default in /usr/lib/oracle/xxx/client/network/admin (http://www.orafaq.com/wiki/Tnsnames.ora)
- Set environment variables:
- export ORACLE_HOME=/usr/lib/oracle/xxx/client
- export PATH=$PATH:$ORACLE_HOME/bin
- export LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Modify /etc/environment to contain these vars
- SQLPlus to test linux box connection to oracle db instance using tnsnames
4. Install DBD::ORacle
- cd /root/.cpan/build/DBD-Oracle-xxx-xxxx/
- perl Makefile.PL
- make
- make test (optional)
- make install
5. Download & install ora2pg
- Download ora2pg (check for latest vers before running) wget http://pgfoundry.org/frs/download.php/2894/ora2pg-7.1.tar.bz2
- tar xjvf ora2pg-7.1.tar.bz2
- cd ora2pg-7.1/
- perl Makefile.PL
- make
- make install
6. Configure ora2pg
- vim /etc/ora2pg/ora2pg.conf
- set ORACLE_DSN to dbi:Oracle:SID (sid must match tnsnames)
- set ORACLE_USER and ORACLE_PWD
- verify ORACLE_HOME
- Uncomment FUNCTION line under “Type of export”
7. Run ora2pg
- ora2pg -p /etc/ora2pg/ora2pg.conf
- Output.sql is the plpgsql converted code.
I didn’t run the psql from here – instead I picked the 3 or 4 that the client wanted and manually ran them in pgAdmin. Why? – well – ora2pg is good but does not capture everything. One example I found is that it doesn’t convert defaulted parameters well.
Leave a Reply