Copy table indexes using dbms_metadata

[rjlolli]

Using the dbms_metadata package, I'm trying to copy indexes from one
table to another. Since index names must be unique, I'm also trying
to append _724 to each new index. I'm using the following script and
it's not quite working exactly how I would like. The script below is
using dbms_metadata.ddl to retrieve the create index statements for
the table PRSNL and where it finds PRSNL in the index name it replaces
it with PRSNL_724 and it also appends _724 to the name. For example,
lets say the index XPK_PRSNL existed on the table PRSNL. This script
will create an index on the PRSNL_724 table named XPK_PRSNL_724_724.
I want the script to just append _724, so I end up with the index name
XPK_PRSNL_724 on the PRSNL_724 table. If I take out the
replace(p_src_tbl,p_trg_tbl), the script creates the XPK_PRSNL_724
table on the PRSNL table instead of the PRSNL_724 table. I'm looking
for help on how to fix it. Thanks.


declare
p_src_tbl varchar2(30):= 'PRSNL';
p_trg_tbl varchar2(30):= 'PRSNL_724';
l_ddl varchar2(32000);
begin
for trg in (select index_name from user_indexes where table_name =
p_src_tbl) loop
l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'INDEX',
trg.index_name),p_src_tbl,p_trg_tbl),trg.index_name,substr(trg.index_name||
724, 1, 30)) as varchar2);
execute immediate l_ddl;
end loop;
end;
/

[Michael Moore]

DECLARE
   p_src_tbl varchar2 (30) := 'PRSNL';
   p_trg_tbl varchar2 (30) := 'PRSNL_724';
   l_ddl varchar2 (32000);
BEGIN
   FOR trg IN (SELECT index_name
                 FROM user_indexes
                WHERE table_name = p_src_tbl)
   LOOP
      l_ddl :=
         CAST
         (
            REPLACE
            (
               REPLACE
               (
                  DBMS_METADATA.get_ddl ('INDEX', trg.index_name),
                  p_src_tbl,
                  p_trg_tbl
               ),
               trg.index_name||'" ON' ,
               SUBSTR (trg.index_name || '724" ON', 1, 30)
            )
               AS varchar2
         );

      EXECUTE IMMEDIATE l_ddl;
   END LOOP;
END;
/
try this
Mike

[Michael Moore]

Also, you don't need the cast since the datatype is already character.