使用DTS迁移Oracle到DM

1.从Oracle迁移至DM

1.1 创建Oracle测试环境

--创建用户
create user DM identified by Oracle;
grant connect,resource to DM;


--创建表
SQL> create table emp as select * from scott.emp;

Table created.

SQL>  create table dept as select * from scott.dept;

Table created.

--创建带有lob字段的表
create table lob_tab(id int,i_txt cl![1657773956261.png](https://oss-emcsprod-public.modb.pro/image/editor/20220715-82091139-8a97-4684-9c0b-ceefc018d8d5.png)ob);

--测试数据
declare 
 begin
      for i in 1..100000 loop
        insert into lob_tab values(i,lpad('aaa',i));
      end loop;
 end;
 /
 
--创建视图

create view emp_dept_view as select a.ename,a.sal,b.dname from emp a,dept b
where a.deptno=b.deptno;

--创建触发器
create or replace trigger emp_trigger
before delete on emp 
for each row
 begin
      dbms_output.put_line('hello world');
 end;
 /

--创建触发器
CREATE OR REPLACE TRIGGER t
  BEFORE
    INSERT OR
    UPDATE OF sal, deptno OR
    DELETE
  ON emp
BEGIN
  CASE
    WHEN INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('Inserting');
    WHEN UPDATING('sal') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
    WHEN UPDATING('deptno') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
  END CASE;
END;


--创建存储过程
create or replace procedure pro_emp
as
i number; 
 begin
 select count(1) into i from emp where empno=7788;
 if i=0 then
    dbms_output.put_line('无此员工');
 else
    dbms_output.put_line('有此员工');
 end if;
 
 end;
 /
复制

2.迁移工具配置

​ 迁移工具目录$DM_HOME/tool

​ ./dts启动

看到procedure创建失败，原因为Oracle该procedure本就是失效的。

3.数据验证

Oracle端:

DM端:

验证lob表

Oracle端:

dm端:

3.附加测试

写了一个复杂的存储过程看看是否正常迁移

/
* 将表DM_TEST_INFO DM_NAME字段按照逗号分隔并展示
*
/
CREATE OR REPLACE PROCEDURE SP_STR(V_OUT OUT SYS_REFCURSOR)
AS
CURSOR C1 IS SELECT * FROM DM_TEST_INFO;
TYPE C3 IS TABLE OF DM_TEST_INFO%ROWTYPE;
C4 C3;
V_START NUMBER :=1;
V_LOCATE NUMBER;
V_STR_BEFORE VARCHAR2(4000);
V_STR VARCHAR2(100);
BEGIN
  OPEN C1;
  LOOP
  EXIT WHEN C1%NOTFOUND;
  FETCH C1 BULK COLLECT INTO C4;
   FOR I IN 1..C4.COUNT LOOP
      V_STR:=C4(I).dm_name;
      WHILE INSTR(V_STR,',')>0
      LOOP     
      V_LOCATE :=INSTR(V_STR,',');     
      V_STR_BEFORE :=SUBSTR(V_STR,V_START,V_LOCATE-V_START);     
      INSERT INTO DM_TEST_INFO_temp(id,name,dm_name) VALUES(C4(I).id,C4(I).name,V_STR_BEFORE);     
      V_STR :=SUBSTR(V_STR,V_LOCATE+1);     
      END LOOP;     
      INSERT INTO DM_TEST_INFO_temp(id,name,dm_name) VALUES(C4(I).id,C4(I).name,V_STR);  
      commit;           
   END LOOP;
   COMMIT;
END LOOP;
CLOSE C1;
  OPEN V_OUT FOR SELECT id,name,dm_name FROM DM_TEST_INFO_temp;
END;
/

--构建测试数据
insert into DM_TEST_INFO values(1,'dm01','DMDSC,DMMPP,DMDW');
insert into DM_TEST_INFO values(1,'dm01','DMDTS,DMHS,DWRWC');

--调用
DECLARE
C1 SYS_REFCURSOR;
    DATA_DT_1       DM_TEST_INFO_TEMP.ID%TYPE;
    INVESTOR_ID_1   DM_TEST_INFO_TEMP.NAME%TYPE;
    PROPERTY_NAME_1 DM_TEST_INFO_TEMP.DM_NAME%TYPE;
BEGIN
       SP_STR(C1);
       LOOP
       BEGIN
       EXIT WHEN C1%NOTFOUND;
       FETCH C1 INTO DATA_DT_1,INVESTOR_ID_1,PROPERTY_NAME_1;
       DBMS_OUTPUT.PUT_LINE(DATA_DT_1||','||INVESTOR_ID_1||','||PROPERTY_NAME_1);
       END;
       END LOOP;
       EXCEPTION
       WHEN OTHERS THEN
       DELETE FROM  DM_TEST_INFO_TEMP;
	   commit;
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

--结果
SQL> DECLARE
C1 SYS_REFCURSOR;
    DATA_DT_1       DM_TEST_INFO_TEMP.ID%TYPE;
    INVESTOR_ID_1   DM_TEST_INFO_TEMP.NAME%TYPE;
    PROPERTY_NAME_1 DM_TEST_INFO_TEMP.DM_NAME%TYPE;
BEGIN
       SP_STR(C1);
       LOOP
       BEGIN
       EXIT WHEN C1%NOTFOUND;
       FETCH C1 INTO DATA_DT_1,INVESTOR_ID_1,PROPERTY_NAME_1;
       DBMS_OUTPUT.PUT_LINE(DATA_DT_1||','||INVESTOR_ID_1||','||PROPERTY_NAME_1);
       END;
       END LOOP;
       EXCEPTION
       WHEN OTHERS THEN
       DELETE FROM  DM_TEST_INFO_TEMP;
           commit;
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21
1,dm01,DMHS
1,dm01,DWRWC
1,dm01,DMDSC
1,dm01,DMMPP
1,dm01,DMDW
1,dm01,DMDTS
1,dm01,DMDTS

PL/SQL procedure successfully completed
复制

利用DTS开始迁移

验证是否成功:

DM很好的兼容了Oracle存储过程语法

达梦技术社区:https://eco.dameng.com