PL/SQL工具导出整个数据库的表的结构

create or replace package reverse_engineer as

  procedure single_file(schema_name in varchar2);
  procedure multi_file(schema_name in varchar2);

  --產出CREATE  TABLE的語法，並將相關建立Trigger、Index、Grant權限的語法產出。 
  PROCEDURE PRO_GENERATE_TABLE(P_OWNER VARCHAR2, P_TABLE VARCHAR2);

end reverse_engineer;

create or replace package body reverse_engineer as

  procedure single_file(schema_name in varchar2) as
    cursor object(schema_db varchar2) is
      select object_name, object_type
        from all_objects
       where owner = schema_db
         and object_type in ('TABLE', 'VIEW', 'SYNONYM', 'PACKAGE',
              'PROCEDURE', 'INDEX', 'TYPE', 'TRIGGER')
       order by object_type;
  
    rec_obj       object%rowtype;
    ddl_statement clob;
    fileHandler   UTL_FILE.FILE_TYPE;
  begin
    fileHandler := UTL_FILE.FOPEN('TEST_DIR',
                                  'r_e_' || upper(schema_name) || '.sql',
                                  'a');
    for rec_obj in object(upper(schema_name)) loop
      begin
        ddl_statement := dbms_metadata.get_ddl(rec_obj.object_type,
                                               rec_obj.object_name,
                                               schema_name);
        ddl_statement := ddl_statement || '/';
        UTL_FILE.PUT_LINE(fileHandler, ddl_statement);
      exception
        when others then
          fileHandler := UTL_FILE.FOPEN('TEST_DIR', 'r_e_error.log', 'a');
          UTL_FILE.PUT_LINE(fileHandler, sqlcode);
          UTL_FILE.PUT_LINE(fileHandler, sqlerrm);
          UTL_FILE.FCLOSE(fileHandler);
      end;
    end loop;
    UTL_FILE.FCLOSE(fileHandler);
  exception
    WHEN utl_file.invalid_path THEN
      raise_application_error(-20000,
                              'ERROR:   Invalid   path   for   file   or   path   not   in   INIT.ORA   or   SPFILE.ORA');
    WHEN utl_file.invalid_mode THEN
      raise_application_error(-20001,
                              'ERROR:   Invalid   mode   for   open   file');
    WHEN utl_file.invalid_operation THEN
      raise_application_error(-20002,
                              'ERROR:   Invalid   operation   for   file');
    
    WHEN others then
      UTL_FILE.FCLOSE(fileHandler);
      dbms_output.put_line('Error   code:   ' || sqlcode);
      dbms_output.put_line('Error   description:   ' || sqlerrm);
  end single_file;

  procedure multi_file(schema_name in varchar2) as
    cursor object(schema_db varchar2) is
      select object_name, object_type
        from all_objects
       where owner = schema_db
         and object_type in ('TABLE', 'VIEW', 'SYNONYM', 'PACKAGE',
              'PROCEDURE', 'INDEX', 'TYPE', 'TRIGGER')
       order by object_type;
  
    rec_obj       object%rowtype;
    ddl_statement clob;
    fileHandler   UTL_FILE.FILE_TYPE;
  begin
    for rec_obj in object(upper(schema_name)) loop
      fileHandler := UTL_FILE.FOPEN('TEST_DIR',
                                    'r_e_' || upper(schema_name) || '.' ||
                                    rec_obj.object_name || '.sql',
                                    'a');
      begin
        ddl_statement := dbms_metadata.get_ddl(rec_obj.object_type,
                                               rec_obj.object_name,
                                               schema_name);
        ddl_statement := ddl_statement || '/';
        UTL_FILE.PUT_LINE(fileHandler, ddl_statement);
        UTL_FILE.FCLOSE(fileHandler);
      exception
        when others then
          fileHandler := UTL_FILE.FOPEN('TEST_DIR', 'r_e_error.log', 'a');
          UTL_FILE.PUT_LINE(fileHandler, sqlcode);
          UTL_FILE.PUT_LINE(fileHandler, sqlerrm);
          UTL_FILE.FCLOSE(fileHandler);
      end;
    end loop;
  exception
    WHEN utl_file.invalid_path THEN
      raise_application_error(-20000,
                              'ERROR:   Invalid   path   for   file   or   path   not   in   INIT.ORA   or   SPFILE.ORA');
    WHEN utl_file.invalid_mode THEN
      raise_application_error(-20001,
                              'ERROR:   Invalid   mode   for   open   file');
    WHEN utl_file.invalid_operation THEN
      raise_application_error(-20002,
                              'ERROR:   Invalid   operation   for   file');
    WHEN others then
      UTL_FILE.FCLOSE(fileHandler);
      dbms_output.put_line('Error   code:   ' || sqlcode);
      dbms_output.put_line('Error   description:   ' || sqlerrm);
  end multi_file;

  PROCEDURE PRO_GENERATE_TABLE(P_OWNER VARCHAR2, P_TABLE VARCHAR2) IS
    V_DDL       clob;
    fileHandler UTL_FILE.FILE_TYPE;
  BEGIN
    fileHandler := UTL_FILE.FOPEN('TEST_DIR',
                                  'CRE_' || upper(P_OWNER) || '_' ||
                                  upper(P_TABLE) || '.sql',
                                  'a');
    begin
      V_DDL := dbms_metadata.get_ddl('TABLE', P_TABLE, P_OWNER);
      V_DDL := V_DDL || '/';
      UTL_FILE.PUT_LINE(fileHandler, V_DDL);
    exception
      when others then
        fileHandler := UTL_FILE.FOPEN('TEST_DIR', 'cre_error.log', 'a');
        UTL_FILE.PUT_LINE(fileHandler, sqlcode);
        UTL_FILE.PUT_LINE(fileHandler, sqlerrm);
        UTL_FILE.FCLOSE(fileHandler);
    end;
    UTL_FILE.FCLOSE(fileHandler);
  exception
    WHEN utl_file.invalid_path THEN
      raise_application_error(-20000,
                              'ERROR:   Invalid   path   for   file   or   path   not   in   INIT.ORA   or   SPFILE.ORA');
    WHEN utl_file.invalid_mode THEN
      raise_application_error(-20001,
                              'ERROR:   Invalid   mode   for   open   file');
    WHEN utl_file.invalid_operation THEN
      raise_application_error(-20002,
                              'ERROR:   Invalid   operation   for   file');
    
    WHEN others then
      UTL_FILE.FCLOSE(fileHandler);
      dbms_output.put_line('Error   code:   ' || sqlcode);
      dbms_output.put_line('Error   description:   ' || sqlerrm);
  END PRO_GENERATE_TABLE;

end reverse_engineer;