批次過程獲取指令碼

declare
 v_text varchar2(4000);
 v_line number;
 v_max_line number;
 v_owner varchar2(30);
 v_name varchar2(30);
 v_type varchar2(12);
-- i_owner varchar2(30);
-- i_object varchar2(30);
 cursor c_xo is
  select distinct xo.owner,xo.name,xo.type from ALL_SOURCE xo 
   where --(xo.bbbz like '1%' or xo.bbbz like '2%') and
    xo.owner='&J1_G3_ZBQ' and xo.type in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION') ORDER BY TYPE;
 cursor c_as is
  select line,case when line=1 then 'create or replace '||text 
               else text 
                end text
  from all_source where owner=v_owner and name =v_name and type =v_type;  
 fhandle utl_file.file_type;
begin
--
 for cur_1 in c_xo loop
 v_owner :=cur_1.owner;
 v_name :=cur_1.name;
 v_type :=cur_1.type;
 fhandle := utl_file.fopen(v_owner||'_PROC_DIR','init-proc.sql', 'a'); 
  if v_owner='J1_G3_ZBQ' then
     --lvc_line :=  '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
     v_text :=  '@"01 dw\01 inf\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_LDM' then
     v_text :=  '@"01 dw\02 ldm\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_DW' then
     v_text :=  '@"01 dw\03 dw\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_DI' then
     v_text :=  '@"01 dw\05 di\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_CXTJ' then
     v_text :=  '@"03 xt\01 cxtj\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_DMT' then
     v_text :=  '@"03 xt\06 dmt\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_METAONE' then
     v_text :=  '@"03 xt\07 metaone\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  elsif v_owner='J1_LDCX' then
     v_text :=  '@"01 dw\08 ldcx\01 object\04 procedure\'||v_name||'_'||v_type||'.sql";';
  end if;
   --@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
     utl_file.put_line(fhandle ,v_text); 
     utl_file.fclose(fhandle);
 end loop;
--  
 for cur_0 in c_xo loop
 v_owner :=cur_0.owner;
 v_name :=cur_0.name;
 v_type :=cur_0.type;
 select max(line) into v_max_line from all_source where owner=v_owner and name =v_name and type =v_type;  
-- dbms_output.put_line(v_owner||','||v_name||','||v_type);
 fhandle := utl_file.fopen(v_owner||'_PROC_DIR',v_name||'_'||v_type||'.sql', 'w',32767);
 utl_file.put_line(fhandle ,'prompt'||' '||v_type||' '||v_owner||'.'||v_name||' created');
 utl_file.put_line(fhandle ,'set define off'); 
 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(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.put_line(fhandle ,'set define on'); 
 utl_file.fclose(fhandle);
 end loop;
end;
/