ora2pg使用

一、环境准备

centos 6.5
oracle 11.2.0.4
perl v5.10.1
DBD::Oracle
DBI
DBD::Pg -- 如果需要将结果直接导入到postgresql需要安装

各模块安装如下

-- 使用yum安装DBI, DBD::Pg
# yum install perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker gcc
-- 安装DBD::Oracle，centos上已安装了oracle，如未安装oracle，也可用oracle client代替，此处不详说
# su - oracle
$ wget --no-check-certificate http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz
$ tar -zxvf DBD-Oracle-1.74.tar.gz
$ cd DBD-Oracle-1.74
-- 生效环境变量，.bash_profile文件中需包含ORACLE_HOME,LD_LIBRARY_PATH
$ source /home/oracle/.bash_profile
$ cat /home/oracle/.bash_profile
...
export ORACLE_HOME=/data/app/oracle/product/11.2.0/dbhome_1
if [ -z "$LD_LIBRARY_PATH" ]; then
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
else
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
fi
...
-- 安装ora2pg
# tar -jxvf ora2pg-18.2.tar.bz2
# cd ora2pg-19.0
# perl Makefile.PL
# make && make install

二、配置参数

ora2pg的配置文件ora2pg.conf，可以配置相关oracle连接信息，需要导出的对象，目标库postgresql的连接配置等等。

-- 设置oracle连接参数
ORACLE_DSN  dbi:Oracle:host=192.168.112.22;sid=cndb;port=1521
ORACLE_USER system
ORACLE_PWD  root
-- 需要导出的schema
SCHEMA      DBUSER
-- 需要导出的对象，表结构、数据、触发器等等，详见文件内说明
TYPE    TABLE COPY TRIGGER
-- 导出文件的路径
OUTPUT_DIR  /tmp

三、使用ora2pg导出SQL脚本

-- 配置好ora2pg.conf后,执行以下语句导出脚本
$ ora2pg -c ora2pg.conf

对view，trigger，package，sequence，function，procedure，type，materialized view 的测试结果如下表：

对象	ora2pg是否支持
view	是
trigger	是，某些情况下需要手工修改脚本
package	是，某些情况下需要手工修改脚本
sequence	是
function	是
procedure	是，某些情况下需要手工修改脚本
type	是，某些情况下需要手工修改脚本
materialized view	是，某些情况下需要手工修改脚本

具体的脚本转换结果如下：

1. View

-- 原oracle脚本

CREATE OR REPLACE VIEW manager_info AS
    SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,
           p.projno
        FROM   Emp_tab e, Dept_tab d, Project_tab p
        WHERE  e.empno =  d.mgr_no
        AND    d.deptno = p.resp_dept;

-- 转换后脚本

CREATE OR REPLACE VIEW manager_info (ename, empno, dept_type, deptno, prj_level, projno) AS SELECT  e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,
           p.projno
         FROM   Emp_tab e, Dept_tab d, Project_tab p  
        WHERE  e.empno =  d.mgr_no  
        AND    d.deptno = p.resp_dept;

转换后的脚本正常执行

2. Trigger

-- 原oracle脚本

CREATE OR REPLACE TRIGGER manager_info_insert  
INSTEAD OF INSERT ON manager_info  
REFERENCING NEW AS n                 -- new manager information  
FOR EACH ROW  
DECLARE  
   rowcnt number;  
BEGIN  
   SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno;  
   IF rowcnt = 0  THEN  
       INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename);  
   ELSE  
      UPDATE Emp_tab SET Emp_tab.ename = :n.ename  
         WHERE Emp_tab.empno = :n.empno;  
   END IF;  
   SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno;  
   IF rowcnt = 0 THEN  
      INSERT INTO Dept_tab (deptno, dept_type)   
         VALUES(:n.deptno, :n.dept_type);  
   ELSE  
      UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type  
         WHERE Dept_tab.deptno = :n.deptno;  
   END IF;  
   SELECT COUNT(*) INTO rowcnt FROM Project_tab   
      WHERE Project_tab.projno = :n.projno;  
   IF rowcnt = 0 THEN  
      INSERT INTO Project_tab (projno, prj_level)   
         VALUES(:n.projno, :n.prj_level);  
   ELSE  
      UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level  
         WHERE Project_tab.projno = :n.projno;  
   END IF;  
