I need to copy a partitioned index from one table to another, I want to remove the partitioning but keep the LOCAl keyword in there. Something like this --

DECLARE
   v_handle                        NUMBER;
   v_transform_handle              NUMBER;
   v_transform_handle1 NUMBER;
   v_transform_handle2 NUMBER;   
   v_table_query                   CLOB;
BEGIN

   v_handle := DBMS_METADATA.OPEN(object_type    => 'INDEX'
                                 );

   DBMS_METADATA.SET_FILTER( v_handle
                           , 'SCHEMA'
                           , 'SCHEMANAME');

   DBMS_METADATA.SET_FILTER( v_handle
                           , 'NAME'
                           , 'INDEXNAME');

  
   v_transform_handle := DBMS_METADATA.ADD_TRANSFORM(v_handle, 'SXMLDDL');                                

   DBMS_METADATA.SET_TRANSFORM_PARAM ( v_transform_handle, 'PARTITIONING'      , FALSE);  

    DBMS_METADATA.SET_TRANSFORM_PARAM ( v_transform_handle, 'PRESERVE_LOCAL'      , TRUE);                                                    

   v_table_query := DBMS_METADATA.FETCH_CLOB(v_handle);

END;
/


But it fails when I fetch at the last line. Can you tell me what m I doing wrong?

You know local indexes are partitioned, right?

If you just want to remove partition properties, using local is fine. But if you want an unpartitioned index, this won't give you want you want...

Anyway to get the SXMLDDL transform results it's a slightly convoluted process:

- Start the transform as SXML
- Fetch the resulting XML document
- Close the transform and open a new one as SXMLDDL
- dbms_metadata.convert the XML document to DDL
- Fetch the resulting clob:

drop table t purge;
create table t (
  x int
) partition by range (x) (
  partition p0 values less than (10),
  partition p1 values less than (20),
  partition p2 values less than (30),
  partition p3 values less than (40)
);

create index i on t(x) local;

insert into t 
  select rownum from dual connect by level < 40;
commit;


declare
   v_handle           number;
   v_transform_handle number;
   v_table_query      sys.xmltype;
   xmldoc             xmltype;
   l_ddl               clob;
begin

   v_handle := dbms_metadata.open(object_type    => 'INDEX');

   dbms_metadata.set_filter( v_handle, 'SCHEMA', 'CHRIS');

   dbms_metadata.set_filter( v_handle, 'NAME', 'I');

   v_transform_handle := dbms_metadata.add_transform(v_handle, 'SXML');     
   xmldoc := dbms_metadata.fetch_xml(v_handle);
   dbms_metadata.close(v_handle);

   dbms_lob.createtemporary(l_ddl, true);
   v_handle := dbms_metadata.openw(object_type => 'INDEX');
   v_transform_handle := dbms_metadata.add_transform(v_handle, 'SXMLDDL');   
   dbms_metadata.set_transform_param ( v_transform_handle, 'PARTITIONING', false);  
   dbms_metadata.set_transform_param ( v_transform_handle, 'PRESERVE_LOCAL', true);                                                    

   dbms_metadata.convert(v_handle, xmldoc, l_ddl);
   dbms_metadata.close(v_handle);
   dbms_output.put_line(l_ddl);
   dbms_lob.freetemporary(l_ddl);

end;
/
CREATE INDEX "CHRIS"."I" ON "CHRIS"."T" ("X")
  PCTFREE 10 INITRANS 2
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

Note you also need to create (& free) the temporary LOB holding the output

HT:
http://www.moeding.net/archives/54-XSLT-on-DBMS_METADATA.html