More on unicode conversion and JD Edwards

All of the heavy lifting is done in a stop red procedure, no matter what platform you login with.  This also shows that JD Edwards has the ability to call a stored proc in the middleware (well, that is my assumption).

So this is pretty handy, you can work out the variables by reading the code and then you can just call this with all of the tips and tricks you want!  Forcing parallelism might be a good start!


CREATE OR REPLACE PROCEDURE "JDEDBA"."OWTBLCONV" (szUseridIn    in  varchar2,
                                      szPasswdIn    in  varchar2,
                                      szCatalogIn   in  varchar2,
                                      szOwnerIn     in  varchar2,
                                      szTableNameIn in  varchar2,
                                      nFlag         in  integer,
                                      szPathIn      in  varchar2
                                      /* OLEDB has problems with out varchar fields. Temporarily comment this out,
                                      szReturnDesc out varchar2*/)
       authid current_user
is
  OW_SP_SUCCESS   constant integer := 0;
  OW_SP_ERROR     constant integer := -20101;
  TAB_NOT_EXIST   constant integer := -942;

  szOwner         varchar2(30);
  szTableName     varchar2(30);

  type ColCurTyp is ref cursor;
  tab_col_info    ColCurTyp;

  col_name        varchar2(30);
  col_type        varchar2(106);
  col_length      number;
  char_length     number;
  data_precision  number;

  sql_stmt        varchar2(10000):= null;
  insert_sql_stmt varchar2(4000) := null;
  select_sql_stmt varchar2(4000) := null;
  old_tab_name    varchar2(30)   := UPPER(szTableNameIn) || '_NONUNI';
  notFirstTime       boolean     := false;
  nCharCols          integer     := 0;

  tablespace      varchar2(30);
  initextent      number;
  nextextent      number;
  pctincrease     number;

  retcode         integer;
  errMsg          varchar2(512);
  szReturnDesc    varchar2(512);