END;  
/

-- 转换后脚本

DROP TRIGGER IF EXISTS manager_info_insert ON manager_info CASCADE;  
CREATE OR REPLACE FUNCTION trigger_fct_manager_info_insert() RETURNS trigger AS $BODY$  
DECLARE  
   rowcnt bigint;  
BEGIN  
   SELECT COUNT(*) INTO STRICT rowcnt FROM Emp_tab WHERE empno = n.empno;  
   IF rowcnt = 0  THEN  
       INSERT INTO Emp_tab(empno,ename) VALUES (n.empno, n.ename);  
   ELSE  
      UPDATE Emp_tab SET Emp_tab.ename = n.ename  
         WHERE Emp_tab.empno = n.empno;  
   END IF;  
   SELECT COUNT(*) INTO STRICT rowcnt FROM Dept_tab WHERE deptno = n.deptno;  
   IF rowcnt = 0 THEN  
      INSERT INTO Dept_tab(deptno, dept_type)  
         VALUES (n.deptno, n.dept_type);  
   ELSE  
      UPDATE Dept_tab SET Dept_tab.dept_type = n.dept_type  
         WHERE Dept_tab.deptno = n.deptno;  
   END IF;  
   SELECT COUNT(*) INTO STRICT rowcnt FROM Project_tab  
      WHERE Project_tab.projno = n.projno;  
   IF rowcnt = 0 THEN  
      INSERT INTO Project_tab(projno, prj_level)  
         VALUES (n.projno, n.prj_level);  
   ELSE  
      UPDATE Project_tab SET Project_tab.prj_level = n.prj_level  
         WHERE Project_tab.projno = n.projno;  
   END IF;  
RETURN NEW;  
END  
$BODY$  
 LANGUAGE 'plpgsql';  
  
CREATE TRIGGER manager_info_insert  
    INSTEAD OF INSERT ON manager_info  FOR EACH ROW  
    EXECUTE PROCEDURE trigger_fct_manager_info_insert();

转换后的脚本正常执行，但少了某些参数，如：REFERENCING NEW AS n

-- 原oracle脚本

CREATE OR REPLACE TRIGGER Print_salary_changes  
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab    
  FOR EACH ROW  
WHEN (new.Empno > 0)  
DECLARE  
    sal_diff number;  
BEGIN  
    sal_diff  := :new.sal  - :old.sal;  
    dbms_output.put('Old salary: ' || :old.sal);  
    dbms_output.put('  New salary: ' || :new.sal);  
    dbms_output.put_line('  Difference ' || sal_diff);  
END;  
/

-- 转换后脚本

DROP TRIGGER IF EXISTS print_salary_changes ON emp_tab CASCADE;  
CREATE OR REPLACE FUNCTION trigger_fct_print_salary_changes() RETURNS trigger AS $BODY$  
DECLARE  
    sal_diff bigint;  
BEGIN  
    sal_diff  := NEW.sal  - OLD.sal;  
    RAISE NOTICE 'Old salary: %', OLD.sal;  
    RAISE NOTICE '  New salary: %', NEW.sal;  
    RAISE NOTICE '  Difference %', sal_diff;  
IF TG_OP = 'DELETE' THEN  
    RETURN OLD;  
ELSE  
    RETURN NEW;  
END IF;  
  
END  
$BODY$  
 LANGUAGE 'plpgsql';  
  
CREATE TRIGGER "print_salary_changes"  
  before delete or insert or update on emp_tab  
  for each row  
    WHEN (NEW.Empno > 0) -- 此处出现语法错误，before delete不允许使用NEW.  
    EXECUTE PROCEDURE trigger_fct_print_salary_changes();

