Declare
dir_path varchar2(50) := '/u08';
ofile utl_file.file_type;
t_owner all_source.owner%type;
t_name all_source.name%TYPE;
t_type all_source.type%TYPE;
hdr    all_source.text%TYPE;
cursor c1 is
    select distinct owner,name,type 
      from all_source
     where owner not like 'SYS%';
cursor c2 is
    select length(text) max_len,text
      from all_source 
     where owner=t_owner and name=t_name and type=t_type;
hdr_done char := 'N';
begin
  ofile := utl_file.fopen(dir_path, 'X_create_all_procs.sql','W');
  utl_file.put_line(ofile, 'spool /u08/dev_proc.out');
  for i in c1 loop
    utl_file.put_line(ofile, 'prompt creating '||i.type||' ==> '||i.name);
    utl_file.put_line(ofile, 'Create or Replace ');
    t_owner := i.owner;
    t_name := i.name;
    t_type := i.type;
    for j in c2 loop
      utl_file.put_line(ofile, substr(j.text,1,j.max_len-1));
    end loop;
    utl_file.put_line(ofile, '/');
    utl_file.put_line(ofile, '');
    utl_file.put_line(ofile, '');
  end loop;
  utl_file.put_line(ofile, 'spool ');
  utl_file.put_line(ofile, 'spool off');
  utl_file.put_line(ofile, 'prompt Chequear /u08/dev_proc.out por posibles errores');
  utl_file.fclose(ofile);
end;
/
undefine user

