--==============================================================
-- migrate data define and body
--==============================================================
--**************************************************************migrate data begin**************************************
--open screen output
set serveroutput on;
--**********************************************package declare begin*********************************
create or replace package migration_package is
--declare procedures
--define cursor type
type sp_cursor is ref cursor;
--insert data into mapper table
procedure insert_auditmapper_data(mapperId in number, listenerName in varchar2, affectRows out number);
--insert data into the relation of mapper and listener table
procedure insert_xrefmapperlistener_data(mapperId in number, listenerId in number, affectRows out number);
--insert mapper id, Mapper Selector for GAL_PUBLIC_KEY_FIELD table
procedure update_publickeyfield_data(listenerId in number, mapperId in number, listenerName varchar2, u_pkf_affectRows out number, affectRows out number);
--migrate data
procedure migrate_data(isSuccessFlag out number);
end;
/
--**********************************************package declare end*********************************
--**********************************************package body begin*********************************
create or replace package body migration_package is
--insert data into mapper table
procedure insert_auditmapper_data(mapperId number, listenerName varchar2, affectRows out number) is
begin
--execute part
insert into GAL_AUDIT_MESSAGE_MAPPER (MAPPER_ID, MAPPER_NAME, MAPPER_DESCRIPTION, CREATED_BY, CREATION_DATE) values(mapperId, substr(listenerName||'_Mapper',1,255), substr('create a mapper for Listener: '||listenerName,1,255), 0, sysdate);
affectRows:= SQL%ROWCOUNT;
end;
--insert data into the relation of mapper and listener table
procedure insert_xrefmapperlistener_data(mapperId number, listenerId number, affectRows out number) is
begin
--execute part
insert into GAL_XREF_MAPPER_LISTENER (MAPPER_ID, LISTENER_ID, CREATED_BY, CREATION_DATE) values(mapperId, listenerId, 0, sysdate);
affectRows:= SQL%ROWCOUNT;
end;
--insert mapper id, Mapper Selector for GAL_PUBLIC_KEY_FIELD table
procedure update_publickeyfield_data(listenerId number, mapperId number, listenerName varchar2, u_pkf_affectRows out number, affectRows out number) is
v_publicKeyId gal_public_key_field.public_key_field_id%type;
begin
--execute part
--insert mapper id
update GAL_PUBLIC_KEY_FIELD set mapper_id=mapperId where listener_id=listenerId;
u_pkf_affectRows:= SQL%ROWCOUNT;
--get public key id
select PUBLIC_KEY_FIELD_ID_SEQ.NEXTVAL into v_publicKeyId from dual;
--insert Mapper Selector
insert into GAL_PUBLIC_KEY_FIELD (PUBLIC_KEY_FIELD_ID, MAPPER_ID, FIELD_NAME_DESCRIPTION, CONSTANT_VALUE, IS_XML_FIELD, FIELD_TYPE, CREATED_BY, CREATION_DATE, DEFAULT_VALUE)
values(v_publicKeyId, mapperId, substr('Mapper Selector for '||listenerName,1,255), substr('Mapper Selector for '||listenerName,1,255), 'N', 'Mapper Selector', 0, sysdate, 'N');
affectRows:= SQL%ROWCOUNT;
end;
--insert data base on GAL_LISTENER TABLE
procedure migrate_data(isSuccessFlag out number) is
--define cursor var
m_cursor sp_cursor;
--define v_listenerId var
v_listenerId gal_listener.listener_id%type;
--define v_listenerName var
v_listenerName gal_listener.listener_name%type;
--define v_mapperId var
v_mapperId gal_audit_message_mapper.mapper_id%type;
affectRows number;
u_pkf_affectRows number;
mapper_affectRows number;
xref_affectRows number;
u_pkf_amount number;
i_pkf_amount number;
v_listeners_amount number;
begin
--execute part
dbms_output.put_line('migration data begin...');
mapper_affectRows:=0;
xref_affectRows:=0;
u_pkf_amount:=0;
i_pkf_amount:=0;
--get the listener rows amount
select count(distinct listener_id) into v_listeners_amount from GAL_LISTENER;
dbms_output.put_line('the GAL_LISTENER table rows amount: '||v_listeners_amount);
open m_cursor for select distinct listener_id, listener_name from GAL_LISTENER;
loop
fetch m_cursor into v_listenerId, v_listenerName;
exit when m_cursor%notfound;
--get mapper id from sequence
select MAPPER_ID_SEQ.NEXTVAL into v_mapperId from dual;
--dbms_output.put_line('listener_name...'||v_listenerName);
--insert data into mapper table
affectRows:=0;
insert_auditmapper_data(v_mapperId, v_listenerName, affectRows);
--sum the affect rows
mapper_affectRows:=mapper_affectRows+affectRows;
affectRows:=0;
--insert data into the relation of mapper and listener table
insert_xrefmapperlistener_data(v_mapperId, v_listenerId, affectRows);
--sum the affect rows
xref_affectRows:=xref_affectRows+affectRows;
affectRows:=0;
u_pkf_affectRows:=0;
--insert mapper id for GAL_PUBLIC_KEY_FIELD table
update_publickeyfield_data(v_listenerId, v_mapperId, v_listenerName, u_pkf_affectRows, affectRows);
--sum the affect rows
u_pkf_amount:=u_pkf_amount+u_pkf_affectRows;
i_pkf_amount:=i_pkf_amount+affectRows;
end loop;
affectRows:=mapper_affectRows+xref_affectRows+u_pkf_amount+i_pkf_amount;
dbms_output.put_line('Below are the execute result: ');
dbms_output.put_line('insert GAL_AUDIT_MESSAGE_MAPPER table rows amount: '||mapper_affectRows);
dbms_output.put_line('insert GAL_XREF_MAPPER_LISTENER table rows amount: '||xref_affectRows);
dbms_output.put_line('update GAL_PUBLIC_KEY_FIELD table rows amount: '||u_pkf_amount);
dbms_output.put_line('insert GAL_PUBLIC_KEY_FIELD table rows amount: '||i_pkf_amount);
dbms_output.put_line('affect rows amount: '||affectRows);
dbms_output.put_line('migration data end.');
isSuccessFlag:=1;
--close cursor
close m_cursor;
--dbms_output.put_line('close cursor!');
Exception
when DUP_VAL_ON_INDEX then
isSuccessFlag:=-1;
rollback;
raise_application_error(-20001, 'An error was encountered: insert duplicated data!'||' And the detail error info: '||SQLERRM);
when others then
isSuccessFlag:=-1;
rollback;
raise_application_error(-20001, 'An error was encountered1 - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
end migration_package;
/
--***********************************************package body end************
--************************************migrate data end***********************
--==============================================================
-- migrate data and update database version
--==============================================================
declare
isSuccessFlag number;
begin
--call procedure migrate_data
migration_package.migrate_data(isSuccessFlag);
if isSuccessFlag=1 then
execute immediate 'alter table GAL_PUBLIC_KEY_FIELD drop COLUMN LISTENER_ID';
dbms_output.put_line('drop listener_id from gal_public_key_field table successfully!');
UPDATE GAL_SETTINGS SET SETTING_VALUE = '5.0.0' WHERE SETTING_NAME = 'DATABASE VERSION';
commit;
end if;
end;
/
--clean operation
drop package migration_package;
exit;
分享到:
相关推荐
本文主要结合之前一次oracle迁移达梦的项目,将碰到的问题以及一系列踩过的坑列举出来供大家参考,数据库版本是达梦7。(本文中涉及到的部分对象名已用sch1,tab1等方式替换) 1、整体情况 迁移过程中失败任务数低于5%...
从Oracle迁移到MySQL经典实战,省钱公司必备
oracle迁移到mysql问题记录
ORACLE数据库迁移方案,单机迁移到rac
Oracle迁移上云指南.pptx
oracle迁移postgres全流程,类型匹配、安装、迁移过程、函数兼容、数据校验等
Oracle到Mysql数据库迁移总结Oracle到Mysql数据库迁移总结Oracle到Mysql数据库迁移总结Oracle到Mysql数据库迁移总结
oracle迁移到PostgreSQL社区代码,已经下载,分享给大家
Oracle 10g->11g XTTS迁移方案 Oracle 9i至11g exp迁移 Oracle数据库三种迁移方案 Oracle DataGuard方式迁移数据库 数据库迁移升级最佳实践9i-10g-11g Oracle ASM扩容&数据迁移文档 06使用XTTS技术进行U2L跨平台数据...
oracle迁移(windows平台).mht
程序是C#写的,编译成64bit程序,只能跑在windows 64位平台,无需安装Oracle客户端,实现SQLServer2005以上数据 迁移数据到Oracle数据库 自动创建表及主键,自动迁移数据 使用方法: 1. 修改配置文件MigrationMSSQL2...
从Oracle迁移到MySQL的那些坑.pdf
1.2 oracle生产环境 1.3 Oracle SQL Developer配置 2 数据迁移 2.1 数据准备 2.1.1 生产数据备份 2.1.2 导入机还原备份文件 2.2 Oracle SQL Developer配置连接 2.2.1 连接本机mysql数据库 2.2.2 连接生产oracle...
windows环境下, db_name和instance_name都不相同的情况下的数据库迁移. 通过镜像文件实现.
oracle资料库资料迁移到MS-sql(sqlserver)工具,版本为6.0 如果连oracle资料库提示以下错误时, Unable to find specified provider. Error occurred while establishing connection to Oracle server. You might ...
linux下的ORACLE 数据库迁移,新人适用。
omwb oracle迁移工具 SQL 向oracle 迁移
oracle数据库迁移步骤,11g版支持数据泵,卸库速度大幅提高。
oracle迁移mysql说明 详细见文档,包括了各种常见错误
yugong 是阿里巴巴推出的去Oracle数据迁移同步工具(全量 增量,目标支持MySQL/DRDS)。2008年左右,阿里巴巴开始尝试MySQL的相关研究,并开发了基于MySQL分库分表技术的相关产品,Cobar/TDDL(目前为阿里云DRDS产品),...