转换后的脚本 WHEN (NEW.Empno > 0) 出现语法错误

3. Package

-- 原oracle脚本

--创建包头（function类型）  
create or replace package package_demo is  
 function Getage(birthst varchar,birthend varchar) return integer;  
 function Getsalary(VFpsncode varchar) return number;  
end package_demo;
/

--创建包体
create or replace package body package_demo is
  function Getage(birthst varchar,birthend varchar) return integer
  is
    V_birth integer;
    ToDateEnd Date;
    Toyear number(4);
    Tomonth number(4);
    Fromyear number(4);
    Frommonth number(4);
  begin
    if (birthend='') or (birthend is null) then 
      select sysdate into ToDateEnd from dual;
    end if;
    Toyear := to_number(to_char(ToDateEnd,'YYYY'));
    Tomonth := to_number(to_char(ToDateEnd,'MM'));
    Fromyear := to_number(substr(birthst,1,4));  
    Frommonth := to_number(substr(birthst,6,2));
    if Tomonth-Frommonth>0 then
      V_birth:=Toyear-fromyear;
    else
      V_birth:=Toyear-fromyear-1;
    end if;
    return(V_birth);
  end Getage;

  function getSalary(VFpsncode varchar) return number
  is
    V_psnSalary number(8,2);
  begin
    Select FpsnSalary into V_psnSalary from t_normal_package1 where Fpsncode = VFpsncode;
    return(V_psnSalary);
  end getSalary; 
end package_demo;
/

-- 转换后脚本

DROP SCHEMA IF EXISTS package_demo CASCADE;
CREATE SCHEMA package_demo;

CREATE OR REPLACE FUNCTION package_demo.getage (birthst text,birthend text) RETURNS numeric AS $body$
DECLARE
    V_birth numeric;
    ToDateEnd timestamp;
    Toyear smallint;
    Tomonth smallint;
    Fromyear smallint;
    Frommonth smallint;
BEGIN
    if (birthend='') or (birthend is null) then
      select clock_timestamp() into STRICT ToDateEnd;
    end if;
    Toyear := (to_char(ToDateEnd,'YYYY'))::numeric;
    Tomonth := (to_char(ToDateEnd,'MM'))::numeric;
    Fromyear := (substr(birthst,1,4))::numeric;
    Frommonth := (substr(birthst,6,2))::numeric;
    if Tomonth-Frommonth>0 then
      V_birth:=Toyear-fromyear;
    else
      V_birth:=Toyear-fromyear-1;
    end if;
    return(V_birth);
  END;

$body$
LANGUAGE PLPGSQL
 STABLE;
-- REVOKE ALL ON FUNCTION package_demo.getage (birthst text,birthend text) FROM PUBLIC;

CREATE OR REPLACE FUNCTION package_demo.getsalary (VFpsncode text) RETURNS bigint AS $body$
DECLARE
    V_psnSalary double precision;
BEGIN
    Select FpsnSalary into STRICT V_psnSalary from t_normal_package1 where Fpsncode = VFpsncode;
    return(V_psnSalary);
  END;

$body$
LANGUAGE PLPGSQL;

转换后的脚本正常执行

-- 原oracle脚本

--建包头（procedure类型）
create or replace
  package sdept_or_grade as
   procedure print_sdept(pID char);
   procedure print_grade(pID char);
   end;
   /
--建包体
create or replace
package body sdept_or_grade 
as
procedure print_sdept(pID char) as
    psdept t_normal_package.sdept%type;
begin
     select sdept into psdept
    from t_normal_package
    where ID=pID;
    dbms_output.put_line(psdept);
    exception
    when no_data_found then
        dbms_output.put_line('Invalid t_normal_package number');
end;
procedure print_grade(pID char) as
    pgrade t_normal_package_fk.grade%type;
