Tuesday, April 24, 2012

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;
/