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;
