PL/SQL program to get metadata by transforming

set serveroutput on

set linesize 32767

declare

  handle        number;

  ddl_handle    number;

  modify_handle number;

  fetched_ddl   clob;

  owner         varchar2(100);

  table_name    varchar2(100);

  ts_arg        varchar2(10);

begin

  owner := 'MURTY';

  table_name := 'TAB1';

  -- Do we need tablespace names in table and index DDLs?

  ts_arg := upper('YES');

 

  handle := dbms_metadata.open('TABLE');

  dbms_metadata.set_filter(handle, 'NAME', table_name);

  dbms_metadata.set_filter(handle, 'SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  -- Instead of original schema, we can remap to other schema, 

  -- blank '' to remove schema name

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  -- PK and FKs will be generated as ALTER, not in table definitions

  dbms_metadata.set_transform_param(ddl_handle, 'CONSTRAINTS_AS_ALTER', true);

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  -- We do not need INITIAL, NEXT extent in DDLs

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

 

  fetched_ddl := dbms_metadata.fetch_clob(handle);

  dbms_output.put_line(replace(

                               replace(fetched_ddl, '"', ''), -- Remove quotes in DDLs

                               ';',

  -- Extra blank line between table and constraint definitions

                               ';' || chr(10)  

                              )

                      );

  -- Now dependent INDEX objects

  handle := dbms_metadata.open('INDEX');

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_NAME', table_name);

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

  -- Get one index at a time.

  fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  while (fetched_ddl is not null) loop

    dbms_output.put_line(fetched_ddl);

    fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  end loop;

end;

/