How to remove Flashback Archive statements from Oracle's dbms_metadata.fetch_ddl output

57 Views Asked by At

When running dbms_metadata.fetch_ddl or dbms_metadata.get_ddl, the output includes ALTER TABLE "SCHEMA"."TABLE" FLASHBACK ARCHIVE "ARCHIVE_NAME". Is there any option in the dbms_metadata package to remove this line? I could use regex, but I would rather avoid it if possible.

I have tried changing all of the options for set_transform_parameter that I could find. None of the options remove this statement. Any insight would be appreciated.

Side note: Even with the SQLTERMINATOR set to true, the ALTER TABLE statement does not have a line terminator at the end, which I found odd.

My current script:

set SERVEROUTPUT ON

declare
  metadata_handle number;
  transform_handle number;
  ddl_handle number;
  result_array sys.ku$_ddls;

begin

  metadata_handle := dbms_metadata.open('TABLE');
--  transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY');
--  dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2');
--  dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y');
  ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');
  dbms_metadata.set_transform_param (ddl_handle, 'SQLTERMINATOR', true);
  dbms_metadata.set_transform_param (ddl_handle, 'PRETTY', true);
  dbms_metadata.set_transform_param (ddl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);
  dbms_metadata.set_transform_param (ddl_handle, 'TABLESPACE', false);
  dbms_metadata.set_transform_param (ddl_handle, 'PARTITIONING', false);
  dbms_metadata.set_transform_param (ddl_handle, 'CONSTRAINTS', false);
  dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'SCHEMA_NAME');


  loop
    result_array := dbms_metadata.fetch_ddl(metadata_handle);
    exit when result_array is null;
      for i in result_array.first..result_array.last loop
      dbms_output.put_line(result_array(i).ddltext);
    end loop;
  end loop; 
  dbms_metadata.close(metadata_handle);
end;

Output:

CREATE TABLE "TEST_SCHEMA"."TEST_TABLE"
   (
        "TEST_COLUMN" VARCHAR2(20)
   ) ;
  
ALTER TABLE "TEST_SCHEMA"."TEST_TABLE" FLASHBACK ARCHIVE "TEST_ARCHIVE"
0

There are 0 best solutions below