Installing Ora2Pg

An Oracle Client is required on the machine:

./runInstaller -silent -noconfig -responseFile /tmp/client/client_install.rsp

Run your root scripts and configuration assistant scripts in separate window, post install.

Reference the installation document.  Like any other Perl Module Ora2Pg can be installed with the following commands:

        tar xzf ora2pg-18.x.tar.gz
        cd ora2pg-18.x/
        perl Makefile.PL
        make && make install

you may need to run:
       sudo make install 
if permission issues arise

This will install into your site Perl repository, ora2pg into /usr/local/bin/ and ora2pg.conf.dist into /etc/ora2pg/.

Installing DBD::Oracle

Ora2Pg need perl module DBD::Oracle for connectivity to an Oracle database from perl DBI. To get DBD::Oracle get it from CPAN a perl module repository.

After setting ORACLE_HOME and LD_LIBRARY_PATH environment variables as root user, install DBD::Oracle. Proceed as follow:

        yum install perl-CPAN        
        export LD_LIBRARY_PATH=/data/oracle/client/lib
        export ORACLE_HOME=/data/oracle/client
        perl -MCPAN -e 'install DBD::Oracle'
I was getting an error for missing perl module:
        yum install perl-Time-HiRes


Configure ora2pg parameter file

vi /etc/ora2pg/ora2pg.conf <<— Configure some mandatory and optional parameters



# Set the Oracle home directory
ORACLE_HOME /data/oracle/client

# Set Oracle database connection (data source, user, password)
ORACLE_USER username
ORACLE_PWD password
# Oracle schema/owner to use
# EXPORT SECTION (Export type and filters)

# Type of export. Values can be the following keyword:
# TABLE Export tables, constraints, indexes, …
# PACKAGE Export packages
# INSERT Export data from table as INSERT statement
# COPY Export data from table as COPY statement
# VIEW Export views
# GRANT Export grants
# SEQUENCE Export sequences
# TRIGGER Export triggers
# FUNCTION Export functions
# PROCEDURE Export procedures
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)
# TYPE Export user-defined Oracle types
# PARTITION Export range or list partition (PostgreSQL >= v8.4)
# FDW Export table as foreign data wrapper tables
# MVIEW Export materialized view as snapshot refresh view
# QUERY Convert Oracle SQL queries from a file.
# KETTLE Generate XML ktr template files to be used by Kettle.

# By default all output is dump to STDOUT if not send directly to PostgreSQL
# database (see above). Give a filename to save export to it. If you want
# a Gzip’d compressed file just add the extension .gz to the filename (you
# need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2
# compression.
OUTPUT output.sql
# Base directory where all dumped files must be written
#OUTPUT_DIR /var/tmp
OUTPUT_DIR /tmp/ora2pg



Limiting object to export

Test your connection:
ora2pg -t SHOW_VERSION -c ora2pg.conf
...Oracle Database 12c Enterprise Edition Release

ora2pg -c ora2pg.conf -t PROCEDURE

Converts PL/SQL to PL/PGSQL

Go to the directory you set in your parameter file and review your output.  The conversion is not 100% and like everything, it will require testing and fixing.

cd /tmp/ora2pg

vi output.sql