manual unicode coversion for JDE part 2: rebuild indexes–just add parallel!

 

This is used when doing manual unicode conversion index generation.  Note that with a couple of tweeks, some parallel – you’ll be getting them done in nottime!

 

create or replace function jde_unicode_create_index(szOwnerSrc      in  varchar2,
                 szTableNameSrc  in  varchar2,
          szOwnerDest     in  varchar2,
          szTableNameDest in  varchar2,
                                                    errMsg          out varchar2)
       return integer
       authid current_user
is
  OW_SP_SUCCESS   constant integer := 0;
  OW_SP_ERROR     constant integer := -20101;
 
  pkName          varchar2(30) := null;
 
  cursor ind_name_cur is
    select owner, index_name, uniqueness, tablespace_name, ini_trans, max_trans,
           initial_extent, next_extent, min_extents, max_extents, pct_increase,
           freelists, pct_free
    from sys.all_indexes
    where table_owner = szOwnerSrc and table_name = szTableNameSrc
    order by index_name;
  ind_name_rec ind_name_cur%rowtype;
 
  type ColCurTyp is ref cursor;
  ind_col_info    ColCurTyp;
  ind_col_exp     ColCurTyp;
  cons_col_info   ColCurTyp;
 
  sql_stmt1 varchar2(256);
  sql_stmt2 varchar2(256);
  sql_ind_create varchar2(1024);
  sql_ind_rename varchar2(128);
  sql_cons_drop varchar2(128);
 
  ind_col_name varchar2(30);
  ind_col_pos integer;
  ind_col_order varchar2(4);
  cons_col_name varchar2(30);
 
  ind_col_expression varchar2(32);
  isNotFirstColumn boolean := false;
  isNotFirstIndex boolean := false;
  ind_count integer := 0;
 
