create or replace package SCHEMA_PKG
AUTHID CURRENT_USER 
is  
  procedure Packages(location in varchar2, filename in varchar2);
end;
/

grant execute on SCHEMA_PKG to public;

create or replace package body SCHEMA_PKG
is
  max_text_len constant number :=  32767 ;
  NL_LF        constant varchar2( 2 ) := chr( 10 );

  spool_file   utl_file.file_type;

  procedure FileOpen(location in varchar2, filename in varchar2)
  is
  begin
    spool_file := utl_file.fopen(location, filename, 'w', max_text_len);
  exception
    when utl_file.INVALID_PATH then 
      RAISE_APPLICATION_ERROR(- 20001 , 'File location or name was invalid');
    when utl_file.INVALID_MODE then 
      RAISE_APPLICATION_ERROR(- 20001 , 'The open_mode string was invalid');
    when utl_file.INVALID_OPERATION then 
      RAISE_APPLICATION_ERROR(- 20001 , 'File could not be opened as requested');
    when utl_file.INVALID_MAXLINESIZE then 
      RAISE_APPLICATION_ERROR(- 20001 , 'Specified max_linesize is too large or too small');
  end;

  procedure FileClose
  is
  begin
    utl_file.fclose(spool_file);
  end;

  procedure FileWriteCLOB(clob_text in clob)
  is
    char_text varchar2( 32767 );
    clob_len  number;
    amount    number;
    offset    number :=  1 ;
  begin
    clob_len := dbms_lob.getlength(clob_text);
    while clob_len >  0 
    loop
      if clob_len > max_text_len then
        amount := max_text_len;
      else
        amount := clob_len;
      end if;
      char_text := dbms_lob.substr(clob_text, amount, offset);
      utl_file.put(spool_file, char_text);
      utl_file.fflush(spool_file);
      offset := offset + amount;
      clob_len := clob_len - amount;
    end loop;
  end;

  procedure Packages_DDL(is_body in boolean)
  is
    metadata_handle number;
    transfrm_handle number;
    clob_text       clob;
  begin
    metadata_handle := dbms_metadata.open('PACKAGE');

    transfrm_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');
    dbms_metadata.set_transform_param(transfrm_handle, 'BODY', is_body);
    dbms_metadata.set_transform_param(transfrm_handle, 'SPECIFICATION', not is_body);
    dbms_metadata.set_transform_param(transfrm_handle, 'SQLTERMINATOR', true);

    loop
      clob_text := dbms_metadata.fetch_clob(metadata_handle);
      exit when clob_text is null;
      FileWriteCLOB(clob_text);
    end loop;

    dbms_metadata.close(metadata_handle);
  end;

  procedure Packages(location in varchar2, filename in varchar2)
  is
  begin
    FileOpen(location, filename);

    Packages_DDL(is_body => false);
    Packages_DDL(is_body => true);

    FileClose;   
  exception
    when others then dbms_output.put_line(SQLERRM);
  end;
end;
/

select 
  case line 
    when  1  then 'CREATE OR REPLACE ' || text
    else text
  end
from 
  user_source
order by type, name, line;