Remember all the oracle functions? We need those in postgresql.

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 (
  • 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 (
  • Set environment variables: 
    • export ORACLE_HOME=/usr/lib/oracle/xxx/client
    • export PATH=$PATH:$ORACLE_HOME/bin
  • 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

6. Configure ora2pg

  • vim /etc/ora2pg/ora2pg.conf
    • set ORACLE_DSN to dbi:Oracle:SID (sid must match tnsnames)
    • 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

Powered by

Up ↑

%d bloggers like this: