ora2pg使用

一、環(huán)境準(zhǔn)備

  • centos 6.5
  • oracle 11.2.0.4
  • perl v5.10.1
  • DBD::Oracle
  • DBI
  • DBD::Pg -- 如果需要將結(jié)果直接導(dǎo)入到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
-- 生效環(huán)境變量，.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

二、配置參數(shù)

ora2pg的配置文件ora2pg.conf，可以配置相關(guān)oracle連接信息，需要導(dǎo)出的對(duì)象，目標(biāo)庫postgresql的連接配置等等。

-- 設(shè)置oracle連接參數(shù)
ORACLE_DSN  dbi:Oracle:host=192.168.112.22;sid=cndb;port=1521
ORACLE_USER system
ORACLE_PWD  root
-- 需要導(dǎo)出的schema
SCHEMA      DBUSER
-- 需要導(dǎo)出的對(duì)象，表結(jié)構(gòu)、數(shù)據(jù)、觸發(fā)器等等，詳見文件內(nèi)說明
TYPE    TABLE COPY TRIGGER
-- 導(dǎo)出文件的路徑
OUTPUT_DIR  /tmp

三、使用ora2pg導(dǎo)出SQL腳本

-- 配置好ora2pg.conf后,執(zhí)行以下語句導(dǎo)出腳本
$ ora2pg -c ora2pg.conf

對(duì)view，trigger，package，sequence，function，procedure，type，materialized view 的測(cè)試結(jié)果如下表：

對(duì)象 ora2pg是否支持
view 是
trigger 是，某些情況下需要手工修改腳本
package 是，某些情況下需要手工修改腳本
sequence 是
function 是
procedure 是，某些情況下需要手工修改腳本
type 是，某些情況下需要手工修改腳本
materialized view 是，某些情況下需要手工修改腳本

具體的腳本轉(zhuǎn)換結(jié)果如下 ：

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;

-- 轉(zhuǎn)換后腳本

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;

轉(zhuǎn)換后的腳本正常執(zhí)行

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;  
/

-- 轉(zhuǎn)換后腳本

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();

轉(zhuǎn)換后的腳本正常執(zhí)行，但少了某些參數(shù)，如：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;  
/

-- 轉(zhuǎn)換后腳本

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) -- 此處出現(xiàn)語法錯(cuò)誤，before delete不允許使用NEW.  
    EXECUTE PROCEDURE trigger_fct_print_salary_changes();

轉(zhuǎn)換后的腳本 WHEN (NEW.Empno > 0) 出現(xiàn)語法錯(cuò)誤

3. Package

-- 原oracle腳本

--創(chuàng)建包頭（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;
/

--創(chuàng)建包體
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;
/

-- 轉(zhuǎn)換后腳本

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;

轉(zhuǎn)換后的腳本正常執(zhí)行

-- 原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;
/

-- 轉(zhuǎn)換后腳本

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 -- 此處關(guān)聯(lián)了上條語句定義的pgrade，執(zhí)行出錯(cuò)
    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;

轉(zhuǎn)換后的腳本出現(xiàn)語法錯(cuò)誤

4. Sequence

-- 原oracle腳本

create sequence my_seq   --創(chuàng)建序列名：my_seq
start with 1  --從1開始
increment by 1  --每次增長(zhǎng)1
maxvalue 999999  --nomaxvalue(不設(shè)置最大值) ---最大值  //有限制的序列，無限制的序列設(shè)置時(shí)的相關(guān)參數(shù)：maxvalue，minvalue
minvalue 1  --最小值
cycle  --nocycle   一直累加，不循環(huán)   ；cycle 表示循環(huán)
nocache   ---緩存  //cache 10 
/

-- 轉(zhuǎn)換后腳本

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

轉(zhuǎn)換后的腳本正常執(zhí)行

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;  
/

-- 轉(zhuǎn)換后腳本

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;

轉(zhuǎn)換后的腳本正常執(zhí)行，但出現(xiàn)中文亂碼，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;
/

-- 轉(zhuǎn)換后腳本

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;

轉(zhuǎn)換后的腳本正常執(zhí)行，但出現(xiàn)中文亂碼，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;
/

-- 轉(zhuǎn)換后腳本

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;

轉(zhuǎn)換后的腳本正常執(zhí)行

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)
    ) ;
/

-- 轉(zhuǎn)換后腳本

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)
);

轉(zhuǎn)換后腳本正常執(zhí)行

-- 原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;
/

-- 轉(zhuǎn)換后腳本

-- 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;

轉(zhuǎn)換后的腳本顯示語法不支持

8. Materialized View

-- 原oracle腳本

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

-- 轉(zhuǎn)換后腳本

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));

轉(zhuǎn)換后的腳本多了一條創(chuàng)建索引的語句，且該語句有語法錯(cuò)誤

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容