how to install ora2pg on windows 7?

Question

i downloaded the zip file which was available online for free download but i a not able to install the downloaded file and run the file, can any one please help me installing the ora2pg on my windows machine please.

Solution

Summary: I had to migrate few tables from Oracle database to PostgreSQL for my local development team to work on few modules of the application.

How? To save time, I did some research in postgreSQL blogs and found a tool ora2pg tool. This tool works best on UNIX as described in the blog. But there are quite good challenges when your PostgreSQL is on windows system.

Challenge:

• Install Perl 5.0 on windows
• Install Oracle DBD libraries to perl on windows
• Tables structure should exists prior running the tool
• Oracle client "Administrator" pack to be installed on windows.

Action:

Pre-requisites:

• Download and install perl on your windows from "http://strawberryperl.com/"
• Install oracle client on your windows
• Download ora2pg software and untar to a known location on windows from "http://sourceforge.net/projects/ora2pg/files/13.0/ora2pg-13.0.tar.bz2/download"
• PostgreSQL is pre-installed on your windows machine. Else download it from "http://www.enterprisedb.com/products-services-training/pgdownload#windows"

Installation:

In cmd on your windows. Make sure you have internet connection:

cd <un tarred location>\ora2pg-15.2
perl Makefile.PL

dmake && dmake install

Set the environment variables:
Set ORACLE_HOME=<ORACLE_CLIENT_SOFTWARE_LOCATION>

Set LD_LIBRARY_PATH=<ORACLE_CLIENT_HOME>/lib

Install DBD::Oracle libraries - Internet is must

perl -MCPAN -e "install DBD::Oracle"

• Configuration file is created by default in C:\ora2pg
• Copy the template config file to original file name and edit
• Copy ora2pg_dist.conf to ora2pg.conf

Edit config file as in the example I used for one table:

ORACLE_HOME          C:\oracle\app\yaddanap\product\11.2.0\client_1
ORACLE_DSN  dbi:Oracle:host=172.31.232.253;sid=SUPLINUX
ORACLE_USER            system
ORACLE_PWD 1qaz2wsx!
USER_GRANTS     0
DEBUG                        0
EXPORT_SCHEMA       0
SCHEMA                      DB000001
CREATE_SCHEMA       0
COMPILE_SCHEMA     0
EXPORT_INVALID        0
TYPE                TABLE
ALLOW                        SL_01_DEPOS
DATA_LIMIT    150000
PG_DSN                       dbi:Pg:dbname=Drive1;host=localhost;port=5432
PG_USER                     kcc
PG_PWD                      kcc123
PRESERVE_CASE         0
BZIP2
GEN_USER_PWD         0
FKEY_DEFERRABLE     0
DEFER_FKEY    0
DROP_FKEY     0
DROP_INDEXES           0
PG_NUMERIC_TYPE    1
PG_INTEGER_TYPE     1
DEFAULT_NUMERIC bigint
KEEP_PKEY_NAMES 0
DISABLE_TRIGGERS 0
NOESCAPE       0
DISABLE_SEQUENCE   0
PLSQL_PGSQL 1
ORA_RESERVED_WORDS       audit,comment
FILE_PER_CONSTRAINT          0
FILE_PER_INDEX                     0
FILE_PER_TABLE         0
TRANSACTION committed
PG_SUPPORTS_WHEN                        1
PG_SUPPORTS_INSTEADOF    1
FILE_PER_FUNCTION  0
TRUNCATE_TABLE      0
FORCE_OWNER          0
STANDARD_CONFORMING_STRINGS 1
JOBS                1
ORACLE_COPIES         1
PARALLEL_TABLES      1
ALLOW_CODE_BREAK            1
XML_PRETTY   0
DISABLE_COMMENT         0
USE_RESERVED_WORDS        0
PKEY_IN_CREATE                    0
NULL_EQUAL_EMPTY 1
EXTERNAL_TO_FDW               1
ESTIMATE_COST                     0
COST_UNIT_VALUE                 5
DUMP_AS_HTML                    0
STOP_ON_ERROR                   1
TOP_MAX                                10
ALLOW_PARTITION                PARTNAME
USE_TABLESPACE                   0
PG_SUPPORTS_MVIEW          1
REORDERING_COLUMNS       0
SYNCHRONOUS_COMMIT      0
PG_SUPPORTS_CHECKOPTION           0
AUTODETECT_SPATIAL_TYPE 1
CONVERT_SRID                       1
DEFAULT_SRID                        4326
GEOMETRY_EXTRACT_TYPE  WKT
PREFIX_PARTITION     0
LOG_ON_ERROR                    0
PG_SUPPORTS_IFEXISTS         1
WITH_OID                   0

run the ora2pg now:

c:\ora2pg>ora2pg -c ora2pg.conf

Run the generated DDL statement in PostgreSQL to create structure of the table Now edit ora2pg.conf file at one field as below again to copy the data now.

 TYPE                TABLE,INSERT

Re-run ora2pg tool now and you will see the data pooled.

c:\ora2pg>ora2pg -c ora2pg.conf

[========================>] 953/906 rows (105.2%) Table SL_01_DEPOS (952.9 recs/sec)

[========================>] 906/906 rows (100.0%) on total data (avg: 905.9 rec/sec

Comments

[Manuel Mannhardt]

Neither is this a programming issue, nor do you provide much informations what you have tried. Did you try downloading it somewhere else? Running repair options on it? etc.pp

[Smithendu doddamane]

i have finished installing the strawberry perl and now i am trying to edit the config file where in i am not able to run the config file after editing.

[Smithendu doddamane]

DBI connect('host="Host_id";sid=""SID";port=1521','system',...) failed: E RROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and o r NLS settings, permissions, etc. at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1491. FATAL: -1 ... ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. Aborting export... this error is occuring while trying to run batch file

[Smithendu doddamane]

i have set the oracle path, but now the error is different, its says TNS:no listener (DBD ERROR: OCIServerAttach)

[chq]

Une year later (or more) I'm trying to follow this tutorial. But can't execute ora2pg, its followed by the error: "No Perl script found in input". Any clues on why this is happening or how to fix it?

[a_horse_with_no_name]

The question was about installing ora2pg on Windows 7 your explanation is for Linux.

[Sudhakar Pandey]

Yes agree with you but you can easily find windows related similar command and on configuration file part will be remain in both environment.