把包从一个数据库同步到另一个数据库

最新推荐文章于 2024-08-01 09:25:59 发布

anji6350

最新推荐文章于 2024-08-01 09:25:59 发布

阅读量129

点赞数

文章标签：数据库网络

原文链接：http://www.cnblogs.com/notfresh/p/5957414.html

版权

源库

表

create table CUX_PKG_COPY

(

copy_id NUMBER ,

pkg_name VARCHAR2 ( 30 ),

copy_date DATE ,

type VARCHAR2 ( 30 ),

pkg_txt CLOB

);

create sequence CUX_PKG_COPY_S start with 1;

方法

create or replace procedure copy_pkg(p_pkg_name varchar2 )

/*************************************************

-- Author :

-- Created : 2016-10-04 12:15:37

-- Project :

-- Purpose : 同步一个包

-- 情景 :

-- Parameters: parameter1 => 输入,

parameter2 => 输入,

parameter3 => 输入,

**************************************************/

v_txt clob ;

v_pkg_name varchar2 ( 30 ) := p_pkg_name;

v_count number ;

-- set serveroutput on;

v_id number ;

begin

delete from cux_pkg_copy pc where pc.pkg_name=p_pkg_name;

v_id:=cux_pkg_copy_s.nextval;

v_count := 1 ;

for cur in ( select us.TEXT

from us

where us.TYPE = 'PACKAGE'

and us.name = upper(v_pkg_name)

order by us.line asc ) loop

if v_count = 1 then

v_txt := v_txt || ' create or replace ' || cur.text;

else

v_txt := v_txt || cur.text;

end if ;

v_count := v_count + 1 ;

end loop ;

--v_txt:=v_txt||'/'||chr(13);

insert into cux_pkg_copy

(copy_id, pkg_name, copy_date, type , pkg_txt)

values

(cux_pkg_copy_s.nextval, p_pkg_name, sysdate , 'PACKAGE' , v_txt);

v_txt := '' ;

v_count := 1 ;

for cur in ( select us.TEXT

from us

where us.TYPE = 'PACKAGE BODY'

and us.name = upper(v_pkg_name)

order by us.line asc ) loop

if v_count = 1 then

v_txt := v_txt || ' create or replace ' || cur.text;

else

v_txt := v_txt || cur.text;

end if ;

v_count := v_count + 1 ;

end loop ;

--v_txt:=v_txt||'/';

insert into cux_pkg_copy

(copy_id, pkg_name, copy_date, type , pkg_txt)

values

(cux_pkg_copy_s.nextval, p_pkg_name, sysdate , 'PACKAGE BODY' , v_txt);

--p_copy_id:=v_id;

end ;

目标库

create database link hec_test2uat2

connect to hecuat identified by hecuat

using '(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST= 192.168.203.25)(PORT=1521))

(CONNECT_DATA= (SERVICE_NAME=HECPROD)

(INSTANCE_NAME=HECPROD))

)' ;

create materialized view CUX_PKG_COPY_MV refresh complete on demand as select * from cux_pkg_copy@hec_test2uat2;

目标库

create or replace procedure sync_pkg(p_pkg varchar2 ) is

v1 number ;

v_clob clob ;

begin

copy_pkg@hec_test2uat2(p_pkg);

dbms_mview.refresh(list => 'CUX_PKG_COPY_MV' );

for cur in ( select * from cux_pkg_copy_mv pc where pc.pkg_name = p_pkg) loop

select mv.pkg_txt into v_clob from cux_pkg_copy_mv mv where mv.copy_id=cur.copy_id;

execute immediate v_clob;

end loop ;

end ;