单个过程获取脚本
declare
v_text varchar2(4000);
v_line number;
v_min_line number;
v_max_line number;
-- i_owner varchar2(30);
-- i_object varchar2(30);
cursor c_as is
select line,case when line=1 then 'create or replace '||text
else text
end text
from all_source where owner='J1_G3_ZBQ' and name ='CP_TRANS_G3SJGS';
fhandle utl_file.file_type;
begin
select min(line),max(line) into v_min_line,v_max_line from all_source where owner='J1_G3_ZBQ' and name ='CP_TRANS_G3SJGS';
fhandle := utl_file.fopen('EXP_DIR','CP_TRANS_G3SJGS.sql', 'w',32767);
for cur in c_as loop
v_text :=cur.text;
v_line :=cur.line;
if v_line=v_max_line then
utl_file.put_line(fhandle ,replace(replace(v_text,chr(10),'/'),';',';'||chr(10)||'/'));
-- dbms_output.put_line(replace(v_text,chr(10),'/'));
else
utl_file.put_line(fhandle ,replace(v_text,chr(10),''));
-- dbms_output.put_line(replace(v_text,chr(10),''));
end if;
end loop;
utl_file.fclose(fhandle);
end;
/
v_text varchar2(4000);
v_line number;
v_min_line number;
v_max_line number;
-- i_owner varchar2(30);
-- i_object varchar2(30);
cursor c_as is
select line,case when line=1 then 'create or replace '||text
else text
end text
from all_source where owner='J1_G3_ZBQ' and name ='CP_TRANS_G3SJGS';
fhandle utl_file.file_type;
begin
select min(line),max(line) into v_min_line,v_max_line from all_source where owner='J1_G3_ZBQ' and name ='CP_TRANS_G3SJGS';
fhandle := utl_file.fopen('EXP_DIR','CP_TRANS_G3SJGS.sql', 'w',32767);
for cur in c_as loop
v_text :=cur.text;
v_line :=cur.line;
if v_line=v_max_line then
utl_file.put_line(fhandle ,replace(replace(v_text,chr(10),'/'),';',';'||chr(10)||'/'));
-- dbms_output.put_line(replace(v_text,chr(10),'/'));
else
utl_file.put_line(fhandle ,replace(v_text,chr(10),''));
-- dbms_output.put_line(replace(v_text,chr(10),''));
end if;
end loop;
utl_file.fclose(fhandle);
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29802484/viewspace-1767282/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
serveroutput size不够用
下一篇：
批量过程获取脚本
请登录后发表评论
登录
全部评论
