Oracle PL/SQL DDL Export Wrapper (Exports objects using dbms_metadata package and user object tables)

ddl_export.fnc

create or replace function ddl_export(
  p_type in varchar2,
  p_name in varchar2,
  p_package in boolean default true
) return clob is
  
  l_result clob;

  function use_table_user_source(
    p_type in varchar2,
    p_name in varchar2
  ) return clob is
    l_result clob;
  begin

    for line in (select * from user_source u where u.type = p_type and u.name = p_name) loop
      l_result := l_result || line.text;
    end loop;
      
    return l_result;

  end use_table_user_source;

  function use_package_dbms_metadata(
    p_type in varchar2,
    p_name in varchar2
  ) return clob is
    l_result clob;
    
    procedure initialize_dbms_metadata is
    begin
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);
      dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', true);
      --dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT', true);
    end initialize_dbms_metadata;
    
    function get_ddl(
      p_type in varchar2,
      p_name in varchar2
    ) return clob is
      l_result clob;
    begin
      l_result := dbms_metadata.get_ddl(p_type, p_name);

      -- Transform retrieved text (beautify)
      --  l_result := regexp_replace(l_result, globais.crlf, '');
      --  l_result := trim(leading chr(13) from l_result);
      l_result := substrb(l_result, 4);
      l_result := replace(l_result, '"'||user||'".', '');
      l_result := replace(l_result, '"', '');
      l_result := lower(l_result);

      return l_result;
    end get_ddl;

  begin
    initialize_dbms_metadata;
  
    -- Create clob with export result
    l_result := '';

    -- Export create table
    l_result := l_result || '-- Create table' || globais.crlf;
    l_result := l_result || get_ddl(p_type, p_name);
    
    -- Export indices
    l_result := l_result || '-- Create/Recreate indexes' || globais.crlf;
    for l_index in (select ui.index_name from user_indexes ui where ui.table_name = p_name) loop
          l_result := l_result || get_ddl('INDEX', l_index.index_name);
    end loop;
    
    return l_result;

  end use_package_dbms_metadata;

begin

  if p_type in ('PACKAGE', 'PACKAGE BODY') and p_package then
    l_result := use_table_user_source('PACKAGE', p_name) || 
                '\' || globais.crlf || 
                use_table_user_source('PACKAGE BODY', p_name);
  elsif p_type in ('FUNCTION', 'JAVA SOURCE', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY') then
    l_result := use_table_user_source(p_type, p_name);
  else
    l_result := use_package_dbms_metadata(p_type, p_name);
  end if;

  return l_result;
  
end ddl_export;