begin
  if (szOwnerIn = null) or (szTableNameIn = null) then
     szReturnDesc := 'Error - Either table owner or name is null';
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
  end if;

  szReturnDesc := 'Success';
  szOwner      := UPPER(szOwnerIn);
  szTableName  := UPPER(szTableNameIn);

  begin
    SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE INTO tablespace, initextent, nextextent, pctincrease FROM SYS.ALL_TABLES WHERE OWNER = UPPER(szOwnerIn) AND TABLE_NAME = UPPER(szTableNameIn);
  exception
    when no_data_found then
      dbms_output.put_line('Error: Table ' || szOwner || '.' || szTableName || ' does not exist');
      szReturnDesc := 'Error - Table ' || szOwner || '.' || szTableName || ' does not exist';
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
    when others then
      szReturnDesc := 'Error - ' || sqlerrm;
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
  end;

  SELECT COUNT(*) INTO nCharCols FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = UPPER(szOwnerIn) AND TABLE_NAME = UPPER(szTableNameIn) AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'CLOB', 'LONG');
  if nCharCols = 0 then
   
    szReturnDesc := 'Success - No conversion needed for table ' || szOwner || '.' || szTableName;
    return;
  end if;

  sql_stmt := 'ALTER SESSION SET NLS_NCHAR_CONV_EXCP=TRUE';
  execute immediate sql_stmt;

  begin
    sql_stmt := 'DROP TABLE ' || szOwner || '.' || old_tab_name;
    execute immediate sql_stmt;
  exception
    when others then

      if (sqlcode != TAB_NOT_EXIST) then
        szReturnDesc := 'Error - ' || sqlerrm;
        RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
      end if;
  end;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' RENAME TO ' || old_tab_name;

  execute immediate sql_stmt;

  sql_stmt := 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, CHAR_LENGTH FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = :1 AND TABLE_NAME = :2 ORDER BY COLUMN_ID';

  open tab_col_info for sql_stmt using szOwner, old_tab_name;

  sql_stmt := 'CREATE TABLE ' || szOwner || '.' || szTableName || '(';

  insert_sql_stmt := 'INSERT INTO ' || szOwner || '.' || szTableName || '(';
  select_sql_stmt := ' SELECT ';
   
  loop
    fetch tab_col_info into col_name, col_type, col_length, data_precision, char_length;
    exit when tab_col_info%notfound;

    if (notFirstTime = true) then
      sql_stmt := sql_stmt || ', ';
      insert_sql_stmt := insert_sql_stmt || ', ';
      select_sql_stmt := select_sql_stmt || ', ';
    end if;

    case col_type
      when 'CHAR' then col_type := 'NCHAR';
      when 'VARCHAR2' then col_type := 'NVARCHAR2';
      when 'CLOB' then col_type := 'NCLOB';
      else
        null;
    end case;

    if (col_type = 'LONG') then
      sql_stmt := sql_stmt || col_name || ' ' || 'NCLOB';
    else
      sql_stmt := sql_stmt || col_name || ' ' || col_type;
    end if;
    insert_sql_stmt := insert_sql_stmt || col_name;

    if (col_type = 'NCHAR') or (col_type = 'NVARCHAR2') then
      sql_stmt := sql_stmt || '(' || char_length || ')';
      select_sql_stmt := select_sql_stmt || 'TO_NCHAR(' || col_name || ')';
    elsif (col_type = 'NCLOB') then
     
      select_sql_stmt := select_sql_stmt || 'TO_NCLOB(' || col_name || ')';
    elsif (data_precision is not null) and (col_type = 'NUMBER') then
      sql_stmt := sql_stmt || '(' || data_precision || ')';
      select_sql_stmt := select_sql_stmt || col_name;
    elsif (col_type = 'LONG') then
      select_sql_stmt := select_sql_stmt || 'TO_LOB(' || col_name || ')';
    else
      select_sql_stmt := select_sql_stmt || col_name;
    end if;

    notFirstTime := true;
  end loop;

  close tab_col_info;

  sql_stmt := sql_stmt || ')';
  if tablespace is not null then
    sql_stmt := sql_stmt || ' TABLESPACE ' || tablespace;
  end if;
  sql_stmt := sql_stmt || ' STORAGE (';
  if initextent >= 1000 then
    sql_stmt := sql_stmt || 'INITIAL ' || initextent;
  end if;
  if nextextent >= 1000 then
    sql_stmt := sql_stmt || ' NEXT ' || nextextent;
  end if;
  if pctincrease >= 0 then
    sql_stmt := sql_stmt || ' PCTINCREASE ' || pctincrease || ')';
  else
    sql_stmt := sql_stmt || ' PCTINCREASE 0)';
  end if;

  insert_sql_stmt := insert_sql_stmt || ')';

  execute immediate sql_stmt;

  sql_stmt := 'GRANT ALL ON ' || szOwner || '.' || szTableName || ' TO PUBLIC';
  execute immediate sql_stmt;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' NOLOGGING';
  execute immediate sql_stmt;
  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || old_tab_name || ' NOLOGGING';
  execute immediate sql_stmt;

  select_sql_stmt := select_sql_stmt || ' FROM ' || szOwner || '.' || old_tab_name;

  execute immediate insert_sql_stmt || select_sql_stmt;

  retcode := jde_unicode_create_index(szOwner, old_tab_name, szOwner, szTableName, errMsg);
  if retcode = OW_SP_ERROR then
   
    szReturnDesc := errMsg;
    return;
  end if;
  if errMsg is not null then
    szReturnDesc := szReturnDesc || ' - ' || errMsg;
  end if;

  commit;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' LOGGING';
  execute immediate sql_stmt;

 
  sql_stmt := 'DROP TABLE ' || szOwner || '.' || old_tab_name;
  execute immediate sql_stmt;

  return;
exception
  when others then
    dbms_output.put_line('Error: ' || sqlerrm);
    szReturnDesc := 'Error - ' || sqlerrm;
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
end owtblconv;