cursor printgrade is select grade into pgrade
    from t_normal_package_fk
    where ID=pID;

begin
    open printgrade;
   loop
    fetch printgrade into pgrade;
        dbms_output.put_line(pgrade);
exit when printgrade%notfound;
end loop;
close printgrade;
exception
    when no_data_found then
        dbms_output.put_line('Invalid t_normal_package number');
end;
end;
/

-- 转换后脚本

DROP SCHEMA IF EXISTS sdept_or_grade CASCADE;
CREATE SCHEMA sdept_or_grade;

CREATE OR REPLACE FUNCTION sdept_or_grade.print_sdept (pID char) RETURNS VOID AS $body$
DECLARE

    psdept t_normal_package.sdept%type;

BEGIN
     select sdept into STRICT psdept
    from t_normal_package
    where ID=pID;
    RAISE NOTICE '%', psdept;
    exception
    when no_data_found then
        RAISE NOTICE 'Invalid t_normal_package number';
end;

$body$
LANGUAGE PLPGSQL
 STABLE;
-- REVOKE ALL ON FUNCTION sdept_or_grade.print_sdept (pID char) FROM PUBLIC;

CREATE OR REPLACE FUNCTION sdept_or_grade.print_grade (pID char) RETURNS VOID AS $body$
DECLARE

    pgrade t_normal_package_fk.grade%type;
printgrade CURSOR FOR SELECT grade into STRICT pgrade -- 此处关联了上条语句定义的pgrade，执行出错
    from t_normal_package_fk
    where ID=pID;


BEGIN
    open printgrade;
   loop
    fetch printgrade into pgrade;
        RAISE NOTICE '%', pgrade;
EXIT WHEN NOT FOUND; /* apply on printgrade */
end loop;
close printgrade;
exception
    when no_data_found then
        RAISE NOTICE 'Invalid t_normal_package number';
end;

$body$
LANGUAGE PLPGSQL
 STABLE;

转换后的脚本出现语法错误

4. Sequence

-- 原oracle脚本

create sequence my_seq   --创建序列名：my_seq
start with 1  --从1开始
increment by 1  --每次增长1
maxvalue 999999  --nomaxvalue(不设置最大值) ---最大值  //有限制的序列，无限制的序列设置时的相关参数：maxvalue，minvalue
minvalue 1  --最小值
cycle  --nocycle   一直累加，不循环   ；cycle 表示循环
nocache   ---缓存  //cache 10 
/

-- 转换后脚本

CREATE SEQUENCE my_seq INCREMENT 1 MINVALUE 1 MAXVALUE 999999 START 1 CYCLE;

转换后的脚本正常执行

5. Function

-- 原oracle脚本

create or replace function get_empname(v_id in number) return varchar2 as  
  v_name varchar2(50);  
begin  
  select NAME into v_name from T_NORMAL2 where id = v_id;  
   return v_name;  
exception  
  when no_data_found then  
    raise_application_error(-20001, '你输入的ID无效！');  
end get_empname;  
/

-- 转换后脚本

CREATE OR REPLACE FUNCTION get_empname (v_id bigint) RETURNS varchar AS $body$
DECLARE

  v_name varchar(50);

BEGIN
  select NAME into STRICT v_name from T_NORMAL2 where id = v_id;
   return v_name;
exception
  when no_data_found then
    RAISE EXCEPTION '%', '????????????ID?????????' USING ERRCODE = '45001'; -- 中文乱码，ERRCODE也有改变
end;
$body$
LANGUAGE PLPGSQL
 STABLE;

转换后的脚本正常执行，但出现中文乱码，ERROR CODE也有改变

6. Procedure

-- 原oracle脚本

create or replace procedure get_name(v_id in number,v_username out varchar2)
as
begin
  select NAME into v_username from T_PRIVACY_ALL where id = v_id; --变量赋值 
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end get_name;
/

-- 转换后脚本

