作者:瀚高PG实验室 (Highgo PG Lab)
下载安装perl,下载地址:
Thank You For Downloading ActivePerl - ActiveState
安装dmake,ppm install dmake
下载ora2pg并解压,下载地址
https://sourceforge.net/projects/ora2pg/
编译安装ora2pg
D:\highgo\ora2pg-18.2>perl Makefile.PL
Invalid LICENSE value 'GPLv3' ignored
Generating a dmake-style Makefile
Writing Makefile for Ora2Pg
Invalid LICENSE value 'GPLv3' ignored
Writing MYMETA.yml and MYMETA.json
Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: dmake && dmake install
D:\highgo\ora2pg-18.2>dmake && dmake install
cp lib/Ora2Pg/MySQL.pm blib\lib\Ora2Pg\MySQL.pm
cp lib/Ora2Pg.pm blib\lib\Ora2Pg.pm
cp lib/Ora2Pg/GEOM.pm blib\lib\Ora2Pg\GEOM.pm
cp lib/Ora2Pg/PLSQL.pm blib\lib\Ora2Pg\PLSQL.pm
"C:\Perl64\bin\perl.exe" -MExtUtils::Command -e cp -- scripts/ora2pg blib\script\ora2pg
pl2bat.bat blib\script\ora2pg
"C:\Perl64\bin\perl.exe" -MExtUtils::Command -e cp -- scripts/ora2pg_scanner blib\script\ora2pg_scanner
pl2bat.bat blib\script\ora2pg_scanner
Installing C:\Perl64\html\site\lib\Ora2Pg.html
Installing C:\Perl64\html\site\lib\Ora2Pg\MySQL.html
Installing C:\Perl64\html\site\lib\Ora2Pg\PLSQL.html
Installing C:\Perl64\site\lib\Ora2Pg.pm
Installing C:\Perl64\site\lib\Ora2Pg\GEOM.pm
Installing C:\Perl64\site\lib\Ora2Pg\MySQL.pm
Installing C:\Perl64\site\lib\Ora2Pg\PLSQL.pm
Installing C:\Perl64\site\bin\ora2pg
Installing C:\Perl64\site\bin\ora2pg.bat
Installing C:\Perl64\site\bin\ora2pg_scanner
Installing C:\Perl64\site\bin\ora2pg_scanner.bat
"Installing default configuration file (ora2pg_dist.conf) to C:\ora2pg"
Appending installation info to C:\Perl64\lib/perllocal.pod
dmake.exe: Warning: -- Target [install] was made but the time stamp has not been updated.
安装oracle驱动
C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74>cpan
Loading internal null logger. Install Log::Log4perl for logging messages
Unable to get Terminal Size. The Win32 GetConsoleScreenBufferInfo call didn't work. The COLUMNS and LINES environment variables didn't work. at C:\Perl64\lib/Term/ReadLine/readline.pm line 410.
cpan shell -- CPAN exploration and modules installation (v2.1101)
Enter 'h' for help.
cpan> get DBD::Oracle
Fetching with LWP:
http://ppm.activestate.com/CPAN/authors/01mailrc.txt.gz
Reading 'C:\Perl64\cpan\sources\authors\01mailrc.txt.gz'
............................................................................DONE
Fetching with LWP:
http://ppm.activestate.com/CPAN/modules/02packages.details.txt.gz
Reading 'C:\Perl64\cpan\sources\modules\02packages.details.txt.gz'
Database was generated on Tue, 27 Feb 2018 06:29:03 GMT
.............
New CPAN.pm version (v2.16) available.
[Currently running version is v2.1101]
You might want to try
install CPAN
reload cpan
to both upgrade CPAN.pm and run the new version without leaving
the current session.
...............................................................DONE
Fetching with LWP:
http://ppm.activestate.com/CPAN/modules/03modlist.data.gz
Reading 'C:\Perl64\cpan\sources\modules\03modlist.data.gz'
DONE
Writing C:\Perl64\cpan\Metadata
Running get for module 'DBD::Oracle'
Fetching with LWP:
http://ppm.activestate.com/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz
Fetching with LWP:
http://ppm.activestate.com/CPAN/authors/id/P/PY/PYTHIAN/CHECKSUMS
Checksum for C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74.tar.gz ok
Scanning cache C:\Perl64/cpan/build for sizes
DONE
……
DBD-Oracle-1.74/dbdimp.c
DBD-Oracle-1.74/README
cpan> install DBD::Oracle
示例:使用Ora2pg导出Scott用户下的表
配置Ora2pg配置文件:C:\ora2pg\ora2pg.conf
ORACLE_HOME D:\app\Administrator\product\11.2.0\dbhome_1
ORACLE_DSN dbi:Oracle:host=127.0.0.1;sid=orcl
ORACLE_USER system --导出使用的用户
ORACLE_PWD oracle
SCHEMA scott --将要导出的用户
USER_GRANTS 0
DEBUG 0
ORA_INITIAL_COMMAND
EXPORT_SCHEMA 0
CREATE_SCHEMA 1
COMPILE_SCHEMA 0
TYPE TABLE,INSERT
OUTPUT C:\ora2pg\output.sql --导出文件放置位置
执行导出
C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74>ora2pg -c C:\ora2pg\ora2pg.conf
[========================>] 4/4 tables (100.0%) end of scanning.
[> ] 0/4 tables (0.0%) end of scanning.
[========================>] 4/4 tables (100.0%) end of table export.
[========================>] 0/0 rows (100.0%) Table BONUS (0 recs/sec)
[> ] 0/23 total rows (0.0%) - (0 sec., avg: 0 recs/sec).
[========================>] 4/4 rows (100.0%) Table DEPT (4 recs/sec)
[====> ] 4/23 total rows (17.4%) - (1 sec., avg: 4 recs/sec).
[========================>] 14/14 rows (100.0%) Table EMP (14 recs/sec)
[==================> ] 18/23 total rows (78.3%) - (3 sec., avg: 6 recs/sec).
[========================>] 5/5 rows (100.0%) Table SALGRADE (5 recs/sec)
[========================>] 23/23 total rows (100.0%) - (5 sec., avg: 4 recs/sec).
[========================>] 23/23 rows (100.0%) on total estimated data (5 sec., avg: 4 recs/sec)
导出的文件内容如下:
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=127.0.0.1;sid=orcl
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
SET check_function_bodies = false;
CREATE TABLE salgrade (
grade bigint,
losal bigint,
hisal bigint
) ;
CREATE TABLE dept (
deptno smallint NOT NULL,
dname varchar(14),
loc varchar(13)
) ;
ALTER TABLE dept ADD PRIMARY KEY (deptno);
CREATE TABLE emp (
empno smallint NOT NULL,
ename varchar(10),
job varchar(9),
mgr smallint,
hiredate timestamp,
sal decimal(7,2),
comm decimal(7,2),
deptno smallint
) ;
ALTER TABLE emp ADD PRIMARY KEY (empno);
CREATE TABLE bonus (
ename varchar(10),
job varchar(9),
sal bigint,
comm bigint
) ;
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=127.0.0.1;sid=orcl
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
SET check_function_bodies = false;
BEGIN;
INSERT INTO dept (deptno,dname,loc) VALUES (10,E'ACCOUNTING',E'NEW YORK');
INSERT INTO dept (deptno,dname,loc) VALUES (20,E'RESEARCH',E'DALLAS');
INSERT INTO dept (deptno,dname,loc) VALUES (30,E'SALES',E'CHICAGO');
INSERT INTO dept (deptno,dname,loc) VALUES (40,E'OPERATIONS',E'BOSTON');
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369,E'SMITH',E'CLERK',7902,'1980-12-17 00:00:00',800,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,E'ALLEN',E'SALESMAN',7698,'1981-02-20 00:00:00',1600,300,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521,E'WARD',E'SALESMAN',7698,'1981-02-22 00:00:00',1250,500,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566,E'JONES',E'MANAGER',7839,'1981-04-02 00:00:00',2975,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7654,E'MARTIN',E'SALESMAN',7698,'1981-09-28 00:00:00',1250,1400,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698,E'BLAKE',E'MANAGER',7839,'1981-05-01 00:00:00',2850,NULL,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782,E'CLARK',E'MANAGER',7839,'1981-06-09 00:00:00',2450,NULL,10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788,E'SCOTT',E'ANALYST',7566,'1987-04-19 00:00:00',3000,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7839,E'KING',E'PRESIDENT',NULL,'1981-11-17 00:00:00',5000,NULL,10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844,E'TURNER',E'SALESMAN',7698,'1981-09-08 00:00:00',1500,0,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7876,E'ADAMS',E'CLERK',7788,'1987-05-23 00:00:00',1100,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900,E'JAMES',E'CLERK',7698,'1981-12-03 00:00:00',950,NULL,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902,E'FORD',E'ANALYST',7566,'1981-12-03 00:00:00',3000,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934,E'MILLER',E'CLERK',7782,'1982-01-23 00:00:00',1300,NULL,10);
INSERT INTO salgrade (grade,losal,hisal) VALUES (1,700,1200);
INSERT INTO salgrade (grade,losal,hisal) VALUES (2,1201,1400);
INSERT INTO salgrade (grade,losal,hisal) VALUES (3,1401,2000);
INSERT INTO salgrade (grade,losal,hisal) VALUES (4,2001,3000);
INSERT INTO salgrade (grade,losal,hisal) VALUES (5,3001,9999);
COMMIT;
将以上语句在hgdb中执行即可:
执行结果