--在where子句中添加条件,选择需要替换的过程、函数、包等 cursor cur_objs is select distinct name from user_source where text like '%' ||v_replace || '%' ;
cursor cur_source is select Text,type from user_source where Name=v_ObjName ORDER BY type,line asc; begin v_replace:=''; open cur_objs; loop fetch cur_objs into v_ObjName; exit when cur_objs%notfound; --dbms_output.put_line('objname=' || v_objname || ' objtype=' || v_objtype);
v_objType2:=null; open cur_source; loop fetch cur_source into v_text,v_ObjType; exit when cur_source%notfound;
v_text:=replace(v_text,'TABLE','TABLE'); --在这里进行替换 if v_objType2 is null then v_objtype2:=v_objType; v_sql:='CREATE OR REPLACE ' || v_text; else if v_objtype2 = v_objtype then v_sql:=v_sql || v_text; else execute immediate v_sql; v_objtype2:=v_objtype; v_sql:='CREATE OR REPLACE ' || v_text; end if; end if; end loop; close cur_source; execute immediate v_sql; end loop; close cur_objs; end;
2,使用UltraEdit替换
3,直接执行这个修改过的文件(sql),编译通过的话,库中的内容就都替换过了.
where UPPER(text) like '%CONN%'↑此方法可搜索到conn字符、再自己替换。
--执行后注意,由于没有考虑引用关系,可能某些对象需要手工重新编译
declare
v_ObjName varchar2(30);
v_ObjType varchar2(30);
v_ObjType2 varchar2(30);
v_text varchar2(4000);
v_sql varchar2(32767);
v_replace varchar2(4000); --需要替换的内容
--在where子句中添加条件,选择需要替换的过程、函数、包等
cursor cur_objs is
select distinct name from user_source
where text like '%' ||v_replace || '%' ;
cursor cur_source is
select Text,type from user_source
where Name=v_ObjName
ORDER BY type,line asc;
begin
v_replace:='';
open cur_objs;
loop
fetch cur_objs into v_ObjName;
exit when cur_objs%notfound;
--dbms_output.put_line('objname=' || v_objname || ' objtype=' || v_objtype);
v_objType2:=null;
open cur_source;
loop
fetch cur_source into v_text,v_ObjType;
exit when cur_source%notfound;
v_text:=replace(v_text,'TABLE','TABLE'); --在这里进行替换
if v_objType2 is null then
v_objtype2:=v_objType;
v_sql:='CREATE OR REPLACE ' || v_text;
else
if v_objtype2 = v_objtype then
v_sql:=v_sql || v_text;
else
execute immediate v_sql;
v_objtype2:=v_objtype;
v_sql:='CREATE OR REPLACE ' || v_text;
end if;
end if;
end loop;
close cur_source;
execute immediate v_sql;
end loop;
close cur_objs;
end;