How to remove Primary Key from DBMS_METADATA?

235 Views Asked by At

I'm running Oracle 12.2 and want to copy the table with its dependent objects to a new schema and new name using DBMS_METADATA as below:

h_read := DBMS_METADATA.OPEN('TABLE_EXPORT');
DBMS_METADATA.SET_FILTER(h_read,'SCHEMA','FRAMEWORK');
DBMS_METADATA.SET_FILTER(h_read,'NAME',l_Table_Name);
...
h_modify := DBMS_METADATA.ADD_TRANSFORM(h_write,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(h_modify, 'REMAP_SCHEMA', 'FRAMEWORK', l_new_Schema);
DBMS_METADATA.SET_REMAP_PARAM(h_modify, 'REMAP_NAME', l_parsed_old_name, l_new_Name);

All good, except I get duplicates on Primary Key constraint and its index. This is because the Primary Key constraint is bound to table's definition (together with all NOT NULL constraints). I can exclude Primary Key constraint and its index based on their names (%_PK naming convention):

DBMS_METADATA.SET_FILTER(h_read,'EXCLUDE_NAME_EXPR','='''||l_Table_Name||'_PK''', 'INDEX'); 
DBMS_METADATA.SET_FILTER(h_read,'EXCLUDE_NAME_EXPR','='''||l_Table_Name||'_PK''', 'CONSTRAINT');

... but then:

l_parsed_items := sys.ku$_parsed_items();
DBMS_METADATA.FETCH_XML_CLOB(h_read, l_XML, l_parsed_items, l_Object_Type );

.. does not touch the Primary Key constraint within the table, so I cannot add REMAP_NAME to it. If I add:

DBMS_METADATA.SET_TRANSFORM_PARAM(h_ddl, 'CONSTRAINTS', FALSE);

... then all NOT NULL constraints are gone too - bummer.

Is there a way to skip Primary Key constraint ONLY from the table definition in DBMS_METADATA's XML?

0

There are 0 best solutions below