begin
  errMsg := null;
 
  
  sql_stmt1 := 'SELECT COLUMN_NAME, COLUMN_POSITION, DESCEND FROM SYS.ALL_IND_COLUMNS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 ORDER BY COLUMN_POSITION';
  sql_stmt2 := 'SELECT COLUMN_EXPRESSION FROM SYS.ALL_IND_EXPRESSIONS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 AND COLUMN_POSITION = :3';
 
  isNotFirstColumn := false;
 
  open ind_name_cur;
  loop
    if (isNotFirstIndex) then
      isNotFirstColumn := false;
    end if;
 
    sql_ind_create := 'CREATE ';
 
    fetch ind_name_cur into ind_name_rec;
    exit when ind_name_cur%notfound;
    
 
    sql_ind_rename := 'ALTER INDEX ' || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' RENAME TO ' || szTableNameSrc || '_' || ind_count;
    ind_count := ind_count + 1;
 
    if (ind_name_rec.uniqueness = 'UNIQUE') then
      sql_ind_create := sql_ind_create || 'UNIQUE INDEX ';
    else
      sql_ind_create := sql_ind_create || 'INDEX ';
    end if;
    
    sql_ind_create := sql_ind_create || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' ON ' || szOwnerDest || '.' || szTableNameDest || '(';
 
    open ind_col_info for sql_stmt1 using ind_name_rec.owner, ind_name_rec.index_name;
    loop
      fetch ind_col_info into ind_col_name, ind_col_pos, ind_col_order;
      exit when ind_col_info%notfound;
      
      if (isNotFirstColumn) then
        sql_ind_create := sql_ind_create || ', ';
      end if;
 
      
 
      if (ind_col_order = 'DESC') then
        open ind_col_exp for sql_stmt2 using ind_name_rec.owner, ind_name_rec.index_name, ind_col_pos;
        fetch ind_col_exp into ind_col_expression;
        ind_col_name := substr(ind_col_expression, 2, length(ind_col_expression)-2);
        
        close ind_col_exp;
      end if;
 
      sql_ind_create := sql_ind_create || ind_col_name || ' ' || ind_col_order;
      isNotFirstColumn := true;
    end loop;
    close ind_col_info;
 
    isNotFirstIndex := true;
 
    sql_ind_create := sql_ind_create || ')';
    if (ind_name_rec.pct_free is not null) then
      sql_ind_create := sql_ind_create || ' PCTFREE ' || ind_name_rec.pct_free;
    end if;
    if (ind_name_rec.ini_trans is not null) then
      sql_ind_create := sql_ind_create || ' INITRANS ' || ind_name_rec.ini_trans;
    end if;
    if (ind_name_rec.max_trans is not null) then
      sql_ind_create := sql_ind_create || ' MAXTRANS ' || ind_name_rec.max_trans;
    end if;
    if (ind_name_rec.initial_extent is not null) or
       (ind_name_rec.next_extent is not null) or
       (ind_name_rec.min_extents is not null) or
       (ind_name_rec.max_extents is not null) or
       (ind_name_rec.pct_increase is not null) or
       (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' STORAGE (';
    end if;
 
    if (ind_name_rec.initial_extent is not null) then
      sql_ind_create := sql_ind_create || ' INITIAL ' || ind_name_rec.initial_extent;
    end if;
    if (ind_name_rec.next_extent is not null) then
      sql_ind_create := sql_ind_create || ' NEXT ' || ind_name_rec.next_extent;
    end if;
    if (ind_name_rec.min_extents is not null) then
      sql_ind_create := sql_ind_create || ' MINEXTENTS ' || ind_name_rec.min_extents;
    end if;
    if (ind_name_rec.max_extents is not null) then
      sql_ind_create := sql_ind_create || ' MAXEXTENTS ' || ind_name_rec.max_extents;
    end if;
    if (ind_name_rec.pct_increase is not null) then
      sql_ind_create := sql_ind_create || ' PCTINCREASE ' || ind_name_rec.pct_increase;
    end if;
    if (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' FREELISTS ' || ind_name_rec.freelists;
    end if;
 
    if (ind_name_rec.initial_extent is not null) or
       (ind_name_rec.next_extent is not null) or
       (ind_name_rec.min_extents is not null) or
       (ind_name_rec.max_extents is not null) or
       (ind_name_rec.pct_increase is not null) or
       (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' )';
    end if;
 
    if (ind_name_rec.tablespace_name is not null) then
      sql_ind_create := sql_ind_create || ' TABLESPACE '|| ind_name_rec.tablespace_name;
    end if;
 
    
    execute immediate sql_ind_rename;
 
    
    execute immediate sql_ind_create;
 
  end loop;
 
  close ind_name_cur;
 
  
  begin
    select constraint_name into pkName from sys.all_constraints where owner = szOwnerSrc and table_name = szTableNameSrc and constraint_type = 'P';
  exception
    when no_data_found then
      
      errMsg := 'Warning: no primary key in table ' || szOwnerDest || '.' || szTableNameDest;
    when others then
      
      errMsg := 'Error - ' || sqlerrm;
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
      return (OW_SP_ERROR);
  end;
 
 
 
  if (pkName > ' ') then
    sql_stmt1 := 'ALTER TABLE ' || szOwnerDest || '.' || szTableNameDest || ' ADD CONSTRAINT ' || pkName || ' PRIMARY KEY (';
    sql_stmt2 := 'SELECT COLUMN_NAME FROM SYS.ALL_CONS_COLUMNS WHERE OWNER = :1 AND CONSTRAINT_NAME = :2';
    sql_cons_drop := 'ALTER TABLE ' || szOwnerSrc || '.' || szTableNameSrc || ' DROP CONSTRAINT ' || pkName;
 
    open cons_col_info for sql_stmt2 using szOwnerSrc, pkName;
    isNotFirstColumn := false;
    loop
      fetch cons_col_info into cons_col_name;
      exit when cons_col_info%notfound;
 
      if (isNotFirstColumn) then
        sql_stmt1 := sql_stmt1 || ', ';
      end if;
 
      sql_stmt1 := sql_stmt1 || cons_col_name;
      isNotFirstColumn := true;
    end loop;
    close cons_col_info;
 
    
    execute immediate sql_cons_drop;
 
    sql_stmt1 := sql_stmt1 || ')';
    
    execute immediate sql_stmt1;
  end if;
  
  return (OW_SP_SUCCESS);
exception
  when others then
    
    errMsg := 'Error - ' || sqlerrm;
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
    return (OW_SP_ERROR);
end jde_unicode_create_index;