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"