-- Reverse Engineer script for all indexes found within the input schema

accept table_name prompt  'Enter Table Name :'

declare

cursor index_detail is select owner,index_name,decode(uniqueness,'NONUNIQUE','','UNIQUE') uniq,table_owner||'.'||table_name table_nm,
tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from dba_indexes a where table_name = upper('&table_name') 
and not exists
(select '1' from dba_constraints where constraint_name = a.index_name and constraint_type in ('P','U'));

index_var varchar2(40);
file_id utl_file.file_type;
column_pos_var dba_ind_columns.column_position%type;
ind_column_name dba_ind_columns.column_name%type;
index_expression_col varchar2(500);

cursor index_columns is select column_name,column_position,descend from dba_ind_columns
where index_name = index_var
order by column_position;

utl_file_loc varchar2(100);

begin

select substr(value,1,decode(instr(value,','),0,length(value),instr(value,',')-1)) into utl_file_loc from v$parameter where
name = 'utl_file_dir';

file_id := utl_file.fopen(utl_file_loc,lower('&table_name')||'_idx_make.sql','w');

for rec1 in index_detail
loop

   utl_file.put_line(file_id,'create '||rec1.uniq||' index '||rec1.owner||'.'||rec1.index_name||' on '||rec1.table_nm||'(');
   index_var := rec1.index_name;
   select max(column_position) into column_pos_var from dba_ind_columns
   where index_name = index_var;

     for rec2 in index_columns 
       loop
         index_expression_col := null;
         if rec2.descend = 'DESC'
          then
            select column_expression into index_expression_col from dba_ind_expressions where index_name = rec1.index_name and
            column_position = rec2.column_position;
            index_expression_col := trim(translate(index_expression_col,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_"',
            '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_'))||' desc';
         end if;
         if index_columns%rowcount = column_pos_var
          then
             ind_column_name := nvl(index_expression_col,rec2.column_name);
             exit;
         end if;
             utl_file.put_line(file_id,nvl(index_expression_col,rec2.column_name)||',');
       end loop;
         utl_file.put_line(file_id,ind_column_name||')');
         utl_file.put_line(file_id,'nologging');
         utl_file.put_line(file_id,'tablespace '||rec1.tablespace_name);
         utl_file.put_line(file_id,'storage(initial '||rec1.initial_extent||' next '||rec1.next_extent||' minextents '||rec1.min_extents||
' pctincrease '||rec1.pct_increase||' maxextents '||rec1.max_extents||');');
         utl_file.put_line(file_id,'');
end loop;

utl_file.fclose(file_id);

/*

exception
when utl_file.invalid_path
then
dbms_output.put_line('Operation Failure');
*/
end;
/