How can i ignore create portioned script from create table script using get_ddl in oracle?

Question

I am working on java project in which we are getting create table ddl using get_ddl method in Oracle 12c.

For example my table name is PARENT_EMP and schema is SECONDARYUSER then I am using get_ddl method.

select dbms.get_ddl('TABLE','PARENT_EMP','SECONDARYUSER') from dual;

before executing above statement I am executing following script to ignore constrainst and table spaces from ddl.

begin
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false); 
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);           
end;

But my issue is, some of table has REFERENCE PARTITIONED and if I get ddl using get_ddl method then also I am getting foreign constraint script.

Parent table DDl is :-

 create table parent_emp(
    empno      number  primary key,
    job        varchar2(20),
    sal        number(7,2),
    deptno     number(2)
    )
    partition by list(job)
    ( partition p_job_dba values ('DBA'),
      partition p_job_mgr values ('MGR'),
     partition p_job_vp  values ('VP')
   );

Child table DDl is :

  CREATE TABLE "SECONDARYUSER"."REFERENCE_EMP" 
   (    
    "ENAME" VARCHAR2(10), 
    "EMP_ID" NUMBER, 
    "EMPNO" NUMBER, 
     CONSTRAINT "FK_EMPNO" FOREIGN KEY ("EMPNO")
      REFERENCES "SECONDARYUSER"."PARENT_EMP" ("EMPNO") ENABLE
   ) 
  PARTITION BY REFERENCE ("FK_EMPNO") 
 (PARTITION "P_JOB_DBA" , 
 PARTITION "P_JOB_MGR" , 
 PARTITION "P_JOB_VP" ) "

Please suggest how can I get child table ddl without getting foreign constraint and partitioned ddl , or how can I creating portioned on already created table using alter table. Also is there any way to get partitioned ddl using get_ddl method?

Answer 1

I just found the answer.

You are missing this declaration:

exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING',false);

Include it, and it will ignore the part with 'partition'.

So, you need those ones:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);

Credits: one of the answers of this.