Script contacins two sections
1) Function that would combine multiple rows into single string
2) Script used to create "Create Index" statement.
CREATE OR REPLACE FUNCTION xx_concate_FIELDs (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
OPEN rec FOR sqlstr;
LOOP
FETCH rec INTO field;
EXIT WHEN rec%NOTFOUND;
ret := ret || field || sep;
END LOOP;
if length(ret) = 0 then
RETURN '';
else
RETURN substr(ret,1,length(ret)-length(sep));
end if;
end;
/
SELECT 'create index ' || t.owner || '.' || 'xx_' ||
substr(t.table_name, 1, 20) || xxhr.emp_tmp.nextval ||
' on ' || t.owner || '.' || t.table_name || '(' ||
t.concate_fields || ');' STRING
,t.owner
,t.table_name
FROM (SELECT DISTINCT uc.owner
,uc.constraint_name
,uc.constraint_type
,uc.table_name
,xx_concate_fields('select column_name from all_cons_columns where table_name = ''' ||
uc.table_name || '''' ||'AND constraint_name=''' || uc.constraint_name ||''' order by position ',',') concate_fields
FROM all_constraints uc
,all_cons_columns ucc
WHERE uc.constraint_type = 'R' AND
uc.constraint_name = ucc.constraint_name AND
uc.table_name = ucc.table_name) t;
