同步远端数据库的存储过程到本地数据库

同步远端数据库的存储过程到本地数据库
FW da2i(Jm$C0
$[Gl1D4[ TA2aG0Q：同步数据库的时候表和数据都可以用简单的prc很容易的进行同步，因为通过DB_Link很容易访问，但是对于存储过程、函数、trigger等却不好通过DB_Link访问，所以本过程将把远端数据库的过程、函数等同步到本地的数据库！
AA@b6d'w$}0ITPUB个人空间y&S2w7{/K m:J
学习对utl_file写文件的使用ITPUB个人空间 zck8~!Y|:~9R

M&K\}X2q3\LN x C0远程数据库（开发库）有个每天运行的job，它执行sp_etl_reg，sp_etl_lvy,sp_etl_ass这3个存储过程。每个存储过程代表不同的业务模块，每个过程中调用各自模块的许多存储过程来实现本模块的ETL， 本地数据库想把上边3个存储过程中涉及到的存储过程同步到本地
P&f)E \L0ITPUB个人空间(q;s)_XUD&}6m
实施步骤：
sD
];qP M|LG0一：在本地建立DB_LINKITPUB个人空间o5IKbNf#p
略
tLCN9`h+p?X0二：需要在init参数中增加utl_file_dir，而本地是以spfile登录的，所以
o;yq7mR%\01〉以sysdba登录本地，create pfile='D:oracleproduct10.2.0 empinit1.ora' from spfile;ITPUB个人空间,U!|/F;A7Zz6D)]h
2〉打开init1.ora在文件中加入utl_file_dir=D:oracleproduct10.2.0 emp
0i;Tf7`2Z2B:j03〉create spfile='D:oracleproduct10.2.0 empspfileorclzxt.ora' from pfile='D:oracleproduct10.2.0 empinit1.ora';ITPUB个人空间&l8QJ\0qJ
4〉更改D:oracleproduct10.2.0db_1databaseinitorcl.ora为ITPUB个人空间U?(H i@P$c9E$x:a
SPFILE='d:oracleproduct10.2.0db_1/dbs/SPFILEORCLZXT.ORA'ITPUB个人空间%K D:MAv2ujv6c
5〉拷贝D:oracleproduct10.2.0 empspfileorclzxt.ora到d:oracleproduct10.2.0db_1/dbs/SPFILEORCLZXT.ORAITPUB个人空间SX
P"oS5m t}!Tn
6〉重启数据库
r/n+O^+AT0三：建立存储过程save_procedure_to_file_ZXTITPUB个人空间)\2Zqm\r!{E
create or replace procedure save_procedure_to_file_ZXT isITPUB个人空间&];\!G m-ciq
procedname varchar2(32);ITPUB个人空间e7`q0Z5kI&c
i PLS_INTEGER := 0;
1E[IF!Jl!|Q0L_output utl_file.file_type;ITPUB个人空间r2A~ F"oKU g%@Y+q#f
file_dic varchar2(200) := 'D:oracleproduct10.2.0 emp'; --目录
M7l \,KWc0file_name varchar2(32) := 'pro_zxt.sql'; --生成的文件
^rI0W5Qt
a0cursor proc is --得到每个业务模块中用到的存储过程名称
$UAK8SR2PA0select x.referenced_name
[2N0r n X0U#k_0from user_dependencies@zgcx.com x
o&uO/[Ku;R0where x.type = 'PROCEDURE'
7]KvP'e[0and x.referenced_name like 'SP%'ITPUB个人空间AX!qjWF+T2S
~:j-v
and x.name in
2u#g3W$O\L0(upper('sp_etl_reg'), upper('sp_etl_lvy'), upper('sp_etl_ass'))
;V%d(zyVNP$^0order by x.name,
p,_myZ*n!B0x.referenced_name;
+t2w4Q1NHUSU0-- **********************************************************************ITPUB个人空间8E!_C$V zf
-- 模块名称 : save_procedure_to_file_ZXTITPUB个人空间(k8L'g;A!aWL3J1Oh
-- 模块编号 :
n"?)s-@!kN0-- 功能描述 : 同步存储过程
s2TH-Hw1c3l([0-- 被调用模块: 无ITPUB个人空间;[/gRUi)Q
-- 调用模块 : 无
;MxOoj8[1N0-- Revision : 1.0ITPUB个人空间}C~'sg zj
-- 创 建 人 : Z.X.T
} d S@Yw0-- 创建日期 : 2007-06-13
tHi l Ja8W9]0-- 修改日期 ：2007-06-13ITPUB个人空间6m*}sP1^Mv
-- **********************************************************************
5y?|!N4o l0
j o
` v:d3N0begin
y
r4{(a!Qw0e0--打开文件
JG2@`D:zMv[0L_output := utl_file.fopen(file_dic,ITPUB个人空间x G1m R:{DR3f+rh~
file_name,ITPUB个人空间-?q _/nB zQ
'a');
QC`
um0--循环得到每一个存储过程名称
%p7Mx ?0E,tk(C0open proc;ITPUB个人空间f5_z6I,_4PZC
loopITPUB个人空间MLOG)H,MI([
fetch proc
#\y4_|9v/t0into procedname;
J/D,|u%u5wW0exit when proc%notfound;
7?.@#g#X{ j0i := i + 1;
\ }9MPi
P2i0dbms_output.put_line('procedname' || i || '=' || procedname);
"SXYxtSd0ITPUB个人空间t`Ulccglu
--得到每一个存储过程的sourceITPUB个人空间"I|,j_!p
for j in (select decode(t.line,ITPUB个人空间m+Q-x%a:c&J:}X
1,ITPUB个人空间3kz%{oU!I?
'Create or replace ' || t.text,ITPUB个人空间p
M5]"|b
t.text) text
8p h8vl#~R5F0from user_source@zgcx.com tITPUB个人空间&BQPoH Kc)\B
where t.name = procednameITPUB个人空间"B y|[u E/W
ae
order by line)
@;Y$X6r4d*K4Ki0LOOPITPUB个人空间*MR+_(DpD
--写每一个存储过程到文件ITPUB个人空间)ESX@L.R
UTL_FILE.put_line(L_output,
o M^oY5b0j.text,ITPUB个人空间Bx-_/OhL)_l
false);ITPUB个人空间Gj3`\)}@(k:ag
END LOOP;
i;X
H*?wJea0--在每一个存储过程后增加'/'
b*meR5F9h#xZ9a,E0UTL_FILE.put_line(L_output,ITPUB个人空间W[I vS)g
'/',ITPUB个人空间*S;J7b6Pmv
false);ITPUB个人空间.yK v(V
k4z;M
end loop;
Qh u1kUAo0
{w%U0|\-}1M_7`0close proc;
`f#t1YJ-{S3LkZ0ITPUB个人空间r/q'~,RR
UTL_FILE.fclose(L_output);
{W
Xc7fC2B0end save_procedure_to_file_ZXT;
%?F)?c$g#Ih"KA0四：运行save_procedure_to_file_ZXTITPUB个人空间CQN2K/g'x5x+b
五：执行pro_zxt.sql