DECLARE

f1_tbl_name VARCHAR2(30) :='F1_MO_UPD';

f1_tbl_name_old VARCHAR2(30) :='F1_MO_UPD_OLD';

f1_tbl_name_new VARCHAR2(30) :='F1_MO_UPD_NEW';

tbl_owner varchar2(20) :='CISADM';

v_count NUMBER;

 

BEGIN

SELECT COUNT(1) INTO v_count FROM DBA_TABLES where table_name = 'F1_MO_UPD_NEW' and owner='CISADM';

 

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'CREATE TABLE ' || tbl_owner||'.'|| f1_tbl_name_new || '( MAINT_OBJ_CD CHAR(12 BYTE) NOT NULL ENABLE, PK_VALUE1 VARCHAR2(254 BYTE) NOT NULL ENABLE, PK_VALUE2 VARCHAR2(254 BYTE), PK_VALUE3 VARCHAR2(254 BYTE), PK_VALUE4 VARCHAR2(254 BYTE), PK_VALUE5 VARCHAR2(254 BYTE), "ENT_KEY_HASH" NUMBER(5,0) NOT NULL ENABLE )'|| ' partition by range (ENT_KEY_HASH) interval(1) (partition values less than (2))';

end if;

 

EXECUTE IMMEDIATE 'INSERT INTO ' ||tbl_owner||'.'|| f1_tbl_name_new ||' select * from ' ||tbl_owner||'.'|| f1_tbl_name ;

EXECUTE IMMEDIATE 'ALTER TABLE ' ||tbl_owner||'.'|| f1_tbl_name || ' rename to ' || f1_tbl_name_old;

EXECUTE IMMEDIATE 'ALTER TABLE ' ||tbl_owner||'.'|| f1_tbl_name_new || ' rename to ' || f1_tbl_name;

 

END;