I want to create a procedure to create backup tables for tables which begin with prefix 'KPI'.
I want to copy the tables, comments, sequences, indexes.
Is there any built in oracle package to do this.
Because I am trying to do it with pure plsql and I find it a little complicated.
create or replace PROCEDURE CREATE_BACKUP_TABLES
AS
BEGIN
-- -----------------------------------------------------------
-- CREATE TABLES AND SEQUENCES
-- ----------------------------------------------------------
<<DROP_AND_CREATE>>
BEGIN
FOR REC IN ( SELECT 'DROP TABLE BACKUP_' || TABLE_NAME as TABLE_DROP_STMT,
'DROP SEQUENCE BACKUP_' || TABLE_NAME || '_SEQ' as SEQ_DROP_STMT,
'CREATE TABLE BACKUP_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE 1=2' as TABLE_CREATION_STMT,
'CREATE SEQUENCE BACKUP_' || TABLE_NAME || '_SEQ MAXVALUE 999999999999999999999999999 INCREMENT BY 1 ' ||
CASE WHEN TABLE_NAME IN ('KPI_TABLE1', 'KPI_CONTRACTS') THEN 'NOCACHE '
ELSE 'CACHE 100 '
END || ' NOORDER NOCYCLE' as SEQ_CREATION_STMT,
'ALTER TABLE BACKUP_' || TABLE_NAME || ' ADD CONSTRAINT BACKUP_' || TABLE_NAME || '_PK PRIMARY KEY (ID)' AS PRIMARY_KEY_CREATION
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'KPI%'
) LOOP
BEGIN
-- DBMS_OUTPUT.PUT_LINE(REC.TABLE_DROP_STMT);
EXECUTE IMMEDIATE REC.TABLE_DROP_STMT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
-- DBMS_OUTPUT.PUT_LINE(REC.SEQ_DROP_STMT);
EXECUTE IMMEDIATE REC.SEQ_DROP_STMT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- DBMS_OUTPUT.PUT_LINE(REC.TABLE_CREATION_STMT);
EXECUTE IMMEDIATE REC.TABLE_CREATION_STMT;
-- DBMS_OUTPUT.PUT_LINE(REC.SEQ_CREATION_STMT);
EXECUTE IMMEDIATE REC.SEQ_CREATION_STMT;
-- DBMS_OUTPUT.PUT_LINE(REC.PRIMARY_KEY_CREATION);
EXECUTE IMMEDIATE REC.PRIMARY_KEY_CREATION;
END LOOP;
END DROP_AND_CREATE;
-- ---------------------------------------------------
-- ----------------------------------------------------------
-- CREATE COMMENTS
-- ----------------------------------------------------------
<<CREATE_COMMENT>>
BEGIN
FOR REC IN (SELECT 'COMMENT ON COLUMN "BACKUP_' || TABLE_NAME || '"."' ||
COLUMN_NAME || '" IS ''' || COMMENTS || '''' AS CREATE_COMMENT_CMD
FROM USER_COL_COMMENTS
WHERE TABLE_NAME LIKE 'KPI%'
) LOOP
BEGIN
-- DBMS_OUTPUT.PUT_LINE(REC.CREATE_COMMENT_CMD);
EXECUTE IMMEDIATE REC.CREATE_COMMENT_CMD;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END CREATE_COMMENT;
-- Do the same with indexes
-- using elect DBMS_METADATA.GET_DDL('INDEX','KPI_I_1') from DUAL;
END;
/