CREATE OR REPLACE FUNCTION get_name (v_id bigint,v_username out text) AS $body$
BEGIN
  select NAME into STRICT v_username from T_PRIVACY_ALL where id = v_id; --????????????
exception
when no_data_found then
RAISE EXCEPTION '%', 'ID?????????!' USING ERRCODE = '45001'; -- 中文乱码，ERRCODE也有改变
end;
$body$
LANGUAGE PLPGSQL;

转换后的脚本正常执行，但出现中文乱码，ERROR CODE也有改变

-- 原oracle脚本

drop procedure proc_test;

create or replace procedure proc_test
is
testvalue varchar2(40);
begin
select NAME into testvalue from T_PRIVACY_ALL where id =1;
dbms_output.put_line(testvalue);
end proc_test;
/

-- 转换后脚本

CREATE OR REPLACE FUNCTION proc_test () RETURNS VOID AS $body$
DECLARE
testvalue varchar(40);
BEGIN
select NAME into STRICT testvalue from T_PRIVACY_ALL where id =1;
RAISE NOTICE '%', testvalue;
end;
$body$
LANGUAGE PLPGSQL;

转换后的脚本正常执行

7. Type

-- 原oracle脚本

CREATE TYPE ddl_type_demo AS OBJECT
    ( customer_id        NUMBER(6)
    , cust_first_name    VARCHAR2(20)
    , cust_last_name     VARCHAR2(20)
    , nls_language       VARCHAR2(3)
    , nls_territory      VARCHAR2(30)
    , credit_limit       NUMBER(9,2)
    , cust_email         VARCHAR2(30)
    ) ;
/

-- 转换后脚本

CREATE TYPE ddl_type_demo AS (
customer_id        numeric
    , cust_first_name    varchar(20)
    , cust_last_name     varchar(20)
    , nls_language       varchar(3)
    , nls_territory      varchar(30)
    , credit_limit       double precision
    , cust_email         varchar(30)
);

转换后脚本正常执行

-- 原oracle脚本

-- 含Type body
CREATE OR REPLACE TYPE employee_t AS OBJECT(
   empid RAW(16),
   ename CHAR(31),
   dept REF department_t,
      STATIC function construct_emp
      (name VARCHAR2, dept REF department_t)
      RETURN employee_t
);
/
CREATE OR REPLACE TYPE BODY employee_t IS
   STATIC FUNCTION construct_emp
   (name varchar2, dept REF department_t)
   RETURN employee_t IS
      BEGIN
         return employee_t(SYS_GUID(),name,dept);
      END;
END;
/

-- 转换后脚本

-- Unsupported, please edit to match PostgreSQL syntax
CREATE OR REPLACE TYPE employee_t AS OBJECT(
   empid RAW(16),
   ename CHAR(31),
   dept REF department_t,
      STATIC function construct_emp
      (name VARCHAR2, dept REF department_t)
      RETURN employee_t
);TYPE BODY employee_t IS
   STATIC FUNCTION construct_emp
   (name varchar2, dept REF department_t)
   RETURN employee_t IS
      BEGIN
         return employee_t(SYS_GUID(),name,dept);
      END;
END;

转换后的脚本显示语法不支持

8. Materialized View

-- 原oracle脚本

create materialized view mv_object_count as
select ID,count(*) from T_NORMAL1
group by ID;

-- 转换后脚本

CREATE MATERIALIZED VIEW mv_object_count AS
select ID,count(*) FROM T_NORMAL1
group by ID;
CREATE INDEX "i_snap$_mv_object_count" ON mv_object_count (sys_op_map_nonnull(id));

转换后的脚本多了一条创建索引的语句，且该语句有语法错误

ora2pg使用

一、环境准备

二、配置参数

三、使用ora2pg导出SQL脚本

1. View

2. Trigger

3. Package

4. Sequence

5. Function

6. Procedure

7. Type

8. Materialized View

推荐阅读更多精彩内容