CREATE OR REPLACE PACKAGE BODY swapops AS /* * Private Section */ /* * GetSwapName */ FUNCTION getswapname ( pname IN metadatapkg.tstring, psuffix IN metadatapkg.tstring := cswapsuffix ) RETURN metadatapkg.tstring IS vretval metadatapkg.tstring; BEGIN IF SUBSTR (pname, -LENGTH (cswapsuffix)) = cswapsuffix THEN vretval := SUBSTR (pname, 1, LENGTH (pname) - LENGTH (cswapsuffix)) || psuffix; ELSIF SUBSTR (pname, -LENGTH (ctempsuffix)) = ctempsuffix THEN vretval := SUBSTR (pname, 1, LENGTH (pname) - LENGTH (ctempsuffix)) || psuffix; ELSE vretval := SUBSTR (pname, 1, 30 - LENGTH (psuffix)) || psuffix; END IF; RETURN vretval; END getswapname; /* * GetRenameString. * * Returns SQL "Rename" string, i.e. * ALTER TABLE "TESTDATA_X" RENAME TO "TESTDATA" * ALTER INDEX "TESTDATA_PK_X" RENAME TO "TESTDATA_PK" * ALTER TABLE "TESTDATA_X" RENAME CONSTRAINT "TESTDATA_PK_X" TO "TESTDATA_PK" */ FUNCTION getrenamestring ( pfromname IN metadatapkg.tstring, ptoname IN metadatapkg.tstring, ptype IN metadatapkg.tstring, ptable IN metadatapkg.tstring := NULL ) RETURN metadatapkg.tdbstring IS vretval metadatapkg.tdbstring; BEGIN IF (ptoname IS NOT NULL) AND (pfromname != ptoname) THEN IF (ptype = metadatapkg.cconstraint) THEN vretval := 'ALTER TABLE ' || metadatapkg.cdoublequote || ptable || metadatapkg.cdoublequote || ' ' || 'RENAME CONSTRAINT ' || metadatapkg.cdoublequote || pfromname || metadatapkg.cdoublequote || ' ' || 'TO ' || metadatapkg.cdoublequote || ptoname || metadatapkg.cdoublequote; ELSE vretval := 'ALTER ' || ptype || ' ' || metadatapkg.cdoublequote || pfromname || metadatapkg.cdoublequote || ' ' || 'RENAME ' || 'TO ' || metadatapkg.cdoublequote || ptoname || metadatapkg.cdoublequote; END IF; END IF; RETURN vretval; END getrenamestring; /* * Reset. * Drops CX_ / CT_ tables. * Renames any CX_ / CT_ indexes / constraints, * that exist in CS_ table to CS_ ones. * Renames any _X / _T triggers */ PROCEDURE RESET (pfullmetaobject IN metadatapkg.tfullmetaobject) IS vtablename metadatapkg.tstring := pfullmetaobject.atable.aname; vddlstring metadatapkg.tdbstring; vfullmetaobject metadatapkg.tfullmetaobject; vreloadmeta BOOLEAN := FALSE; vprocedure metadatapkg.tdbstring := 'Reset'; BEGIN dropobject (getswapname (vtablename, cswapsuffix), metadatapkg.ctable); dropobject (getswapname (vtablename, ctempsuffix), metadatapkg.ctable); /* * Indexes. */ IF (pfullmetaobject.aindexes.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aindexes.FIRST .. pfullmetaobject.aindexes.LAST LOOP IF SUBSTR (pfullmetaobject.aindexes (nindex).aname, -LENGTH (cswapsuffix) ) IN (cswapsuffix, ctempsuffix) THEN vddlstring := getrenamestring (pfullmetaobject.aindexes (nindex).aname, SUBSTR (pfullmetaobject.aindexes (nindex).aname, 1, LENGTH (pfullmetaobject.aindexes (nindex).aname ) - LENGTH (cswapsuffix) ), metadatapkg.cindex ); vreloadmeta := TRUE; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; /* * Constraints. */ IF (pfullmetaobject.aconstraints.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aconstraints.FIRST .. pfullmetaobject.aconstraints.LAST LOOP IF SUBSTR (pfullmetaobject.aconstraints (nindex).aname, -LENGTH (cswapsuffix) ) IN (cswapsuffix, ctempsuffix) THEN vddlstring := getrenamestring (pfullmetaobject.aconstraints (nindex).aname, SUBSTR (pfullmetaobject.aconstraints (nindex).aname, 1, LENGTH (pfullmetaobject.aconstraints (nindex).aname ) - LENGTH (cswapsuffix) ), metadatapkg.cconstraint, vtablename ); vreloadmeta := TRUE; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; /* * RefConstraints. */ IF (pfullmetaobject.arefconstraints.COUNT > 0) THEN FOR nindex IN pfullmetaobject.arefconstraints.FIRST .. pfullmetaobject.arefconstraints.LAST LOOP vreloadmeta := TRUE; END LOOP; END IF; /* * Triggers. */ IF (pfullmetaobject.atriggers.COUNT > 0) THEN FOR nindex IN pfullmetaobject.atriggers.FIRST .. pfullmetaobject.atriggers.LAST LOOP IF SUBSTR (pfullmetaobject.atriggers (nindex).aname, -LENGTH (cswapsuffix) ) IN (cswapsuffix, ctempsuffix) THEN vddlstring := getrenamestring (pfullmetaobject.atriggers (nindex).aname, SUBSTR (pfullmetaobject.atriggers (nindex).aname, 1, LENGTH (pfullmetaobject.atriggers (nindex).aname ) - LENGTH (cswapsuffix) ), metadatapkg.ctrigger, vtablename ); vreloadmeta := TRUE; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; IF vreloadmeta THEN vfullmetaobject := getmeta (vtablename, TRUE); END IF; END RESET; /* * EnableOption */ PROCEDURE enableoption ( pobjectname IN metadatapkg.tstring, pobjecttype IN metadatapkg.tstring, poption IN metadatapkg.tstring ) IS vddlstring metadatapkg.tdbstring; vprocedure metadatapkg.tdbstring := 'enableoption'; BEGIN vddlstring := 'ALTER ' || pobjecttype || ' ' || metadatapkg.cdoublequote || pobjectname || metadatapkg.cdoublequote || ' ' || poption; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END enableoption; /* * ModifyTableDDL */ FUNCTION modifytableddl ( pddl IN metadatapkg.tlongstring, pname IN metadatapkg.tstring, pswapname IN metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'modifytableddl'; BEGIN /* * Change table name to renameTo name in the DDL: */ vretval := REPLACE (pddl, 'CREATE TABLE ' || metadatapkg.cdoublequote || pname || metadatapkg.cdoublequote, 'CREATE TABLE ' || metadatapkg.cdoublequote || pswapname || metadatapkg.cdoublequote ); RETURN vretval; END modifytableddl; /* * ModifyTablePartitionDDL */ FUNCTION modifytablepartitionddl ( pddl IN metadatapkg.tlongstring, ptype IN metadatapkg.tstring, ppartitionclause IN metadatapkg.tlongstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring := pddl; vprocedure metadatapkg.tdbstring := 'modifytablepartitionddl'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at start: ' || vretval ); /* * If currently partitioned, remove existing partition clause */ IF (ptype = metadatapkg.ctabletypepartitioned) AND (INSTR (vretval, ' ' || metadatapkg.cpartitionby) <> 0) THEN vretval := SUBSTR (vretval, 1, INSTR (vretval, ' ' || metadatapkg.cpartitionby, 1, 1) - 1 ); END IF; /* * Create parition clause * CREATE TABLE "TESTDATA_X" --> CREATE TABLE "TESTDATA_X" PARTITION BY... */ vretval := vretval || ' ' || ppartitionclause; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at end: ' || vretval ); RETURN vretval; END modifytablepartitionddl; /* * ModifyIndexDDL */ FUNCTION modifyindexddl ( pddl IN metadatapkg.tlongstring, pname IN metadatapkg.tstring, pswapname IN metadatapkg.tstring, pindexlogging IN metadatapkg.tstring, pparallel IN metadatapkg.tstring, ptable IN metadatapkg.tstring, pswaptable IN metadatapkg.tstring, plogging IN metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'modifyindexddl'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at start: ' || pddl ); /* * Change DDL for index: * INDEX "TESTDATA_PK" ON "TESTDATA" --> INDEX "TESTDATA_PK_X" ON "TESTDATA_X" */ vretval := REPLACE (pddl, 'INDEX ' || metadatapkg.cdoublequote || pname || metadatapkg.cdoublequote || ' ' || 'ON ' || metadatapkg.cdoublequote || ptable || metadatapkg.cdoublequote, 'INDEX ' || metadatapkg.cdoublequote || pswapname || metadatapkg.cdoublequote || ' ' || 'ON ' || metadatapkg.cdoublequote || pswaptable || metadatapkg.cdoublequote ); /* * Enable NOLOGGING option for an index */ IF (pindexlogging = metadatapkg.cyes) AND (plogging = metadatapkg.cno) THEN IF INSTR (vretval, ' ' || metadatapkg.clogging) > 0 THEN vretval := REPLACE (vretval, ' ' || metadatapkg.clogging, ' ' || metadatapkg.cnologging ); ELSE vretval := vretval || ' ' || metadatapkg.cnologging; END IF; END IF; /* * Enable PARALLEL option for an index */ IF NVL (pparallel, '0') IN ('0', '1') THEN IF INSTR (vretval, ' ' || metadatapkg.cnoparallel) > 0 THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cnoparallel, ' ' || metadatapkg.cparallel ); ELSE vretval := vretval || ' ' || metadatapkg.cparallel; END IF; END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at end: ' || vretval ); RETURN vretval; END modifyindexddl; /* * ModifyIndexPartitionDDL */ FUNCTION modifyindexpartitionddl ( pddl IN metadatapkg.tlongstring, ptype IN metadatapkg.tstring, ptabletype IN metadatapkg.tstring, ppartitionclause IN metadatapkg.tstring := NULL ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring := pddl; vprocedure metadatapkg.tdbstring := 'modifyindexpartitionddl'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at start: ' || pddl ); /* * If currently partitioned, remove existing partition clause */ IF (ptabletype = metadatapkg.ctabletypepartitioned) AND (INSTR (vretval, ' ' || metadatapkg.clocalindex) <> 0) THEN vretval := SUBSTR (vretval, 1, INSTR (vretval, ' ' || metadatapkg.clocalindex, 1, 1) ); END IF; /* * Create parition clause * INDEX "TESTDATA_PK_X" --> INDEX "TESTDATA_PK_X" LOCAL */ IF ptype NOT IN (metadatapkg.cunique) THEN vretval := vretval || ' ' || metadatapkg.clocalindex; END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at end: ' || vretval ); RETURN vretval; END modifyindexpartitionddl; /* * ModifyConstraintDDL */ FUNCTION modifyconstraintddl ( pmetaobject IN metadatapkg.tmetaobject, ptable IN metadatapkg.tstring, pswaptable IN metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'modifyconstraintddl'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL at start: ' || pmetaobject.addlstring ); COMMIT; IF (SUBSTR (pmetaobject.aname, 1, LENGTH (metadatapkg.csysprefix)) = metadatapkg.csysprefix ) AND (pmetaobject.addlstring LIKE '% ' || metadatapkg.cnotnull || '%' ) THEN /* * Change DDL for NOT NULL constraint: * ALTER TABLE "TESTDATA" MODIFY "OBJECTTYPE" NOT NULL --> * ALTER TABLE "TESTDATA_X" MODIFY "OBJECTTYPE" NOT NULL */ vretval := REPLACE (pmetaobject.addlstring, 'ALTER TABLE ' || metadatapkg.cdoublequote || ptable || metadatapkg.cdoublequote || ' MODIFY', 'ALTER TABLE ' || metadatapkg.cdoublequote || pswaptable || metadatapkg.cdoublequote || ' MODIFY' ); ELSE /* * Change DDL for constraint: * ALTER TABLE "TESTDATA" ADD CONSTRAINT "TESTDATA_PK" --> * ALTER TABLE "TESTDATA_X" ADD CONSTRAINT "TESTDATA_PK_X" */ vretval := REPLACE (pmetaobject.addlstring, 'ADD CONSTRAINT ' || metadatapkg.cdoublequote || pmetaobject.aname || metadatapkg.cdoublequote, 'ADD CONSTRAINT ' || metadatapkg.cdoublequote || pmetaobject.aswapname || metadatapkg.cdoublequote ); /* * Change DDL for: * - foreign key (FK) or check (CK) constraint * ENABLE --> RELY ENABLE NOVALIDATE * * - primary key (PK) or unique (UN) constraint * USING INDEX --> RELY USING INDEX * ENABLE --> ENABLE NOVALIDATE */ IF pmetaobject.atype IN (metadatapkg.cfkconstype, metadatapkg.cckconstype) THEN IF pmetaobject.arely = metadatapkg.crely THEN IF (pmetaobject.avalidated = metadatapkg.cvalidated) AND (pmetaobject.astatus = metadatapkg.cenabled) AND (INSTR (vretval, ' ' || metadatapkg.cenable) > 0) AND (INSTR (vretval, ' ' || metadatapkg.crely || ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ) = 0 ) THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cenable, ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ); END IF; ELSE IF (pmetaobject.avalidated = metadatapkg.cvalidated) AND (pmetaobject.astatus = metadatapkg.cenabled) AND (INSTR (vretval, ' ' || metadatapkg.cenable) > 0) AND (INSTR (vretval, metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ) = 0 ) THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cenable, ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ); END IF; END IF; ELSIF pmetaobject.atype IN (metadatapkg.cpkconstype, metadatapkg.cunconstype) THEN IF (pmetaobject.arely IS NULL) AND (INSTR (vretval, ' USING INDEX') > 0) AND (INSTR (vretval, ' ' || metadatapkg.crely || ' USING INDEX') = 0 ) THEN vretval := REPLACE (vretval, ' USING INDEX', ' ' || metadatapkg.crely || ' USING INDEX' ); END IF; IF (pmetaobject.avalidated = metadatapkg.cvalidated) AND (pmetaobject.astatus = metadatapkg.cenabled) AND (INSTR (vretval, ' ' || metadatapkg.cenable) > 0) AND (INSTR (vretval, metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ) = 0 ) THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cenable, ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ); END IF; END IF; END IF; -- ALWAYS DO THIS vretval := REPLACE (vretval, -- pmetaobject.addlstring, 'ALTER TABLE ' || metadatapkg.cdoublequote || ptable || metadatapkg.cdoublequote, 'ALTER TABLE ' || metadatapkg.cdoublequote || pswaptable || metadatapkg.cdoublequote ); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' DDL returning: ' || vretval ); COMMIT; RETURN vretval; END modifyconstraintddl; /* * ModifyRefConstraintDDL */ FUNCTION modifyrefconstraintddl (pmetaobject IN metadatapkg.tmetaobject) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'modifyrefconstraintddl'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'DDL at start: ' || pmetaobject.addlstring ); COMMIT; vretval := pmetaobject.addlstring; /* * Change DDL for: * - foreign key (FK) or check (CK) constraint * ENABLE --> RELY ENABLE NOVALIDATE * * - primary key (PK) or unique (UN) constraint * USING INDEX --> RELY USING INDEX * ENABLE --> ENABLE NOVALIDATE */ IF pmetaobject.atype IN (metadatapkg.cfkconstype) THEN IF pmetaobject.arely = metadatapkg.crely THEN IF (pmetaobject.avalidated = metadatapkg.cvalidated) AND (pmetaobject.astatus = metadatapkg.cenabled) AND (INSTR (vretval, ' ' || metadatapkg.cenable) > 0) AND (INSTR (vretval, ' ' || metadatapkg.crely || ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ) = 0 ) THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cenable, ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ); END IF; ELSE IF (pmetaobject.avalidated = metadatapkg.cvalidated) AND (pmetaobject.astatus = metadatapkg.cenabled) AND (INSTR (vretval, ' ' || metadatapkg.cenable) > 0) AND (INSTR (vretval, metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ) = 0 ) THEN vretval := REPLACE (vretval, ' ' || metadatapkg.cenable, ' ' || metadatapkg.cenable || ' ' || metadatapkg.cnovalidate ); END IF; END IF; END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'DDL returning: ' || vretval ); COMMIT; RETURN vretval; END modifyrefconstraintddl; /* * ModifyTriggerDDL */ FUNCTION modifytriggerddl ( pddl IN metadatapkg.tlongstring, pname IN metadatapkg.tstring, pswapname IN metadatapkg.tstring, ptable IN metadatapkg.tstring, pswaptable IN metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; BEGIN vretval := pddl; /* * Remove DDL for ALTER TRIGGER */ vretval := SUBSTR (vretval, 1, INSTR (vretval, 'ALTER TRIGGER') - 1); RETURN vretval; END modifytriggerddl; /* * ModifyGrantDDL */ FUNCTION modifygrantddl ( pddl IN metadatapkg.tlongstring, pname IN metadatapkg.tstring, pswapname IN metadatapkg.tstring, ptable IN metadatapkg.tstring, pswaptable IN metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; BEGIN /* * Change DDL for Grant * ON "TESTDATA" --> ON "TESTDATA_X" */ vretval := REPLACE (pddl, ptable || ' TO', pswaptable || ' TO'); RETURN vretval; END modifygrantddl; /* * CreateTable */ PROCEDURE createtable ( pfullmetaobject IN metadatapkg.tfullmetaobject, plogging IN metadatapkg.tstring := metadatapkg.cno ) IS vddlstring metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'CreateTable'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; /* * Reset. */ RESET (pfullmetaobject); vddlstring := modifytableddl (pfullmetaobject.atable.addlstring, pfullmetaobject.atable.aname, pfullmetaobject.atable.aswapname ); IF (pfullmetaobject.apartitionclause IS NOT NULL) THEN vddlstring := modifytablepartitionddl (vddlstring, pfullmetaobject.atable.atype, pfullmetaobject.apartitionclause ); END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; /* * Enable NOLOGGING option */ IF (pfullmetaobject.atable.alogging = metadatapkg.cyes) AND (plogging = metadatapkg.cno) THEN enableoption (pfullmetaobject.atable.aswapname, metadatapkg.ctable, metadatapkg.cnologging ); END IF; /* * Enable PARALLEL option */ IF NVL (pfullmetaobject.atable.aparallel, '0') IN ('0', '1') THEN enableoption (pfullmetaobject.atable.aswapname, metadatapkg.ctable, metadatapkg.cparallel ); END IF; END createtable; /* * CreateIndexes */ PROCEDURE createindexes ( pfullmetaobject IN metadatapkg.tfullmetaobject, plogging IN metadatapkg.tstring := metadatapkg.cno ) IS vddlstring metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'CreateIndexes'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; IF (pfullmetaobject.aindexes.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aindexes.FIRST .. pfullmetaobject.aindexes.LAST LOOP vddlstring := modifyindexddl (pfullmetaobject.aindexes (nindex).addlstring, pfullmetaobject.aindexes (nindex).aname, pfullmetaobject.aindexes (nindex).aswapname, pfullmetaobject.aindexes (nindex).alogging, pfullmetaobject.aindexes (nindex).aparallel, pfullmetaobject.atable.aname, pfullmetaobject.atable.aswapname, plogging ); IF (pfullmetaobject.apartitionclause IS NOT NULL) THEN vddlstring := modifyindexpartitionddl (vddlstring, pfullmetaobject.aindexes (nindex).atype, pfullmetaobject.atable.atype, pfullmetaobject.apartitionclause ); END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END LOOP; END IF; END createindexes; /* * CreateConstraints */ PROCEDURE createconstraints (pfullmetaobject IN metadatapkg.tfullmetaobject) IS vddlstring metadatapkg.tlongstring; vmetaobject metadatapkg.tmetaobject; vprocedure metadatapkg.tdbstring := 'CreateConstraints'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; IF (pfullmetaobject.aconstraints.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aconstraints.FIRST .. pfullmetaobject.aconstraints.LAST LOOP vmetaobject := pfullmetaobject.aconstraints (nindex); COMMIT; vddlstring := modifyconstraintddl (vmetaobject, pfullmetaobject.atable.aname, pfullmetaobject.atable.aswapname ); COMMIT; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; /* * Only Non Foreign Key CreateConstraints * Foreign keys are added in the wrapper code for all the tables, not each one */ IF vmetaobject.atype <> metadatapkg.cfkconstype THEN EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; END createconstraints; /* * CreateRefConstraints */ PROCEDURE createrefconstraints ( pfullmetaobject IN metadatapkg.tfullmetaobject ) IS vddlstring metadatapkg.tlongstring; vmetaobject metadatapkg.tmetaobject; vprocedure metadatapkg.tdbstring := 'CreateRefConstraints'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; IF (pfullmetaobject.arefconstraints.COUNT > 0) THEN FOR nindex IN pfullmetaobject.arefconstraints.FIRST .. pfullmetaobject.arefconstraints.LAST LOOP vmetaobject := pfullmetaobject.arefconstraints (nindex); vddlstring := modifyrefconstraintddl (vmetaobject); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END LOOP; END IF; END createrefconstraints; /* * CreateTriggers */ PROCEDURE createtriggers ( pfullmetaobject IN metadatapkg.tfullmetaobject, plogging IN metadatapkg.tstring := metadatapkg.cno ) IS vddlstring metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'CreateTriggers'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; IF (pfullmetaobject.atriggers.COUNT > 0) THEN FOR nindex IN pfullmetaobject.atriggers.FIRST .. pfullmetaobject.atriggers.LAST LOOP vddlstring := modifytriggerddl (pfullmetaobject.atriggers (nindex).addlstring, pfullmetaobject.atriggers (nindex).aname, pfullmetaobject.atriggers (nindex).aswapname, pfullmetaobject.atable.aname, pfullmetaobject.atable.aswapname ); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END LOOP; END IF; END createtriggers; /* * GrantTablePrivs */ PROCEDURE granttableprivs (pfullmetaobject IN metadatapkg.tfullmetaobject) IS vddlstring metadatapkg.tlongstring; vprocedure metadatapkg.tdbstring := 'GrantTablePrivs'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aswapname || ')'; IF (pfullmetaobject.aprivileges.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aprivileges.FIRST .. pfullmetaobject.aprivileges.LAST LOOP vddlstring := modifygrantddl (pfullmetaobject.aprivileges (nindex).addlstring, pfullmetaobject.aprivileges (nindex).aname, pfullmetaobject.aprivileges (nindex).aswapname, pfullmetaobject.atable.aname, pfullmetaobject.atable.aswapname ); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END LOOP; END IF; END granttableprivs; /* * RenameTable */ PROCEDURE renametable ( pfullmetaobject IN metadatapkg.tfullmetaobject, pfromsuffix IN metadatapkg.tstring := NULL, ptosuffix IN metadatapkg.tstring := NULL ) IS vddlstring metadatapkg.tdbstring; vfromname metadatapkg.tstring; vtoname metadatapkg.tstring; vprocedure metadatapkg.tdbstring := 'RenameTable'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aname || ')'; -- "From" name IF pfromsuffix IS NULL THEN vfromname := pfullmetaobject.atable.aname; ELSIF pfromsuffix = cswapsuffix THEN vfromname := pfullmetaobject.atable.aswapname; ELSE vfromname := getswapname (pfullmetaobject.atable.aname, pfromsuffix); END IF; -- "To" name IF ptosuffix IS NULL THEN vtoname := pfullmetaobject.atable.aname; ELSIF pfromsuffix = cswapsuffix THEN vtoname := pfullmetaobject.atable.aswapname; ELSE vtoname := getswapname (pfullmetaobject.atable.aname, ptosuffix); END IF; vddlstring := getrenamestring (vfromname, vtoname, metadatapkg.ctable); IF vddlstring IS NOT NULL THEN locktable (vfromname); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END renametable; /* * RenameIndexes */ PROCEDURE renameindexes ( pfullmetaobject IN metadatapkg.tfullmetaobject, pfromsuffix IN metadatapkg.tstring := NULL, ptosuffix IN metadatapkg.tstring := NULL ) IS vddlstring metadatapkg.tdbstring; vfromname metadatapkg.tstring; vtoname metadatapkg.tstring; vprocedure metadatapkg.tdbstring := 'RenameIndexes'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aname || ')'; IF (pfullmetaobject.aindexes.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aindexes.FIRST .. pfullmetaobject.aindexes.LAST LOOP -- "From" name IF (pfromsuffix IS NULL) THEN vfromname := pfullmetaobject.aindexes (nindex).aname; ELSIF pfromsuffix = cswapsuffix THEN vfromname := pfullmetaobject.aindexes (nindex).aswapname; ELSE vfromname := getswapname (pfullmetaobject.aindexes (nindex).aname, pfromsuffix ); END IF; -- "To" name IF (ptosuffix IS NULL) THEN vtoname := pfullmetaobject.aindexes (nindex).aname; ELSIF ptosuffix = cswapsuffix THEN vtoname := pfullmetaobject.aindexes (nindex).aswapname; ELSE vtoname := getswapname (pfullmetaobject.aindexes (nindex).aname, ptosuffix ); END IF; vddlstring := getrenamestring (vfromname, vtoname, metadatapkg.cindex); IF vddlstring IS NOT NULL THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; END renameindexes; /* * RenameConstraints */ PROCEDURE renameconstraints ( pfullmetaobject IN metadatapkg.tfullmetaobject, pfromsuffix IN metadatapkg.tstring := NULL, ptosuffix IN metadatapkg.tstring := NULL ) IS vddlstring metadatapkg.tdbstring; vfromname metadatapkg.tstring; vtoname metadatapkg.tstring; vtable metadatapkg.tstring; vprocedure metadatapkg.tdbstring := 'RenameConstraints'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aname || ')'; IF (pfullmetaobject.aconstraints.COUNT > 0) THEN FOR nindex IN pfullmetaobject.aconstraints.FIRST .. pfullmetaobject.aconstraints.LAST LOOP IF (SUBSTR (pfullmetaobject.aconstraints (nindex).aname, 1, LENGTH (metadatapkg.csysprefix) ) = metadatapkg.csysprefix ) /* Related FK constraint */ OR pfullmetaobject.aconstraints (nindex).atablename <> vtable /*** ADDITION - SKIP ALL SYSTEM CONSTRAINTS AND (pfullmetaobject.aconstraints (nindex).aswapname IS NULL ) -- AND (pfullmetaobject.aconstraints (nindex).addlstring LIKE -- '% ' || metadatapkg.cnotnull || '%' -- ) ***ADDITION END */ THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' Skipping ' || pfullmetaobject.aconstraints (nindex).aname ); COMMIT; /* * Skip NOT NULL constraints: no need to rename them. * ADDITION- SKIP ALL SYSTEM CONSTRAINTS */ NULL; ELSE -- "From" name IF pfromsuffix IS NULL THEN vfromname := pfullmetaobject.aconstraints (nindex).aname; vtable := pfullmetaobject.atable.aname; ELSIF pfromsuffix = cswapsuffix THEN vfromname := pfullmetaobject.aconstraints (nindex).aswapname; vtable := pfullmetaobject.atable.aswapname; ELSE vfromname := getswapname (pfullmetaobject.aconstraints (nindex).aname, pfromsuffix ); vtable := getswapname (pfullmetaobject.atable.aname, pfromsuffix); END IF; -- "To" name IF ptosuffix IS NULL THEN vtoname := pfullmetaobject.aconstraints (nindex).aname; ELSIF ptosuffix = cswapsuffix THEN vtoname := pfullmetaobject.aconstraints (nindex).aswapname; ELSE vtoname := getswapname (pfullmetaobject.aconstraints (nindex).aname, ptosuffix ); END IF; vddlstring := getrenamestring (vfromname, vtoname, metadatapkg.cconstraint, vtable ); IF vddlstring IS NOT NULL THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END IF; END LOOP; END IF; END renameconstraints; /* * RenameTriggers */ PROCEDURE renametriggers ( pfullmetaobject IN metadatapkg.tfullmetaobject, pfromsuffix IN metadatapkg.tstring := NULL, ptosuffix IN metadatapkg.tstring := NULL ) IS vddlstring metadatapkg.tdbstring; vfromname metadatapkg.tstring; vtoname metadatapkg.tstring; vprocedure metadatapkg.tdbstring := 'RenameTriggers'; BEGIN vprocedure := vprocedure || '(' || pfullmetaobject.atable.aname || ')'; IF (pfullmetaobject.atriggers.COUNT > 0) THEN FOR nindex IN pfullmetaobject.atriggers.FIRST .. pfullmetaobject.atriggers.LAST LOOP -- "From" name IF (pfromsuffix IS NULL) THEN vfromname := pfullmetaobject.atriggers (nindex).aname; ELSIF pfromsuffix = cswapsuffix THEN vfromname := pfullmetaobject.atriggers (nindex).aswapname; ELSE vfromname := getswapname (pfullmetaobject.atriggers (nindex).aname, pfromsuffix ); END IF; -- "To" name IF (ptosuffix IS NULL) THEN vtoname := pfullmetaobject.atriggers (nindex).aname; ELSIF ptosuffix = cswapsuffix THEN vtoname := pfullmetaobject.atriggers (nindex).aswapname; ELSE vtoname := getswapname (pfullmetaobject.atriggers (nindex).aname, ptosuffix ); END IF; vddlstring := getrenamestring (vfromname, vtoname, metadatapkg.ctrigger); IF vddlstring IS NOT NULL THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END LOOP; END IF; END renametriggers; /* * BuildInsertString */ FUNCTION buildinsertstring ( psource metadatapkg.tstring, ptarget metadatapkg.tstring, pwhere metadatapkg.tlongstring := NULL ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; vwhere metadatapkg.tlongstring := LTRIM (RTRIM (pwhere)); vkey metadatapkg.tlongstring := 'WHERE '; BEGIN IF vwhere IS NOT NULL THEN IF UPPER (SUBSTR (vwhere, 1, LENGTH (vkey))) != vkey THEN vwhere := vkey || vwhere; END IF; END IF; vretval := 'INSERT /*+ APPEND(T) PARALLEL(T) */ INTO ' || ptarget || ' T ' || 'SELECT /*+ PARALLEL(S) */ * FROM ' || psource || ' S ' || vwhere; RETURN vretval; END buildinsertstring; /* * RestoreParallel */ PROCEDURE restoreparallel ( pname IN metadatapkg.tstring, ptype IN metadatapkg.tstring, pparallel IN metadatapkg.tstring ) IS vddlstring metadatapkg.tdbstring; vprocedure metadatapkg.tdbstring := 'restoreparallel'; BEGIN CASE WHEN pparallel = metadatapkg.cdefault THEN NULL; WHEN NVL (pparallel, '0') IN ('0', '1') THEN vddlstring := 'ALTER ' || ptype || ' ' || metadatapkg.cdoublequote || pname || metadatapkg.cdoublequote || ' ' || metadatapkg.cnoparallel; ELSE vddlstring := 'ALTER ' || ptype || ' ' || metadatapkg.cdoublequote || pname || metadatapkg.cdoublequote || ' ' || metadatapkg.cparallel || ' ' || pparallel; END CASE; IF vddlstring IS NOT NULL THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing DDL : ' || vddlstring ); COMMIT; EXECUTE IMMEDIATE vddlstring; END IF; END restoreparallel; /* * Public Section */ /* * GetMeta */ FUNCTION getmeta (pname IN metadatapkg.tstring, pforce IN BOOLEAN := FALSE) RETURN metadatapkg.tfullmetaobject IS vfullmetaobject metadatapkg.tfullmetaobject; vprocedure metadatapkg.tdbstring := 'GetMeta'; BEGIN vprocedure := vprocedure || '(' || pname || ')'; /* * Get full meta object from metadata repository. */ vfullmetaobject := metadatapkg.getmeta (pname, pforce); /* * Traverse it and populate renameTo property if it's empty. */ IF vfullmetaobject.atable.aswapname IS NULL THEN -- table vfullmetaobject.atable.aswapname := getswapname (vfullmetaobject.atable.aname); -- indexes IF (vfullmetaobject.aindexes.COUNT > 0) THEN FOR nindex IN vfullmetaobject.aindexes.FIRST .. vfullmetaobject.aindexes.LAST LOOP vfullmetaobject.aindexes (nindex).aswapname := getswapname (vfullmetaobject.aindexes (nindex).aname); END LOOP; END IF; -- constraints IF (vfullmetaobject.aconstraints.COUNT > 0) THEN FOR nindex IN vfullmetaobject.aconstraints.FIRST .. vfullmetaobject.aconstraints.LAST LOOP vfullmetaobject.aconstraints (nindex).aswapname := getswapname (vfullmetaobject.aconstraints (nindex).aname); END LOOP; END IF; -- triggers IF (vfullmetaobject.atriggers.COUNT > 0) THEN FOR nindex IN vfullmetaobject.atriggers.FIRST .. vfullmetaobject.atriggers.LAST LOOP vfullmetaobject.atriggers (nindex).aswapname := getswapname (vfullmetaobject.atriggers (nindex).aname); END LOOP; END IF; /* * Put full meta object into metadata repository. */ metadatapkg.setmeta (pname, vfullmetaobject); END IF; RETURN vfullmetaobject; END getmeta; /* * CreateObjects */ PROCEDURE createobjects ( pname IN metadatapkg.tstring, ptype IN metadatapkg.tstring, plogging IN metadatapkg.tstring := metadatapkg.cno, ppartitionclause IN metadatapkg.tstring := NULL ) IS vprocedure metadatapkg.tdbstring := 'CreateObjects'; vfullmetaobject metadatapkg.tfullmetaobject; BEGIN vprocedure := vprocedure || '(' || pname || ')'; vfullmetaobject := getmeta (pname); vfullmetaobject.apartitionclause := ppartitionclause; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' Creating ' || CASE WHEN ptype = 'TABLE' THEN LOWER (ptype) || ' ' || vfullmetaobject.atable.aswapname ELSE LOWER (ptype) || '(s) for table ' || vfullmetaobject.atable.aswapname END ); COMMIT; CASE ptype WHEN metadatapkg.ctable THEN createtable (vfullmetaobject, UPPER (plogging)); granttableprivs (vfullmetaobject); WHEN metadatapkg.cindex THEN createindexes (vfullmetaobject, UPPER (plogging)); WHEN metadatapkg.cconstraint THEN createconstraints (vfullmetaobject); WHEN metadatapkg.ctrigger THEN createtriggers (vfullmetaobject); WHEN metadatapkg.crefconstraint THEN createrefconstraints (vfullmetaobject); ELSE NULL; END CASE; END createobjects; /* * RenameObjects */ PROCEDURE renameobjects ( pname IN metadatapkg.tstring, ptype IN metadatapkg.tstring, pfromsuffix IN metadatapkg.tstring, ptosuffix IN metadatapkg.tstring ) IS vprocedure metadatapkg.tdbstring := 'RenameObjects'; vfullmetaobject metadatapkg.tfullmetaobject; BEGIN vfullmetaobject := getmeta (pname); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' Renaming ' || CASE WHEN ptype = 'TABLE' THEN LOWER (ptype) || ' ' || pname ELSE LOWER (ptype) || '(s) for table ' || pname END ); COMMIT; CASE ptype WHEN metadatapkg.ctable THEN renametable (vfullmetaobject, pfromsuffix, ptosuffix); WHEN metadatapkg.cindex THEN renameindexes (vfullmetaobject, pfromsuffix, ptosuffix); WHEN metadatapkg.cconstraint THEN renameconstraints (vfullmetaobject, pfromsuffix, ptosuffix); WHEN metadatapkg.ctrigger THEN renametriggers (vfullmetaobject, pfromsuffix, ptosuffix); ELSE NULL; END CASE; END renameobjects; /* * ExecuteInsert */ PROCEDURE executeinsert ( pname IN metadatapkg.tstring, pwhere IN metadatapkg.tstring := NULL ) IS vsqlstring metadatapkg.tlongstring; vfullmetaobject metadatapkg.tfullmetaobject; vprocedure metadatapkg.tdbstring := 'ExecuteInsert'; BEGIN vprocedure := vprocedure || '(' || UPPER (pname) || ')'; vfullmetaobject := getmeta (pname); vsqlstring := buildinsertstring (vfullmetaobject.atable.aname, vfullmetaobject.atable.aswapname, pwhere ); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL : ' || vsqlstring ); COMMIT; EXECUTE IMMEDIATE vsqlstring; COMMIT; END executeinsert; /* * ExecuteSQL */ PROCEDURE executesql ( psqlstring IN metadatapkg.tlongstring, p_signal_complete_fl IN CHAR := metadatapkg.cno ) IS vprocedure metadatapkg.tstring := 'ExecuteSQL'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL : ' || psqlstring ); COMMIT; EXECUTE IMMEDIATE psqlstring; COMMIT; IF p_signal_complete_fl = metadatapkg.cyes THEN jobspkg.signalcompletion ( 'SwapOps.executesql(''' || psqlstring || ''',''' || metadatapkg.cyes || ''')', '* executesql job completed' ); END IF; END executesql; /* * SwapObjects */ PROCEDURE swapobjects ( pname IN metadatapkg.tstring, pdrop IN metadatapkg.tstring := metadatapkg.cno ) IS vprocedure metadatapkg.tdbstring := 'SwapObjects'; vdrop metadatapkg.tstring := NVL (UPPER (pdrop), metadatapkg.cno); BEGIN vprocedure := vprocedure || '(' || UPPER (pname) || ')'; /* * Step 1: Drop old table (with no suffix)? */ IF vdrop = metadatapkg.cyes THEN dropobject (pname, 'TABLE'); END IF; /* * Step 2: Indexes and Constraints. * "No suffix" indexes --> "_T" indexes * "No suffix" constraints --> "_T" constraints * "_X" indexes --> "No suffix" indexes * "_X_" constraints --> "No suffix" constraints */ IF vdrop != metadatapkg.cyes THEN renameobjects (pname, metadatapkg.cindex, NULL, ctempsuffix); renameobjects (pname, metadatapkg.cconstraint, NULL, ctempsuffix); renameobjects (pname, metadatapkg.ctrigger, NULL, ctempsuffix); END IF; renameobjects (pname, metadatapkg.cindex, cswapsuffix, NULL); renameobjects (pname, metadatapkg.cconstraint, cswapsuffix, NULL); -- renameobjects (pname, metadatapkg.ctrigger, cswapsuffix, NULL); /* * Step 3: Tables * "No suffix" table --> "_T" table * "_X" table --> "No suffix" table */ IF vdrop != metadatapkg.cyes THEN renameobjects (pname, metadatapkg.ctable, NULL, ctempsuffix); END IF; renameobjects (pname, metadatapkg.ctable, cswapsuffix, NULL); END swapobjects; /* * RestoreSettings * * Restore original LOGGING / NOLOGGING * and PARALLEL / NOPARALLEL options for table and indexes. */ PROCEDURE restoresettings ( pname IN metadatapkg.tstring, plogging IN metadatapkg.tstring := metadatapkg.cno, pindexlogging IN metadatapkg.tstring := metadatapkg.cno ) IS vddlstring metadatapkg.tdbstring; vfullmetaobject metadatapkg.tfullmetaobject; vprocedure metadatapkg.tdbstring := 'RestoreSettings'; BEGIN vprocedure := vprocedure || '(' || UPPER (pname) || ')'; vfullmetaobject := getmeta (pname); /* * Table. */ IF (vfullmetaobject.atable.alogging = metadatapkg.cyes) AND (UPPER (plogging) = metadatapkg.cno) THEN enableoption (vfullmetaobject.atable.aname, metadatapkg.ctable, metadatapkg.clogging ); END IF; restoreparallel (vfullmetaobject.atable.aname, metadatapkg.ctable, vfullmetaobject.atable.aparallel ); /* * Indexes */ IF (vfullmetaobject.aindexes.COUNT > 0) THEN FOR nindex IN vfullmetaobject.aindexes.FIRST .. vfullmetaobject.aindexes.LAST LOOP IF (vfullmetaobject.aindexes (nindex).alogging = metadatapkg.cyes ) AND (UPPER (pindexlogging) = metadatapkg.cno) THEN enableoption (vfullmetaobject.aindexes (nindex).aname, metadatapkg.cindex, metadatapkg.clogging ); END IF; restoreparallel (vfullmetaobject.aindexes (nindex).aname, metadatapkg.cindex, vfullmetaobject.aindexes (nindex).aparallel ); END LOOP; END IF; END restoresettings; /* * GetPartitionString */ FUNCTION getpartitionstring ( cparameter_group_cd_prttn operation_parameter.parameter_group_cd%TYPE := cparameter_group_cd_prttndflt ) RETURN metadatapkg.tlongstring IS vprocedure metadatapkg.tdbstring := 'getpartitionstring'; vretval metadatapkg.tlongstring; /*************************************************************************** ** Get parameters containing partition clause ***************************************************************************/ CURSOR c_partition_strings IS SELECT parameter_value, parameter_cd FROM operation_parameter WHERE operation_cd = l_operation_cd AND parameter_group_cd = cparameter_group_cd_prttn AND parameter_cd LIKE cparameter_cd_prttnbase || '%' ORDER BY parameter_id ASC; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Compiling and returning partition string.' ); FOR r_partition_string IN c_partition_strings LOOP vretval := vretval || r_partition_string.parameter_value; END LOOP; RETURN vretval; END getpartitionstring; /* * BuildPartitionString_Month */ FUNCTION buildpartitionstring_month ( pstartdaterange DATE, penddaterange DATE, ppartitionprefix metadatapkg.tstring, pcolumn metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vprocedure metadatapkg.tdbstring := 'buildpartitionstring_month'; vretval VARCHAR2 (30000); --save space for complete DDL vcounterdate DATE := pstartdaterange; vtempstring metadatapkg.tstring := 'sagreplacedate'; vlessthanclause metadatapkg.tlongstring := ' VALUES LESS THAN (TO_DATE(''' || vtempstring || ''', ''SYYYY-MM-DD HH24:MI:SS'')),'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Building partition string...' ); vretval := 'PARTITION BY RANGE (' || pcolumn || ') ('; /* * PreRange */ vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM') || 'PRE '; vretval := vretval || vlessthanclause; vretval := REPLACE (vretval, vtempstring, TO_CHAR (vcounterdate, 'YYYY-MM') || '-01 00:00:00' ); /* * Range */ WHILE vcounterdate < penddaterange LOOP vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM'); vretval := vretval || vlessthanclause; vretval := REPLACE (vretval, vtempstring, TO_CHAR (ADD_MONTHS (vcounterdate, 1), 'YYYY-MM') || '-01 00:00:00' ); vcounterdate := ADD_MONTHS (vcounterdate, 1); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' String :' || vretval ); END LOOP; /* * PostRange */ vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM') || 'POST VALUES LESS THAN (MAXVALUE)' || operation_mgmt.crlf || ')'; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Partition string built.' ); RETURN vretval; EXCEPTION WHEN cxora_string_buffer_too_small THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_error, cxora_prtn_buffer_error_string ); COMMIT; raise_application_error (operation_mgmt.l_default_error_code, cxora_prtn_buffer_error_string ); WHEN OTHERS THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_error, 'Exception occurred: ' || SQLERRM ); COMMIT; RAISE; END buildpartitionstring_month; PROCEDURE updatepartitionstring_month ( pstartdaterange DATE, penddaterange DATE, ppartitionprefix metadatapkg.tstring := 'PARTITION PART_', pcolumn metadatapkg.tstring := 'CTRL_INSERT_DT', pparameter_group_cd_prttn operation_parameter.parameter_group_cd%TYPE := cparameter_group_cd_prttndflt ) IS cprocedure metadatapkg.tdbstring := 'updatepartitionstring_month'; vpartitionclause metadatapkg.tlongstring; vpartitionclause_part metadatapkg.tdblongstring; cparameter_dscr metadatapkg.tstring := 'Partition clause'; vstring_counter INT := 1; BEGIN operation_mgmt.log_operation_event (l_operation_cd, cprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Updating partition string for ' || pparameter_group_cd_prttn ); vpartitionclause := swapops.buildpartitionstring_month (pstartdaterange, penddaterange, ppartitionprefix, pcolumn ); DELETE FROM operation_parameter WHERE parameter_group_cd = pparameter_group_cd_prttn AND parameter_cd LIKE cparameter_cd_prttnbase || '%'; vpartitionclause_part := SUBSTR (vpartitionclause, 1, 4000); WHILE vpartitionclause_part IS NOT NULL LOOP INSERT INTO operation_parameter (operation_cd, parameter_group_cd, parameter_cd, parameter_dscr, parameter_value ) VALUES (l_operation_cd, pparameter_group_cd_prttn, cparameter_cd_prttnbase || vstring_counter, cparameter_dscr, vpartitionclause_part ); vpartitionclause := SUBSTR (vpartitionclause, 4001); vpartitionclause_part := SUBSTR (vpartitionclause, 1, 4000); vstring_counter := vstring_counter + 1; END LOOP; END updatepartitionstring_month; /* * BuildPartitionString_Month */ FUNCTION buildpartitionstring_day ( pstartdaterange DATE, penddaterange DATE, ppartitionprefix metadatapkg.tstring, pcolumn metadatapkg.tstring ) RETURN metadatapkg.tlongstring IS vprocedure metadatapkg.tdbstring := 'buildpartitionstring_day'; vretval VARCHAR2 (30000); --save space for complete DDL vcounterdate DATE := pstartdaterange; vtempstring metadatapkg.tstring := 'sagreplacedate'; vlessthanclause metadatapkg.tlongstring := ' VALUES LESS THAN (TO_DATE(''' || vtempstring || ''', ''SYYYY-MM-DD HH24:MI:SS'')),'; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Building partition string...' ); vretval := 'PARTITION BY RANGE (' || pcolumn || ') ('; /* * PreRange */ vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM') || TO_CHAR (vcounterdate, 'DD') || 'PRE '; vretval := vretval || vlessthanclause; vretval := REPLACE (vretval, vtempstring, TO_CHAR (vcounterdate, 'YYYY-MM-DD') || ' 00:00:00' ); /* * Range */ WHILE vcounterdate < penddaterange LOOP vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM') || TO_CHAR (vcounterdate, 'DD'); vretval := vretval || vlessthanclause; vretval := REPLACE (vretval, vtempstring, TO_CHAR ((vcounterdate + 1), 'YYYY-MM-DD') || ' 00:00:00' ); vcounterdate := vcounterdate + 1; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, ' String :' || vretval ); END LOOP; /* * PostRange */ vretval := vretval || operation_mgmt.crlf || ppartitionprefix || TO_CHAR (vcounterdate, 'YYYY') || TO_CHAR (vcounterdate, 'MM') || TO_CHAR (vcounterdate, 'DD') || 'POST VALUES LESS THAN (MAXVALUE)' || operation_mgmt.crlf || ')'; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Partition string built.' ); RETURN vretval; EXCEPTION WHEN cxora_string_buffer_too_small THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_error, cxora_prtn_buffer_error_string ); COMMIT; raise_application_error (operation_mgmt.l_default_error_code, cxora_prtn_buffer_error_string ); WHEN OTHERS THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_error, 'Exception occurred: ' || SQLERRM ); COMMIT; RAISE; END buildpartitionstring_day; PROCEDURE updatepartitionstring_day ( pstartdaterange DATE, penddaterange DATE, ppartitionprefix metadatapkg.tstring := 'PARTITION PART_', pcolumn metadatapkg.tstring := 'CTRL_INSERT_DT', pparameter_group_cd_prttn operation_parameter.parameter_group_cd%TYPE := cparameter_group_cd_prttndflt ) IS cprocedure metadatapkg.tdbstring := 'updatepartitionstring_day'; vpartitionclause metadatapkg.tlongstring; vpartitionclause_part metadatapkg.tdblongstring; cparameter_dscr metadatapkg.tstring := 'Partition clause'; vstring_counter INT := 1; BEGIN operation_mgmt.log_operation_event (l_operation_cd, cprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Updating partition string for ' || pparameter_group_cd_prttn ); vpartitionclause := swapops.buildpartitionstring_day (pstartdaterange, penddaterange, ppartitionprefix, pcolumn ); DELETE FROM operation_parameter WHERE parameter_group_cd = pparameter_group_cd_prttn AND parameter_cd LIKE cparameter_cd_prttnbase || '%'; vpartitionclause_part := SUBSTR (vpartitionclause, 1, 4000); WHILE vpartitionclause_part IS NOT NULL LOOP INSERT INTO operation_parameter (operation_cd, parameter_group_cd, parameter_cd, parameter_dscr, parameter_value ) VALUES (l_operation_cd, pparameter_group_cd_prttn, cparameter_cd_prttnbase || vstring_counter, cparameter_dscr, vpartitionclause_part ); vpartitionclause := SUBSTR (vpartitionclause, 4001); vpartitionclause_part := SUBSTR (vpartitionclause, 1, 4000); vstring_counter := vstring_counter + 1; END LOOP; END updatepartitionstring_day; /* * PartitionTable */ PROCEDURE partitiontable ( ptablename IN metadatapkg.tstring, ppartitionclause IN metadatapkg.tlongstring := NULL, pparameter_group_cd_prttn operation_parameter.parameter_group_cd%TYPE := cparameter_group_cd_prttndflt, plogging IN metadatapkg.tstring := metadatapkg.cno, pindexlogging IN metadatapkg.tstring := metadatapkg.cno, pdrop IN metadatapkg.tstring := metadatapkg.cno ) IS vprocedure metadatapkg.tdbstring := 'PartitionTable'; vpartitionclause metadatapkg.tlongstring := ppartitionclause; BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'START: Partitioning table ' || ptablename ); IF vpartitionclause IS NULL THEN vpartitionclause := getpartitionstring (pparameter_group_cd_prttn); END IF; /*************************************************************************** ** VALIDATE operation parameters ***************************************************************************/ IF vpartitionclause IS NULL THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_error, vprocedure || ' parameters not loaded properly.' || operation_mgmt.crlf || 'Ensure a value for "' || cparameter_cd_prttnbase || '" is populated in the OPERATION_PARAMETER table.' ); COMMIT; RETURN; END IF; vprocedure := vprocedure || '(' || UPPER (ptablename) || ')'; locktable (ptablename); /* * Step 1: Create swap (target) table. */ createobjects (ptablename, metadatapkg.ctable, plogging, vpartitionclause ); /* * Step 2: Insert data into swap table using table * name pName and WHERE clause pWhere. */ executeinsert (ptablename, pwhere => NULL); /* * Step 3: Create indexes on swap table. */ createobjects (ptablename, metadatapkg.cindex, pindexlogging, vpartitionclause ); /* * Step 4: Create constraints on swap table. */ createobjects (ptablename, metadatapkg.cconstraint); /* * Step 5: Lock tables and swap them. */ swapobjects (ptablename, pdrop); /* * Step 6: Create triggers on new table. */ createobjects (ptablename, metadatapkg.ctrigger); /* * Step 7: Recreate referential constraints on foreign key tables. * This step is done as a wrapper for all the tables, not each one */ createobjects (ptablename, metadatapkg.crefconstraint); /* * Step 8: Restore original settings. */ restoresettings (ptablename, plogging, pindexlogging); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'END: Partitioning table ' || ptablename ); END partitiontable; /* * BuildExchangeString */ FUNCTION buildexchangestring ( psource metadatapkg.tstring, ptarget metadatapkg.tstring, ppartitionname IN metadatapkg.tlongstring, pupdateindexes IN metadatapkg.tstring := metadatapkg.cyes ) RETURN metadatapkg.tlongstring IS vretval metadatapkg.tlongstring; BEGIN vretval := 'alter table ' || psource || ' exchange partition ' || ppartitionname || ' with table ' || ptarget; IF pupdateindexes = metadatapkg.cyes THEN vretval := vretval || ' ' || metadatapkg.cupdateindexes; END IF; RETURN vretval; END buildexchangestring; /* * ExecutePartitionExchange */ PROCEDURE executepartitionexchange ( psourcename IN metadatapkg.tstring, ptargetname IN metadatapkg.tstring, ppartitionname IN metadatapkg.tlongstring, pupdateindexes IN metadatapkg.tstring := metadatapkg.cyes ) IS vsqlstring metadatapkg.tlongstring; vfullmetaobject metadatapkg.tfullmetaobject; vprocedure metadatapkg.tdbstring := 'ExecutePartitionExchange'; BEGIN vsqlstring := buildexchangestring (psourcename, ptargetname, ppartitionname, pupdateindexes ); operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL : ' || vsqlstring ); COMMIT; EXECUTE IMMEDIATE vsqlstring; COMMIT; END executepartitionexchange; /* * ExchangePartition */ PROCEDURE exchangepartition ( psourcename IN metadatapkg.tstring, ptargetname IN metadatapkg.tstring, ppartitionname IN metadatapkg.tlongstring, ptargetdblink IN metadatapkg.tdbstring := NULL, plogging IN metadatapkg.tstring := metadatapkg.cno, pupdateindexes IN metadatapkg.tstring := metadatapkg.cyes ) IS vprocedure metadatapkg.tdbstring := 'ExchangePartition'; vfullmetaobject metadatapkg.tfullmetaobject; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN BEGIN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, 'Exchanging partition ' || ppartitionname || ' from ' || psourcename || ' to ' || ptargetname || '.' ); COMMIT; END; locktable (psourcename, 'EXCLUSIVE', metadatapkg.cno); locktable (ptargetname, 'EXCLUSIVE', metadatapkg.cno); vfullmetaobject := getmeta (psourcename); /* * Step 1: Create temp exchange table. */ createobjects (psourcename, metadatapkg.ctable, plogging, ' '); createobjects (psourcename, metadatapkg.cconstraint, plogging, ' '); /* * Step 2: Exchange source into temp exchange table */ executepartitionexchange (psourcename, vfullmetaobject.atable.aswapname, ppartitionname, pupdateindexes ); /* * Step 3: Exchange target from temp exchange table */ executepartitionexchange (ptargetname, vfullmetaobject.atable.aswapname, ppartitionname, pupdateindexes ); /* * Step 8: Restore original settings. */ restoresettings (psourcename, plogging); END exchangepartition; /* * FullInsert */ PROCEDURE fullinsert ( pname IN metadatapkg.tstring, pwhere IN metadatapkg.tstring := NULL, plogging IN metadatapkg.tstring := metadatapkg.cno, pindexlogging IN metadatapkg.tstring := metadatapkg.cno, pdrop IN metadatapkg.tstring := metadatapkg.cno ) IS vprocedure metadatapkg.tdbstring := 'FullInsert'; BEGIN vprocedure := vprocedure || '(' || UPPER (pname) || ')'; locktable (pname); /* * Step 1: Create swap (target) table. */ createobjects (pname, metadatapkg.ctable, plogging); /* * Step 2: Insert data into swap table using table * name pName and WHERE clause pWhere. */ executeinsert (pname, pwhere); /* * Step 3: Create indexes on swap table. */ createobjects (pname, metadatapkg.cindex, pindexlogging); /* * Step 4: Create constraints on swap table. */ createobjects (pname, metadatapkg.cconstraint); /* * Step 5: Lock tables and swap them. */ swapobjects (pname, pdrop); /* * Step 6: Create triggers on new table. */ createobjects (pname, metadatapkg.ctrigger); /* * Step 7: Recreate referential constraints on foreign key tables. * This step is done as a wrapper for all the tables, not each one createobjects (pname, metadatapkg.crefconstraint); */ /* * Step 8: Restore original settings. */ restoresettings (pname, plogging, pindexlogging); END fullinsert; /* * FullSQL */ PROCEDURE fullsql ( pname IN metadatapkg.tstring, psqlstring IN metadatapkg.tlongstring, plogging IN metadatapkg.tstring := metadatapkg.cno, pindexlogging IN metadatapkg.tstring := metadatapkg.cno, pdrop IN metadatapkg.tstring := metadatapkg.cno ) IS vprocedure metadatapkg.tdbstring := 'FullSQL'; BEGIN locktable (pname); vprocedure := vprocedure || '(' || UPPER (pname) || ')'; /* * Step 1: Create swap table. */ createobjects (pname, metadatapkg.ctable, plogging); /* * Step 2: Insert data into swap table using SQL String pSQLString. */ executesql (psqlstring); /* * Step 3: Create indexes on swap table. */ createobjects (pname, metadatapkg.cindex, pindexlogging); /* * Step 4: Create constraints on swap table. */ createobjects (pname, metadatapkg.cconstraint); /* * Step 5: Lock tables and swap them. */ swapobjects (pname, pdrop); /* * Step 6: Create triggers on new table. */ createobjects (pname, metadatapkg.ctrigger); /* * Step 7: Recreate referential constraints on foreign key tables. * This step is done in the wrapper code for all the tables, not each one createobjects (pname, metadatapkg.crefconstraint); */ /* * Step 8: Restore original settings. */ restoresettings (pname, plogging, pindexlogging); END fullsql; /* * DropObject */ PROCEDURE dropobject ( pobjectname metadatapkg.tstring, pobjecttype metadatapkg.tstring, ptableclause metadatapkg.tstring := NULL ) IS vprocedure metadatapkg.tdbstring := 'DropObject'; vobjectname metadatapkg.tstring := LTRIM (RTRIM (pobjectname)); vobjecttype metadatapkg.tstring := UPPER (LTRIM (RTRIM (pobjecttype))); vcascadeclause metadatapkg.tstring := metadatapkg.ccascade; cnora_table_not_exist CONSTANT PLS_INTEGER := -942; cxora_table_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_table_not_exist, -942); cnora_object_not_exist CONSTANT PLS_INTEGER := -4043; cxora_object_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_object_not_exist, -4043); cnora_index_not_exist CONSTANT PLS_INTEGER := -1418; cxora_index_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_index_not_exist, -1418); cnora_constraint_not_exist CONSTANT PLS_INTEGER := -2443; cxora_constraint_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_constraint_not_exist, -2443); cnora_trigger_not_exist CONSTANT PLS_INTEGER := -4080; cxora_trigger_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_trigger_not_exist, -4080); cnora_sequence_not_exist CONSTANT PLS_INTEGER := -2289; cxora_sequence_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_sequence_not_exist, -2289); cnora_column_not_exist CONSTANT PLS_INTEGER := -904; cxora_column_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_column_not_exist, -904); cnora_index_part_not_exist CONSTANT PLS_INTEGER := -2149; cxora_index_part_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (cxora_index_part_not_exist, -2149); BEGIN CASE WHEN vobjecttype IN (metadatapkg.ctable, metadatapkg.cview) THEN IF NVL (UPPER (LTRIM (RTRIM (ptableclause))), ' ') = metadatapkg.cnocascade THEN vcascadeclause := ''; END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: Drop ' || vobjectname ); COMMIT; EXECUTE IMMEDIATE 'DROP ' || vobjecttype || ' ' || vobjectname || ' ' || vcascadeclause; WHEN vobjecttype IN (metadatapkg.cindex, metadatapkg.cfunction, metadatapkg.cpackage, metadatapkg.cpackagebody, metadatapkg.cprocedure, metadatapkg.csequence, metadatapkg.csynonym, metadatapkg.ctrigger, metadatapkg.ctype ) THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: Drop' || vobjectname ); COMMIT; EXECUTE IMMEDIATE 'DROP ' || vobjecttype || ' ' || vobjectname; WHEN vobjecttype = metadatapkg.cconstraint THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: Drop' || vobjectname ); COMMIT; EXECUTE IMMEDIATE 'ALTER TABLE ' || ptableclause || ' ' || 'DROP ' || vobjecttype || ' ' || vobjectname; WHEN vobjecttype = metadatapkg.ccolumn THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: Drop Column ' || vobjectname ); COMMIT; EXECUTE IMMEDIATE 'ALTER TABLE ' || ptableclause || ' ' || 'DROP ' || vobjecttype || ' ' || vobjectname || ' ' || metadatapkg.ccascade; WHEN vobjecttype IN (metadatapkg.cjavasource, metadatapkg.cjavaclass) THEN operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: Drop Java ' || vobjectname ); COMMIT; EXECUTE IMMEDIATE 'DROP ' || vobjecttype || ' ' || '"' || vobjectname || '"'; ELSE NULL; END CASE; EXCEPTION WHEN cxora_table_not_exist THEN NULL; WHEN cxora_index_not_exist THEN NULL; WHEN cxora_object_not_exist THEN NULL; WHEN cxora_constraint_not_exist THEN NULL; WHEN cxora_trigger_not_exist THEN NULL; WHEN cxora_sequence_not_exist THEN NULL; WHEN cxora_column_not_exist THEN NULL; END dropobject; /* * LockTable */ PROCEDURE locktable ( ptablename IN metadatapkg.tstring, pmode IN metadatapkg.tstring := 'EXCLUSIVE', pnowait IN metadatapkg.tstring := metadatapkg.cyes ) IS vprocedure metadatapkg.tdbstring := 'LockTable (' || UPPER (ptablename) || ')'; vmode metadatapkg.tdbstring := UPPER (LTRIM (RTRIM (pmode))); vsql metadatapkg.tdblongstring; BEGIN vsql := 'LOCK TABLE ' || UPPER (ptablename) || ' IN ' || vmode || ' MODE'; IF pnowait = metadatapkg.cyes THEN vsql := vsql || ' NOWAIT'; END IF; operation_mgmt.log_operation_event (l_operation_cd, vprocedure, operation_mgmt.l_rtn_status_cd_no_error, '* Executing SQL: ' || vsql ); COMMIT; EXECUTE IMMEDIATE vsql; END locktable; END swapops; /