Cisco Transport Manager Server Database Schema, 8.5 - Chapter 3: Packages, Procedures, Functions, and Triggers [Cisco Transport Manager]

Packages, Procedures, Functions, and Triggers

3.1 CTM Packages

3.1.1 Body Security_Pkg

3.2 CTM Stored Procedures

3.2.1 Add_Circuit_Ctps_To_Met

3.2.2 Add_Log_Partition

3.2.3 Add_PM_Partition

3.2.4 Alter_User_Permission

3.2.5 Append_File

3.2.6 BulkNEAddition

3.2.7 Clear_AllUnack_Alarms

3.2.8 Create_File

3.2.9 Execute_Dml_Statement

3.2.10 Execute_Statement

3.2.11 Login_Disable

3.2.12 PortName

3.2.13 Prune_Audittrail

3.2.14 Prune_NE

3.2.15 Prune_PM

3.2.16 Prune_TCA_Event

3.2.17 Reset_Seq

3.2.18 Update_Portnames

3.3 CTM Functions

3.3.1 CollapsedDestNodeName

3.3.2 CollapsedDestNodeName

3.3.3 CollapsedEqptPortName

3.3.4 CollapsedIfIndex

3.3.5 CollapsedIfIndex

3.3.6 CollapsedModelType

3.3.7 CollapsedModuleName

3.3.8 CollapsedObjectIndex

3.3.9 CollapsedPhy

3.3.10 CollapsedPort

3.3.11 CollapsedPortModuleName

3.3.12 ConvertAxxIfIndex

3.3.13 ConvertLinkModuleType

3.3.20 Get_Enet_Value

3.3.21 Get_Enet_Value2

3.3.22 GetBccBwInfo

3.3.23 GetBccClassInfo

3.3.24 GetBccClassRPR80217

3.3.25 GetBccCosCommitGrp

3.3.26 GetBccCosInfo

3.3.27 GetBGFromBGObj

3.3.28 GetBGFromQoSObj

3.3.29 GetCktDestNodeId

3.3.30 GetCktSecSrcIfIndex

3.3.31 GetCktSecSrcPhysicalLoc

3.3.32 GetCktSecSrcPortName

3.3.33 GetCktSrcIfIndex

3.3.34 GetCktSrcPhysicalLoc

3.3.35 GetCktSrcPortName

3.3.36 GetCollapsedPortname

3.3.37 GetEqptInfoPhysicalLoc

3.3.38 GetIfCosGrp

3.3.39 GetInterfaceId

3.3.40 GetLowBits

3.3.41 GetMgx88xxMgmtState

3.3.42 GetMgx88xxSyncMode

3.3.43 GetMLcardAddnlInfo

3.3.44 GetModelTypeFromPhysicalLoc

3.3.45 GetModuleName

3.3.46 GetModuleName_PM

3.3.47 GetModuleName1

3.3.48 GetModuleName2

3.3.49 GetNEDBAccessID

3.3.50 GetObjectTypeFromIfIndex

3.3.51 GetObjectTypeFromIfIndex2

3.3.52 GetPhysicalLoc

3.3.53 GetPhysicalLocWithPIMPPM

3.3.54 GetPIM

3.3.55 GetPMParameterValue

3.3.56 GetPolicyMapType

3.3.57 GetPort

3.3.58 GetPort_XTC

3.3.59 GetPortModuleName

3.3.60 GetPortModuleName2

3.3.61 GetPortName

3.3.62 GetPortWithPIMPPM

3.3.63 GetPosPortState

3.3.64 GetPosPortStateForRPR802

3.3.65 GetPPM

3.3.66 GetProtectMLCardInfo

3.3.67 GetQoSPhLoc

3.3.68 GetShelf

3.3.69 GetSlot

3.3.70 GetSlotModuleName

3.3.71 GetSlotModuleType

3.3.72 GetSpanTblCktSpanDestNodeId

3.3.73 GetSpanTblCktSpanSrcNodeId

3.3.74 GetSubIf

3.3.75 GetValidCktSecSrcNodeId

3.3.76 GetValidCktSrcNodeId

3.3.77 GetValidModelType

3.3.78 GetValidModuleType

3.3.79 GetValidObjectType

3.3.80 MapObjectIndex

3.3.81 MergedInvalidityList

3.3.82 RemoveFirstByteIf

3.3.83 RemoveLowBits

3.3.84 TYPE AlarmInfoListType

3.3.85 TYPE AlarmInfoType

3.3.86 TYPE Link_Id_Tabtype

3.4 CTM Triggers

3.4.1 AAT_Before_Trigger

3.4.2 AAT_Trigger

3.4.3 Act_Trigger

3.4.4 Aft_Del_L2CktTable_Trigger

3.4.5 Aft_Del_L2Interface_Trigger

3.4.6 Aft_Ins_L2Interface_Trigger

3.4.7 Aft_InsDel_Bridgegroup_Trigger

3.4.8 Circuit_Tbl_Alias_Trigger

3.4.9 Ckt_Tbl_Alias_Trigger

3.4.10 Deleted_User_Trigger

3.4.11 Link_Tbl_Alias_Trigger

3.4.12 Nit_Trigger

3.4.13 Security_Aft_Row_All

3.4.14 Security_Aft_Stm_All

3.4.15 Security_Bef_Stm_All

3.4.16 SoTL1_Trigger

3.4.17 Ut_Trigger

3.4.18 VT_Trigger

Packages, Procedures, Functions, and Triggers

This chapter describes the stored packages, procedures, functions, and triggers defined in CTM tables. It contains the following sections:

•CTM Packages

•CTM Stored Procedures

•CTM Functions

•CTM Triggers

3.1 CTM Packages

This section describes the packages that are defined in CTM tables. The packages are listed in alphabetical order.

3.1.1 Body Security_Pkg

CREATE OR REPLACE PACKAGE body security_pkg AS

   PROCEDURE sysAdminEnable AS

   BEGIN

     UPDATE CTM_CONFIG_TABLE SET ACTIVEVALUE = 'true' WHERE PROPERTYNAME = 
'sys-admin-enable';

   END sysAdminEnable;

   PROCEDURE sysAdminDisable AS

   BEGIN

     UPDATE CTM_CONFIG_TABLE SET ACTIVEVALUE = 'false' WHERE PROPERTYNAME = 
'sys-admin-enable';

   END sysAdminDisable;

END security_pkg;

show errors;

3.1.2 CTMAlarm

Create or Replace package CTMAlarm is

--TYPE link_id_tabtype is Table of number INDEX by BINARY_INTEGER;

INVALID_ALARM_STATUS         EXCEPTION;

PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

ALARM_NOT_FOUND              EXCEPTION;

PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

Procedure alarm_handle_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

);

Procedure clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure add_clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure alarm_handle

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

);

Procedure clear_alarms

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure add_clear_alarms

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure bulk_alarm_handle

    p_AlarmInfoList     IN AlarmInfoListType

);

Procedure bulk_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

);

Procedure bulk_add_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

);

END CTMAlarm;

create or replace package body CTMAlarm is

Procedure alarm_handle_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2)

IS

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

        counter             number:=0;

        total_num_linkid    number:=0;

BEGIN

     IF (p_alarmstatus = 0) THEN

        -- this is the new alarm

        begin

           INSERT INTO active_alarm_table (

                  NEDBACCESSID,

                  ALARMSEQNUM,

                  MODULEORIFINDEX,

                  ACTIVEALARMTIMESTAMP,

                  ACTIVEALARMTYPE,

                  ACTIVEALARMSEVERITY,

                  ACTIVEALARMSERVEFF,

                  ACTIVEALARMADDITIONALINFO,

                  ACKINDEX,

                  ACTIVEALARMFLAG,

                  NEALARMTIMESTAMP,

                  ACTIVEALARMINDEX,

                  MODULETYPE,

                  PHYSICALLOC,

                  ALARMSTATUS,

                  EXTERNALCONDITION,

                  MODELTYPE,

                  OBJECTTYPE,

                  STROBJINSTANCE,

                  DIRECTION,

                  LOCATION)

           Values (

                  p_nedbaccessid,

                  p_alarmseqnum,

                  p_moduleorifindex,

                  p_activealarmtimestamp,

                  p_activealarmtype,

                  p_activealarmseverity,

                  p_activealarmserveff,

                  p_activealarmadditionalinfo,

                  p_ackindex,

                  p_activealarmflag,

                  p_nealarmtimestamp,

                  activealarmindex_seq.nextval,

                  p_moduletype,

                  p_physicalloc,

                  p_alarmstatus,

                  p_externalcondition,

                  p_ModelType,

                  p_ObjectType,

                  p_StrObjInstance,

                  p_StrDirection,

                  p_StrLocation);

           exception

              when DUP_VAL_ON_INDEX then

                   dbms_output.put_line('Duplicate alarm');

                   create_file('/tmp','alarm_handling.log','Duplicated alarms');

                      select /*+ INDEX_FFS(ACTIVE_ALARM_PK) */ count(*) into counter from 
active_alarm_table

                       where nedbaccessid=p_nedbaccessid

                         AND moduleorifindex=p_ModuleOrIfIndex

                         AND ActiveAlarmType=p_ActiveAlarmType

                         AND NEAlarmTimeStamp=p_NEAlarmTimeStamp

                         AND Physicalloc=p_Physicalloc

                         AND ObjectType = p_ObjectType

                         AND alarmstatus=0;

                      UPDATE active_alarm_table

                         SET activealarmflag=0,

                             alarmstatus=0,

                             clearalarmtimestamp=to_date(''),

                             nealarmcleartimestamp=to_date(''),

                             ActiveAlarmSeverity=p_ActiveAlarmSeverity,

                             ActiveAlarmServEff=p_ActiveAlarmServEff,

                             ActiveAlarmAdditionalInfo=p_ActiveAlarmAdditionalInfo,

                             ModuleType=p_ModuleType,

                             StrObjInstance = p_StrObjInstance

                       WHERE nedbaccessid=p_nedbaccessid

                         AND moduleorifindex=p_ModuleOrIfIndex

                         AND ActiveAlarmType=p_ActiveAlarmType

                         AND NEAlarmTimeStamp=p_NEAlarmTimeStamp

                         AND Physicalloc=p_Physicalloc

                         AND ObjectType = p_ObjectType

                         AND ModelType = p_ModelType;

                      --commit;

                     IF ( counter = 1) THEN

                         return;

                     END IF;

           --commit;

           end;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

               --dbms_output.put_line('p_linkid=' || p_linkid(table_row));

               IF (p_linkid(table_row) != -1) THEN

                   IF (p_activealarmseverity = 4) THEN

                       UPDATE link_table

                          SET NumWarningAlarms=NumWarningAlarms+1

                        WHERE linkid=p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 5) Then

                       UPDATE link_table

                          SET NumMinorAlarms=NumMinorAlarms+1

                        WHERE linkid = p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 6) THEN

                       UPDATE link_table

                          SET NumMajorAlarms=NumMajorAlarms+1

                        WHERE linkid = p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 7) THEN

                       UPDATE link_table

                          SET NumCriticalAlarms=NumCriticalAlarms+1

                        WHERE linkid= p_linkid(table_row);

                        --commit;

                   END IF;

               END IF;

            END LOOP;

      ELSE

           raise INVALID_ALARM_STATUS;

      END IF;

 END;

Procedure clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2)

IS

        rows_processed               number :=0;

        cursor_handle                INTEGER;

        statement_txt                varchar2(4000):= null;

        total_num_linkid    number:=0;

        --ALARM_NOT_FOUND              EXCEPTION;

        --PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

BEGIN

        ---- alarm resync

        IF (p_alarmstatus = 1 ) THEN

            IF (p_actiontype=1) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

                dbms_output.put_line(p_nedbaccessid);

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), activealarmflag=0, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')';

                statement_txt := statement_txt||' Where alarmstatus=0 AND activealarmflag 
=1  AND nedbaccessid= '||p_nedbaccessid ;

                statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

               -- statement_txt := statement_txt||' and 
activealarmseverity='||p_activealarmseverity;

                statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                statement_txt := statement_txt||' and modeltype='||p_ModelType;

                statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

                --dbms_output.put_line(statement_txt);

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

            IF (p_actiontype=2) THEN

               -- this when in service ne put out of service, keep the activealarmflag=2

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), activealarmflag =2, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')';

                 statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= 
'||p_nedbaccessid ;

                 statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                 statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

                 statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                 statement_txt := statement_txt||' and modeltype='||p_ModelType;

                 statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                 statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

        ------ Normal clear alarms

             IF (p_actiontype = 0) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')' ;

                statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= 
'||p_nedbaccessid ;

                statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

               -- statement_txt := statement_txt||' and 
activealarmseverity='||p_activealarmseverity;

                statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                statement_txt := statement_txt||' and modeltype='||p_ModelType;

                statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

              --  dbms_output.put_line(statement_txt);

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

             END IF;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

               IF (p_linkid(table_row) != -1) THEN

                    IF (p_activealarmseverity = 4) THEN

                        UPDATE link_table

                           SET NumWarningAlarms=NumWarningAlarms-rows_processed

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-rows_processed

                         WHERE linkid= p_linkid(table_row)

                           AND NumCriticalAlarms > 0;

                         --commit;

                    END IF;

               END IF;

           END LOOP;

      ELSE

          raise INVALID_ALARM_STATUS;

      END IF;

 END;

Procedure add_clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition                 IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance                    IN varchar2)

IS

        rows_processed               number :=0;

        cursor_handle                INTEGER;

        statement_txt                varchar2(4000):= null;

        total_num_linkid    number:=0;

        --ALARM_NOT_FOUND              EXCEPTION;

        --PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

BEGIN

        ---- alarm resync

        IF (p_alarmstatus = 1 ) THEN

            IF (p_actiontype=1) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

                dbms_output.put_line(p_nedbaccessid);

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), activealarmflag=0, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')';

                statement_txt := statement_txt||' Where alarmstatus=0 AND activealarmflag 
=1  AND nedbaccessid= '||p_nedbaccessid ;

                statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

               -- statement_txt := statement_txt||' and 
activealarmseverity='||p_activealarmseverity;

                statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                statement_txt := statement_txt||' and modeltype='||p_ModelType;

                statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

                dbms_output.put_line(statement_txt);

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

            IF (p_actiontype=2) THEN

               -- this when in service ne put out of service, keep the activealarmflag=2

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), activealarmflag =2, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')';

                 statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= 
'||p_nedbaccessid ;

                 statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                 statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

                 statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                statement_txt := statement_txt||' and modeltype='||p_ModelType;

                statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

        ------ Normal clear alarms

             IF (p_actiontype = 0) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

		statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||', 
''MM/DD/YYYY HH:MI:SS AM''), 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||', ''MM/DD/YYYY 
HH:MI:SS AM'')' ;

                statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= 
'||p_nedbaccessid ;

                statement_txt := statement_txt||' and 
moduleorifindex='||p_moduleorifindex;

                statement_txt := statement_txt||' and 
activealarmtype='||p_activealarmtype;

               -- statement_txt := statement_txt||' and 
activealarmseverity='||p_activealarmseverity;

                statement_txt := statement_txt||' and physicalloc='||p_physicalloc;

                statement_txt := statement_txt||' and modeltype='||p_ModelType;

                statement_txt := statement_txt||' and objecttype='||p_ObjectType;

                statement_txt := statement_txt||' and 
strobjinstance='||''''||p_StrObjInstance||'''';

              --  dbms_output.put_line(statement_txt);

                DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

             END IF;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

               IF (p_linkid(table_row) != -1) THEN

                    IF (p_activealarmseverity = 4) THEN

                        UPDATE link_table

                           SET NumWarningAlarms=NumWarningAlarms-rows_processed

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-rows_processed

                         WHERE linkid= p_linkid(table_row)

                           AND NumCriticalAlarms > 0;

                         --commit;

                    END IF;

               END IF;

           END LOOP;

      ELSE

          raise INVALID_ALARM_STATUS;

      END IF;

EXCEPTION

                when ALARM_NOT_FOUND then

                    dbms_output.put_line('New insertion of cleared alarm');

                IF (p_actiontype = 1 OR p_actiontype = 2) THEN

                        INSERT INTO active_alarm_table (

                                    NEDBACCESSID,

                                    ALARMSEQNUM,

                                    MODULEORIFINDEX,

                                    ACTIVEALARMTIMESTAMP,

                                    ACTIVEALARMTYPE,

                                    ACTIVEALARMSEVERITY,

                                    ACTIVEALARMSERVEFF,

                                    ACTIVEALARMADDITIONALINFO,

                                    ACKINDEX,

                                    ACTIVEALARMFLAG,

                                    CLEARALARMTIMESTAMP,

                                    NEALARMTIMESTAMP,

                                    NEALARMCLEARTIMESTAMP,

 				ACTIVEALARMINDEX,

                                    MODULETYPE,

                                    PHYSICALLOC,

                                    ALARMSTATUS,

                                    EXTERNALCONDITION,

                                    MODELTYPE,

                                    OBJECTTYPE,

                                    STROBJINSTANCE)

                           Values (

                                    p_nedbaccessid,

                                    p_alarmseqnum,

                                    p_moduleorifindex,

                                    p_activealarmtimestamp,

                                    p_activealarmtype,

                                    p_activealarmseverity,

                                    p_activealarmserveff,

                                    p_activealarmadditionalinfo,

                                    p_ackindex,

                                    p_activealarmflag,

                                    p_clearalarmtimestamp,

                                    p_nealarmtimestamp,

                                    p_nealarmcleartimestamp,

    				activealarmindex_seq.nextval,

                                    p_moduletype,

                                    p_physicalloc,

                                    p_alarmstatus,

                                    p_externalcondition,

                                    p_ModelType,

                                    p_ObjectType,

                                    p_StrObjInstance);

                        --commit;

                END IF;

 END;

Procedure alarm_handle

        p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

IS

BEGIN

        --dbms_output.put_line('alarm_handle: calling alarm_handle_impl: nedbaccessid=' || 
p_nedbaccessid || ', ActiveAlarmComment=' || p_ActiveAlarmComment || ', alarmstatus=' || 
p_alarmstatus);

    alarm_handle_impl

        p_nedbaccessid,

        p_Alarmseqnum,

        p_ModuleOrIfIndex,

        p_ActiveAlarmTimeStamp,

        p_ActiveAlarmType,

        p_ActiveAlarmSeverity,

        p_ActiveAlarmServEff,

        p_ActiveAlarmAdditionalInfo,

        p_AckIndex,

        p_ActiveAlarmFlag,

        p_ActiveAlarmComment,

        p_NEAlarmTimeStamp,

        p_ModuleType,

        p_Physicalloc,

        p_alarmstatus,

        p_linkid,

        p_actiontype,

        p_externalcondition,

        p_ModelType,

        p_ObjectType,

        p_StrObjInstance,

        p_StrDirection,

        p_StrLocation

);

      commit;

 END;

Procedure clear_alarms

        p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

IS

BEGIN

        --dbms_output.put_line('clear_alarms: calling clear_alarms_impl: nedbaccessid=' || 
p_nedbaccessid || ', alarmstatus=' || p_alarmstatus);

        clear_alarms_impl

             p_nedbaccessid,

             p_ModuleOrIfIndex,

             p_ActiveAlarmType,

             p_ActiveAlarmSeverity,

             p_clearalarmtimestamp,

             p_NEAlarmClearTimeStamp,

             p_Physicalloc,

             p_alarmstatus,

             p_linkid,

             p_actiontype,

             p_ModelType,

             p_ObjectType,

             p_StrObjInstance

);

        commit;

END;

Procedure add_clear_alarms

        p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN VARCHAR2

IS

BEGIN

        --dbms_output.put_line('add_clear_alarms: calling add_clear_alarms_impl: 
nedbaccessid=' || p_nedbaccessid || ', alarmstatus=' || p_alarmstatus);

        add_clear_alarms_impl

            p_nedbaccessid,

            p_Alarmseqnum,

            p_ModuleOrIfIndex,

            p_ActiveAlarmTimeStamp,

            p_ActiveAlarmType,

            p_ActiveAlarmSeverity,

            p_ActiveAlarmServEff,

            p_ActiveAlarmAdditionalInfo,

            p_AckIndex,

            p_ActiveAlarmFlag,

            p_ClearAlarmTimeStamp,

            p_NEAlarmTimeStamp,

            p_NEAlarmClearTimeStamp,

            p_ModuleType,

            p_Physicalloc,

            p_alarmstatus,

            p_linkid,

            p_actiontype,

            p_externalcondition,

            p_ModelType,

            p_ObjectType,

            p_StrObjInstance

);

        commit;

END;

Procedure bulk_alarm_handle

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

        --dbms_output.put_line('bulk_alarm_handle: calling alarm_handle_impl: i=' || i || 
':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' || 
p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || 
p_AlarmInfoList(i).alarmstatus);

        alarm_handle_impl (

            p_AlarmInfoList(i).nedbaccessid,

            p_AlarmInfoList(i).Alarmseqnum,

            p_AlarmInfoList(i).ModuleOrIfIndex,

            p_AlarmInfoList(i).ActiveAlarmTimeStamp,

            p_AlarmInfoList(i).ActiveAlarmType,

            p_AlarmInfoList(i).ActiveAlarmSeverity,

            p_AlarmInfoList(i).ActiveAlarmServEff,

            p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,

            p_AlarmInfoList(i).AckIndex,

            p_AlarmInfoList(i).ActiveAlarmFlag,

            p_AlarmInfoList(i).ActiveAlarmComment,

            p_AlarmInfoList(i).NEAlarmTimeStamp,

            p_AlarmInfoList(i).ModuleType,

            p_AlarmInfoList(i).Physicalloc,

            p_AlarmInfoList(i).alarmstatus,

            p_AlarmInfoList(i).linkid,

            p_AlarmInfoList(i).actiontype,

            p_AlarmInfoList(i).externalcondition,

            p_AlarmInfoList(i).ModelType,

            p_AlarmInfoList(i).ObjectType,

            p_AlarmInfoList(i).StrObjInstance,

            p_AlarmInfoList(i).StrDirection,

            p_AlarmInfoList(i).StrLocation

);

    END LOOP;

    commit;

END;

Procedure bulk_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

        dbms_output.put_line('bulk_clear_alarms: calling clear_alarms_impl: i=' || i || 
':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' || 
p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || 
p_AlarmInfoList(i).alarmstatus);

        clear_alarms_impl

             p_AlarmInfoList(i).nedbaccessid,

             p_AlarmInfoList(i).ModuleOrIfIndex,

             p_AlarmInfoList(i).ActiveAlarmType,

             p_AlarmInfoList(i).ActiveAlarmSeverity,

             p_AlarmInfoList(i).clearalarmtimestamp,

             p_AlarmInfoList(i).NEAlarmClearTimeStamp,

             p_AlarmInfoList(i).Physicalloc,

             p_AlarmInfoList(i).alarmstatus,

             p_AlarmInfoList(i).linkid,

             p_AlarmInfoList(i).actiontype,

             p_AlarmInfoList(i).ModelType,

             p_AlarmInfoList(i).ObjectType,

             p_AlarmInfoList(i).StrObjInstance

);

    END LOOP;

    commit;

END;

Procedure bulk_add_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

        --dbms_output.put_line('bulk_add_clear_alarms: calling add_clear_alarms_impl: i=' 
|| i || ':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' 
|| p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || 
p_AlarmInfoList(i).alarmstatus);

        add_clear_alarms_impl

            p_AlarmInfoList(i).nedbaccessid,

            p_AlarmInfoList(i).Alarmseqnum,

            p_AlarmInfoList(i).ModuleOrIfIndex,

            p_AlarmInfoList(i).ActiveAlarmTimeStamp,

            p_AlarmInfoList(i).ActiveAlarmType,

            p_AlarmInfoList(i).ActiveAlarmSeverity,

            p_AlarmInfoList(i).ActiveAlarmServEff,

            p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,

            p_AlarmInfoList(i).AckIndex,

            p_AlarmInfoList(i).ActiveAlarmFlag,

            p_AlarmInfoList(i).ClearAlarmTimeStamp,

            p_AlarmInfoList(i).NEAlarmTimeStamp,

            p_AlarmInfoList(i).NEAlarmClearTimeStamp,

            p_AlarmInfoList(i).ModuleType,

            p_AlarmInfoList(i).Physicalloc,

            p_AlarmInfoList(i).alarmstatus,

            p_AlarmInfoList(i).linkid,

            p_AlarmInfoList(i).actiontype,

            p_AlarmInfoList(i).externalcondition,

            p_AlarmInfoList(i).ModelType,

            p_AlarmInfoList(i).ObjectType,

            p_AlarmInfoList(i).StrObjInstance

);

    END LOOP;

    commit;

END;

END CTMAlarm;

3.1.3 MSMCircuit

--CIRCUIT_BS_TBL

create TYPE circuit_bs_tbl_struct as OBJECT (

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER,

        CKTNAME                 VARCHAR2 (64),

        CKTDESCRIPTION	        VARCHAR2 (256),

        CUSTOMERID              VARCHAR2 (256),

        SERVICEID               VARCHAR2 (256),

        CKTTYPE	                NUMBER,

        CKTSIZE	                NUMBER,

        CKTDIRECTION            NUMBER,

        CKTISMONITOR	        NUMBER,

        CKTSTATE                NUMBER,

        CKTENHANCEDSTATE        NUMBER,

        CKTPROTECTIONTYPE       NUMBER,

        CKTADDITIONALINFO       VARCHAR2(64),

        CKTCOMMENT              VARCHAR2 (2014),

        CKTUSElAP               NUMBER,

        CKTOCHNCCHANNEL         NUMBER,

        CKTOCHNCDIR             NUMBER,

        ISDUPLICATENAME         NUMBER,

        NPID                    NUMBER,

        CKTALIASNAME	VARCHAR2 (64)

);

create TYPE circuit_bs_tbl_struct_list as table of circuit_bs_tbl_struct;

create TYPE circuit_identity_struct as OBJECT (

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER

);

create TYPE circuit_identity_struct_list as table of circuit_identity_struct;

--CIRCUIT_CTP_TBL

create TYPE circuit_ctp_tbl_struct as OBJECT (

        CTP_TYPE                NUMBER,

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER,

        CTPNODEID               NUMBER,

        CTPMODULETYPE           NUMBER,

        CTPIFINDEX              NUMBER,

        CTPOBJECTINDEX          NUMBER,

        CTPPHYSICALLOC          NUMBER,

        CKTMODELTYPE            NUMBER,

        CTPDROPPROT             NUMBER,

        CKTCTPPATHPROT          NUMBER,

        CTPDROPPROTTYPE         NUMBER,

        CTPPORTNAME             VARCHAR2 (64),

        CTPADDITIONALINFO       VARCHAR2 (64)

);

create TYPE circuit_ctp_tbl_struct_list as table of circuit_ctp_tbl_struct;

--CIRCUIT_SPAN_BS_TBL

create TYPE circuit_span_bs_tbl_struct as OBJECT (

        CKTNODEID                       NUMBER,

        CKTUNIQUEID                     NUMBER,

        CKTLINKID                       NUMBER,

        SPAN_TYPE                       NUMBER,

        SRC_CTP_TYPE                    NUMBER,

        SRC_CTPNODEID                   NUMBER,

        SRC_CTPMODULETYPE               NUMBER,

        SRC_CTPIFINDEX                  NUMBER,

        SRC_CTPOBJECTINDEX              NUMBER,

        SRC_CTPPHYSICALLOC              NUMBER,

        SRC_CKTMODELTYPE                NUMBER,

        DST_CTP_TYPE                    NUMBER,

        DST_CTPNODEID                   NUMBER,

        DST_CTPMODULETYPE               NUMBER,

        DST_CTPIFINDEX                  NUMBER,

        DST_CTPOBJECTINDEX              NUMBER,

        DST_CTPPHYSICALLOC              NUMBER,

        DST_CKTMODELTYPE                NUMBER,

        CKTSPANSRCSTATE		        NUMBER,

        CKTSPANDESTSTATE                NUMBER,

        ISCKTFORWARDING       	        NUMBER,

        SCKTSPANINUPSR      	        NUMBER,

        ISCKTSPANWORKING   	        NUMBER,

        ISCKTSPANACTIVE    	        NUMBER,

        CKTSPANPROTOP	                NUMBER,

        CKTSPANCDLFLOWID                NUMBER,

        CKTSPANSRCPORTNAME              VARCHAR2(64),

        CKTSPANDESTPORTNAME             VARCHAR2(64)

);

create TYPE circuit_span_bs_tbl_st_list as table of circuit_span_bs_tbl_struct;

Create or Replace package MSMCircuit is

Procedure create_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

);

Procedure update_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

);

Procedure update_circuit_ctps

        p_nedbaccessid            IN NUMBER,

        p_ckt_min_physicalloc     IN NUMBER,

        p_ckt_max_physicalloc     IN NUMBER,

        p_cktmoduletype           IN NUMBER,

        cktmoduletype_upd_flag    IN NUMBER,

        p_cktportname             IN VARCHAR2,

        cktportname_udp_flag      IN NUMBER

);

Procedure delete_circuit

        circuit_id_list           IN circuit_identity_struct_list,

        delcomp                   IN NUMBER

);

Procedure delete_circuits_on_nodeList

        circuit_id_list           IN circuit_identity_struct_list

);

END MSMCircuit;

create or Replace package body MSMCircuit is

Procedure create_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

IS

    p_npid NUMBER(1);

    i number;

    create_circuit_failed EXCEPTION;

    i_CTPACCESSID NUMBER;

    i_CKTSPANBSID NUMBER;

    i_CKTSPANSRCCTPACCESSID NUMBER;

    i_CKTSPANDSTCTPACCESSID NUMBER;

    p_CKTCTPNeDBAccessId NUMBER;

BEGIN

    i := circuit_bs_tbl_data.FIRST;

    p_npid := circuit_bs_tbl_data(i).NPID;

--    BEGIN

        -- Get NPID. It is not sent by application.

--        select sub.NPID into p_npid

--        from ne_info_table ne, subnetwork_table sub

--        where ne.NENODEID = circuit_bs_tbl_data(i).CKTNODEID

--        and   ne.NeSubNetworkid=sub.subnetid;

--

--        exception

--            when no_data_found then

--                raise create_circuit_failed;

--    END;

    --Remove all old data from the tables if any

    BEGIN

        delete from circuit_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_ctp_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_span_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    insert into circuit_bs_tbl

    values (

        circuit_bs_tbl_data(i).CKTNODEID,

        circuit_bs_tbl_data(i).CKTUNIQUEID,

        circuit_bs_tbl_data(i).CKTNAME,

        circuit_bs_tbl_data(i).CKTDESCRIPTION,

        circuit_bs_tbl_data(i).CUSTOMERID,

        circuit_bs_tbl_data(i).SERVICEID,

        circuit_bs_tbl_data(i).CKTTYPE,

        circuit_bs_tbl_data(i).CKTSIZE,

        circuit_bs_tbl_data(i).CKTDIRECTION,

        circuit_bs_tbl_data(i).CKTISMONITOR,

        circuit_bs_tbl_data(i).CKTSTATE,

        circuit_bs_tbl_data(i).CKTENHANCEDSTATE,

        circuit_bs_tbl_data(i).CKTPROTECTIONTYPE,

        circuit_bs_tbl_data(i).CKTADDITIONALINFO,

        circuit_bs_tbl_data(i).CKTCOMMENT,

        circuit_bs_tbl_data(i).CKTUSElAP,

        circuit_bs_tbl_data(i).CKTOCHNCCHANNEL,

        circuit_bs_tbl_data(i).CKTOCHNCDIR,

        circuit_bs_tbl_data(i).ISDUPLICATENAME,

        p_npid,

        circuit_bs_tbl_data(i).CKTALIASNAME

);

    For I IN circuit_ctp_tbl_data.FIRST .. circuit_ctp_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_ctp_tbl_data(i).CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CTPACCESSID,

            circuit_ctp_tbl_data(i).CTP_TYPE,

            circuit_ctp_tbl_data(i).CKTNODEID,

            circuit_ctp_tbl_data(i).CKTUNIQUEID,

            circuit_ctp_tbl_data(i).CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_ctp_tbl_data(i).CTPMODULETYPE,

            circuit_ctp_tbl_data(i).CTPIFINDEX,

            circuit_ctp_tbl_data(i).CTPOBJECTINDEX,

            circuit_ctp_tbl_data(i).CTPPHYSICALLOC,

            circuit_ctp_tbl_data(i).CKTMODELTYPE,

            circuit_ctp_tbl_data(i).CTPDROPPROT,

            circuit_ctp_tbl_data(i).CKTCTPPATHPROT,

            circuit_ctp_tbl_data(i).CTPDROPPROTTYPE,

            circuit_ctp_tbl_data(i).CTPPORTNAME,

            circuit_ctp_tbl_data(i).CTPADDITIONALINFO,

            p_npid

);

    END LOOP;

    if (circuit_span_bs_tbl_data.count = 0) then

        goto skip_circuit_span_bs_tbl;

    end if;

    For I IN circuit_span_bs_tbl_data.FIRST .. circuit_span_bs_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).SRC_CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANSRCCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANSRCCTPACCESSID,

            circuit_span_bs_tbl_data(i).SRC_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).SRC_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).SRC_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).SRC_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).SRC_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            null,

            p_npid

);

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).DST_CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANDSTCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).DST_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).DST_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).DST_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).DST_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).DST_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            null,

            p_npid

);

        SELECT CKTSPANBSID_SEQ.nextval INTO i_CKTSPANBSID FROM dual;

        insert into circuit_span_bs_tbl

        values (

            i_CKTSPANBSID,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).CKTLINKID,

            circuit_span_bs_tbl_data(i).SPAN_TYPE,

            i_CKTSPANSRCCTPACCESSID,

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCSTATE,

            circuit_span_bs_tbl_data(i).CKTSPANDESTSTATE,

            circuit_span_bs_tbl_data(i).ISCKTFORWARDING,

            circuit_span_bs_tbl_data(i).SCKTSPANINUPSR,

            circuit_span_bs_tbl_data(i).ISCKTSPANWORKING,

            circuit_span_bs_tbl_data(i).ISCKTSPANACTIVE,

            circuit_span_bs_tbl_data(i).CKTSPANPROTOP,

            circuit_span_bs_tbl_data(i).CKTSPANCDLFLOWID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            p_npid

);

    END LOOP;

    <<skip_circuit_span_bs_tbl>>

        null;

    commit;

END;

Procedure update_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

IS

    p_npid NUMBER(1);

    i number;

    i_CTPACCESSID NUMBER;

    i_CKTSPANBSID NUMBER;

    update_circuit_failed EXCEPTION;

    i_CKTSPANSRCCTPACCESSID NUMBER;

    i_CKTSPANDSTCTPACCESSID NUMBER;

    p_CKTCTPNeDBAccessId NUMBER;

BEGIN

    i := circuit_bs_tbl_data.FIRST;

    p_npid := circuit_bs_tbl_data(i).NPID;

    BEGIN

        update circuit_bs_tbl

set

            CKTNAME = circuit_bs_tbl_data(i).CKTNAME,

            CKTTYPE = circuit_bs_tbl_data(i).CKTTYPE,

            CKTSIZE = circuit_bs_tbl_data(i).CKTSIZE,

            CKTISMONITOR = circuit_bs_tbl_data(i).CKTISMONITOR,

            CKTSTATE = circuit_bs_tbl_data(i).CKTSTATE,

            CKTENHANCEDSTATE = circuit_bs_tbl_data(i).CKTENHANCEDSTATE,

            CKTPROTECTIONTYPE = circuit_bs_tbl_data(i).CKTPROTECTIONTYPE,

            CKTADDITIONALINFO = circuit_bs_tbl_data(i).CKTADDITIONALINFO,

            CKTUSElAP = circuit_bs_tbl_data(i).CKTUSElAP,

            CKTOCHNCCHANNEL = circuit_bs_tbl_data(i).CKTOCHNCCHANNEL,

            CKTOCHNCDIR = circuit_bs_tbl_data(i).CKTOCHNCDIR,

            ISDUPLICATENAME = circuit_bs_tbl_data(i).ISDUPLICATENAME

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        --No record. Cannot do update. Let application handle the exception.

        exception

            when no_data_found then

                raise update_circuit_failed;

    END;

    --For circuit_ctp_tbl and circuit_span_bs_tbl, do delete and insert, not update

    BEGIN

        delete from circuit_ctp_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_span_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    For I IN circuit_ctp_tbl_data.FIRST .. circuit_ctp_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_ctp_tbl_data(i).CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CTPACCESSID,

            circuit_ctp_tbl_data(i).CTP_TYPE,

            circuit_ctp_tbl_data(i).CKTNODEID,

            circuit_ctp_tbl_data(i).CKTUNIQUEID,

            circuit_ctp_tbl_data(i).CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_ctp_tbl_data(i).CTPMODULETYPE,

            circuit_ctp_tbl_data(i).CTPIFINDEX,

            circuit_ctp_tbl_data(i).CTPOBJECTINDEX,

            circuit_ctp_tbl_data(i).CTPPHYSICALLOC,

            circuit_ctp_tbl_data(i).CKTMODELTYPE,

            circuit_ctp_tbl_data(i).CTPDROPPROT,

            circuit_ctp_tbl_data(i).CKTCTPPATHPROT,

            circuit_ctp_tbl_data(i).CTPDROPPROTTYPE,

            circuit_ctp_tbl_data(i).CTPPORTNAME,

            circuit_ctp_tbl_data(i).CTPADDITIONALINFO,

            p_npid

);

    END LOOP;

    if (circuit_span_bs_tbl_data.count = 0) then

        goto skip_circuit_span_bs_tbl;

    end if;

    For I IN circuit_span_bs_tbl_data.FIRST .. circuit_span_bs_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).SRC_CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANSRCCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANSRCCTPACCESSID,

            circuit_span_bs_tbl_data(i).SRC_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).SRC_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).SRC_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).SRC_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).SRC_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            null,

            p_npid

);

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).DST_CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANDSTCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).DST_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).DST_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).DST_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).DST_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).DST_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            null,

            p_npid

);

        SELECT CKTSPANBSID_SEQ.nextval INTO i_CKTSPANBSID FROM dual;

        insert into circuit_span_bs_tbl

        values (

            i_CKTSPANBSID,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).CKTLINKID,

            circuit_span_bs_tbl_data(i).SPAN_TYPE,

            i_CKTSPANSRCCTPACCESSID,

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCSTATE,

            circuit_span_bs_tbl_data(i).CKTSPANDESTSTATE,

            circuit_span_bs_tbl_data(i).ISCKTFORWARDING,

            circuit_span_bs_tbl_data(i).SCKTSPANINUPSR,

            circuit_span_bs_tbl_data(i).ISCKTSPANWORKING,

            circuit_span_bs_tbl_data(i).ISCKTSPANACTIVE,

            circuit_span_bs_tbl_data(i).CKTSPANPROTOP,

            circuit_span_bs_tbl_data(i).CKTSPANCDLFLOWID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            p_npid

);

    END LOOP;

    <<skip_circuit_span_bs_tbl>>

        null;

    commit;

END;

Procedure update_circuit_ctps

        p_nedbaccessid            IN NUMBER,

        p_ckt_min_physicalloc     IN NUMBER,

        p_ckt_max_physicalloc     IN NUMBER,

        p_cktmoduletype           IN NUMBER,

        cktmoduletype_upd_flag    IN NUMBER,

        p_cktportname             IN VARCHAR2,

        cktportname_udp_flag      IN NUMBER

IS

    rows_processed    number :=0;

    cursor_handle     INTEGER :=  DBMS_SQL.OPEN_CURSOR;

    statement_txt     varchar2(500);

BEGIN

    --Nothing to be updated

    if(cktmoduletype_upd_flag = 0 and cktportname_udp_flag = 0) then

        return;

    end if;

    statement_txt := 'update circuit_ctp_tbl set ';

    if (cktmoduletype_upd_flag = 1) then

        statement_txt := statement_txt || 'CKTCTPMODULETYPE = ' || p_cktmoduletype;

    end if;

    if (cktportname_udp_flag = 1) then

        statement_txt := statement_txt || ', CKTCTPPORTNAME = ' || '''' 
||p_cktportname||'''';

    end if;

    statement_txt := statement_txt || ' where CTPACCESSID = ' || p_nedbaccessid

                 || ' and CKTCTPPHYSICALLOC between ' || p_ckt_min_physicalloc

                 || ' and ' || p_ckt_max_physicalloc;

    --dbms_output.put_line(statement_txt);

    DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);

    rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

    IF (rows_processed=0) THEN

        dbms_output.put_line('No record found');

        --DBMS_SQL.CLOSE_CURSOR (cursor_handle);

    END IF;

    DBMS_SQL.CLOSE_CURSOR (cursor_handle);

    commit;

END;

Procedure delete_circuit

        circuit_id_list           IN circuit_identity_struct_list,

        delcomp                   IN NUMBER

IS

    i number;

BEGIN

    For I IN circuit_id_list.FIRST .. circuit_id_list.LAST

    LOOP

        if(delcomp = 0) then

            goto continue_loop;

        end if;

        BEGIN

            delete from circuit_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VCG_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        <<continue_loop>>

        null;

        BEGIN

            delete from circuit_ctp_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_span_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

    END LOOP;

    commit;

END;

Procedure delete_circuits_on_nodeList

        circuit_id_list           IN circuit_identity_struct_list

IS

    i number;

BEGIN

    For I IN circuit_id_list.FIRST .. circuit_id_list.LAST

    LOOP

        BEGIN

            delete from circuit_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VCG_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_ctp_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_span_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VLAN_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

    END LOOP;

    commit;

END;

END MSMCircuit;

3.1.4 Prune_Package

3.1.4.1 Prune_Package for Small Configurations

rem Pruning Package and change prune schedule package

CREATE OR REPLACE PACKAGE Prune AS

   row_count           NUMBER :=   0 ;

   rows_to_be_deleted  NUMBER := 100000 ;

   ORACLE_SID          varchar2(40);

   ORACLE_BASE         varchar2(20);

   PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );

   PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );

   PROCEDURE prune_auditlog( intervaldays IN Number);

   PROCEDURE prune_errorlog(intervaldays IN number) ;

   PROCEDURE prune_fm( intervaldays IN number);

   PROCEDURE prune_server_monitor( intervaldays IN number);

   PROCEDURE prune_admin_job_table( intervaldays IN number);

END Prune ;

show errors;

CREATE OR REPLACE PACKAGE BODY Prune AS

   PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne, ne_info_Table ne

   WHERE  gne.gnesysid = ne.nesysid AND

          ne.nesysid   = iv_NESysId ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId  AND

          ne.nesysid <> iv_NESysId ;

   lv_GNEId ne_info_table.GNEId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         FOR rec IN GneChildren(lv_GNEId) LOOP

            dbms_output.put_line('NE : '||rec.nesysid);

         END LOOP ;

      END IF ;

      CLOSE IsThisAGNE ;

   END CheckGne ;

   PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne

   WHERE  gne.gnesysid = iv_NESysid ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId AND

          ne.nesysid <> iv_NESysid ;

   lv_GNEId   ne_info_table.GNEId%TYPE   := NULL ;

   lv_NESysId ne_info_table.NESysId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         OPEN  GneChildren (lv_GNEId ) ;

         FETCH GneChildren INTO lv_NESysId ;

         IF GNEChildren%NOTFOUND THEN

            DELETE gne_table

            WHERE GNEId = lv_GNEId ;

            COMMIT ;

         END IF ;

      END IF ;

      CLOSE IsThisAGNE ;

   END  PruneGNE ;

   PROCEDURE prune_auditlog( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             rows_processed  number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=sysdate-intervaldays;

           dbms_output.put_line('Prune data before '||prunedate);

           
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Starting 
prune auditlog at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Prune 
transaction log table before '||prunedate);

           FOR i IN (select /*+ INDEX_FFS(TRANSACTION_LOG_TS_I) */ rowid from 
transaction_log_table

                     where timestamp < prunedate ) LOOP

                       rowid_tab_v(counter) := i.rowid;

                       counter := counter + 1;

                       IF counter = rows_to_be_deleted THEN

                          FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                                 DELETE FROM transaction_log_table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                                 counter := 0;

                       END IF;

           END LOOP;

           IF counter < rows_to_be_deleted AND counter > 0 THEN

               FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM transaction_log_table

                               WHERE rowid = rowid_tab_v(rid);

               COMMIT;

               rows_processed:=rows_processed+counter;

               counter := 0;

           END IF;

               dbms_output.put_line('Deleted '||rows_processed||' rows from 
transaction_log_table');

	       
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Deleted 
'||rows_processed||' rows from transaction_log_table');

               
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Finish 
prune transaction log table at '||sysdate);

               COMMIT ;

      ELSE

          dbms_output.put_line('user choose to keep all audit log data, so no prune at 
this time');

	  create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','User 
choose to keep all data, no prune');

      END IF;

   EXCEPTION

        WHEN OTHERS THEN

        
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.err','Error in 
pruning audit log '||SQLERRM||', '||SQLCODE);

   END;

   PROCEDURE prune_errorlog(intervaldays IN number) AS

             prunedate     date;

             counter       number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:= sysdate-intervaldays;

           dbms_output.put_line('Prune data before '||prunedate);

	   
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Starting 
prune errorlog at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Prune error 
log table data before '||prunedate);

           FOR i IN (select /*+ INDEX_FFS(ERROR_LOG_TIME_INDEX) */ rowid from 
ERROR_LOG_TABLE

                     where timestamp < prunedate ) LOOP

                     rowid_tab_v(counter) := i.rowid;

                     counter := counter + 1;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM error_log_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                        counter := 0;

                     END IF;

           END LOOP;

           IF counter < rows_to_be_deleted AND counter > 0 THEN

               FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM error_log_table

                               WHERE rowid = rowid_tab_v(rid);

               COMMIT;

               rows_processed:=rows_processed+counter;

               counter := 0;

           END IF;

           dbms_output.put_line('Deleted '||rows_processed||' rows from error_log_table 
');

	   
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Deleted 
'||rows_processed||' rows from error_log_table ');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Finish 
prune error log table at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));

       ELSE

           dbms_output.put_line('User choose to keep all data, no prune');

	   create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','User 
choose to keep all data, no prune');

       END IF;

     EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.err','Error in 
pruning error log '||SQLERRM||', '||SQLCODE);

   END;

PROCEDURE prune_fm(intervaldays IN number ) AS

             prunedate    date;

             counter      number :=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             TYPE ackwid_tab is TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;

             ackwid_tab_v ackwid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:= sysdate-intervaldays;

           dbms_output.put_line('Prune data before '||prunedate);

    create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Starting 
prune fm at '||sysdate);

           append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Prune 
FM data before '||prunedate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(ALARM_EVENT_TIME_INDEX) */ rowid from 
alarm_event_table

                     where  AlarmEventTimeStamp < prunedate and rownum <= 
rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from alarm_event_table at '|| sysdate);

           counter:=0;

           rows_processed:=0;

     LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(ACTIVE_ALARM_TIME_INDEX) */ ackindex, rowid from 
ACTIVE_ALARM_TABLE

                     where  ActiveAlarmTimeStamp < prunedate

                       and alarmstatus = 1

                       and ackindex <> 0

                       and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     ackwid_tab_v(counter) := i.ackindex;

                     IF counter = rows_to_be_deleted THEN

                       FOR rid IN 1 .. counter LOOP

                         DELETE FROM ACTIVE_ALARM_TABLE

                         WHERE rowid = rowid_tab_v(rid);

    DELETE FROM ACK_TABLE

                         WHERE ackindex = ackwid_tab_v(rid) AND ackindex <> -1;

                    END LOOP;

                    COMMIT;

                       rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                FOR rid IN 1 .. counter LOOP

                  DELETE FROM ACTIVE_ALARM_TABLE

                  WHERE rowid = rowid_tab_v(rid);

           DELETE FROM ACK_TABLE

                  WHERE ackindex = ackwid_tab_v(rid) AND ackindex <> -1;

               END LOOP;

                COMMIT;

                rows_processed:=rows_processed+counter;

                counter := 0;

              END IF;

              exit;

           END IF;

      END LOOP;

      append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from active_alarm_table at '||sysdate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(SYSLOG_MESSAGE_TS_I) */ rowid from 
SYSLOG_MESSAGE_TABLE

                     where  TimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from SYSLOG_MESSAGE_TABLE at '|| sysdate);

      append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Finish 
prune FM at '||sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

        create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.err','Error in 
pruning fm log '||SQLERRM||', '||SQLCODE);

   END;

   PROCEDURE prune_server_monitor( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

       IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');

           dbms_output.put_line('Prune data before '||prunedate);

	   
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Starti
ng prune server monitor table at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Prune 
server monitor table before '||prunedate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(SERVER_MONITOR_TIME_I) */ rowid  from 
SERVER_MONITOR_TABLE

                     where  CollectionTime < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM SERVER_MONITOR_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SERVER_MONITOR_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Delete
d '||rows_processed||' rows from SERVER_MONITOR_TABLE at '|| sysdate);

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Finish 
prune server monitor table at '||sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

	       
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_servermonitor.err','Error 
in prune server monitor table '||SQLERRM||', '||SQLCODE);

   END;

PROCEDURE prune_admin_job_table( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');

           dbms_output.put_line('Prune data before '||prunedate);

	   
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Starti
ng prune admin job table at '||sysdate);

           
append_file(ORACLE_BASE||'admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Prune 
admin job  table before '||prunedate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(admin_job_ScheduledTIme_index) */ rowid  from 
ADMIN_JOB_TABLE

                     where  ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Delete
d '||rows_processed||' rows from ADMIN_JOB_TABLE at '|| sysdate);

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Finish 
prune admin job table at '||sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

	       
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.err','Error 
in prune admin job  table '||SQLERRM||', '||SQLCODE);

   END;

END prune;

show errors;

3.1.4.2 prune_package for Medium, Large, and High-End Configurations

CREATE OR REPLACE PACKAGE Prune AS

   row_count           NUMBER :=   0 ;

   rows_to_be_deleted  NUMBER := 100000 ;

   ORACLE_SID          varchar2(40);

   ORACLE_BASE         varchar2(20);

   PROCEDURE Prune_log(p_table_name in varchar2, intervaldays in number, log_file in 
varchar2);

   PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );

   PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );

   PROCEDURE prune_auditlog( intervaldays IN Number);

   PROCEDURE prune_errorlog(intervaldays IN number) ;

   PROCEDURE prune_fm( intervaldays IN number);

   PROCEDURE prune_server_monitor( intervaldays IN number);

   PROCEDURE prune_admin_job_table( intervaldays IN number);

END Prune ;

show errors;

CREATE OR REPLACE PACKAGE BODY Prune AS

   PROCEDURE Prune_log(p_table_name in varchar2, intervaldays in number, log_file in 
varchar2)

AS

        partition_to_be_pruned    varchar2(9);

        cursor_handle   integer;

        cursor_handle_1 integer;

        statement_txt   varchar2(500);

        statement_txt_1 varchar2(500);

        rows_processed  number := 0;

        rows_processed_1 number :=0;

        prunedate         date;

        deleted_rows       number;

        ORACLE_SID       varchar2(20);

	ORACLE_BASE      varchar2(20);

        err_file         varchar2(500);

   BEGIN

       err_file := 'prune_'||p_table_name||'.err';

       SELECT activevalue INTO ORACLE_SID

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='dbname';

       SELECT activevalue INTO ORACLE_BASE

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='db-oracle-base';

       IF ( intervaldays != -1) THEN

          execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

          prunedate:= sysdate-intervaldays;

          append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump',log_file,'prune data in 
'||p_table_name||' before '||prunedate);

          prunedate:= trunc(sysdate-intervaldays);

          FOR rec IN ( SELECT table_name, index_name,column_name  FROM user_ind_columns

                        WHERE table_name = p_table_name

                          AND column_name like '%TIME%'

                          AND column_position=1)

          LOOP

               /* get the counter of rows to be pruned */

               cursor_handle_1 := DBMS_SQL.OPEN_CURSOR;

	       execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

	       statement_txt_1 := 'SELECT /*+ INDEX_FFS('||rec.index_name||') */ count(rowid) 
FROM '||rec.table_name||' where '||rec.column_name ||'<= '||''''||prunedate||'''';

               --DBMS_OUTPUT.PUT_LINE(statement_txt_1);

               DBMS_SQL.PARSE(cursor_handle_1, statement_txt_1, DBMS_SQL.NATIVE);

               DBMS_SQL.DEFINE_COLUMN ( cursor_handle_1,1, deleted_rows);

               rows_processed := DBMS_SQL.EXECUTE(cursor_handle_1);

               IF DBMS_SQL.FETCH_ROWS(cursor_handle_1)=0 then

                  EXIT;

               ELSE

                    DBMS_SQL.COLUMN_VALUE (cursor_handle_1,1, deleted_rows);

                    deleted_rows :=nvl(deleted_rows,0);

		    append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump',log_file,'pruned 
'||deleted_rows||' from '||rec.table_name);

               END IF;

               DBMS_SQL.CLOSE_CURSOR (cursor_handle_1);

               /* prune partitions before the prune intervaldays  */

               FOR i IN ( SELECT partition_name FROM user_segments

                          Where to_date(substr(partition_name,2,9), 'MMDDYYYY') <= 
prunedate

                          AND   segment_name=rec.table_name)

               LOOP

                        /* drop all the old partition */

                        --dbms_output.put_line(i.partition_name);

                        cursor_handle := DBMS_SQL.OPEN_CURSOR;

			execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

                        statement_txt := 'alter table '||rec.table_name ||' drop partition 
'||i.partition_name;

                        DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

                        rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                        DBMS_SQL.CLOSE_CURSOR (cursor_handle);

			append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump',log_file,'dropped 
partition '||i.partition_name||' from '||rec.table_name);

                END LOOP;

          END LOOP;

                append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump',log_file,'prune 
'||p_table_name||' successfully finished at '||sysdate);

     END IF;

EXCEPTION

         WHEN OTHERS THEN

         create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump',err_file,'Error in 
pruning '||p_table_name||': '||SQLERRM||', '||SQLCODE);

   END prune_log;

   PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne, ne_info_Table ne

   WHERE  gne.gnesysid = ne.nesysid AND

          ne.nesysid   = iv_NESysId ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId  AND

          ne.nesysid <> iv_NESysId ;

   lv_GNEId ne_info_table.GNEId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         FOR rec IN GneChildren(lv_GNEId) LOOP

            dbms_output.put_line('NE : '||rec.nesysid);

         END LOOP ;

      END IF ;

      CLOSE IsThisAGNE ;

   END CheckGne ;

   PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne

   WHERE  gne.gnesysid = iv_NESysid ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId AND

          ne.nesysid <> iv_NESysid ;

   lv_GNEId   ne_info_table.GNEId%TYPE   := NULL ;

   lv_NESysId ne_info_table.NESysId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         OPEN  GneChildren (lv_GNEId ) ;

         FETCH GneChildren INTO lv_NESysId ;

         IF GNEChildren%NOTFOUND THEN

            DELETE gne_table

            WHERE GNEId = lv_GNEId ;

            COMMIT ;

         END IF ;

      END IF ;

      CLOSE IsThisAGNE ;

   END  PruneGNE ;

   PROCEDURE prune_auditlog( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             rows_processed  number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=sysdate-intervaldays;

           dbms_output.put_line('Prune data before '||prunedate);

	   
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Starting 
prune auditlog at '||sysdate);

           prune_log ('TRANSACTION_LOG_TABLE', intervaldays, 'prune_auditlog.log');

      ELSE

          dbms_output.put_line('user choose to keep all audit log data, so no prune at 
this time');

	  create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','User 
choose to keep all data, no prune');

      END IF;

   EXCEPTION

        WHEN OTHERS THEN

        
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_auditlog.err','Error in 
pruning audit log '||SQLERRM||', '||SQLCODE);

   END;

   PROCEDURE prune_errorlog(intervaldays IN number) AS

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Starting 
prune errorlog at '||sysdate);

      prune_log('ERROR_LOG_TABLE', intervaldays, 'prune_errorlog.log');

   END;

PROCEDURE prune_fm(intervaldays IN number ) AS

             prunedate    date;

             counter      number :=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             TYPE ackwid_tab is TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;

             ackwid_tab_v ackwid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:= sysdate-intervaldays;

           dbms_output.put_line('Prune data before '||prunedate);

    create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Starting 
prune fm at '||sysdate);

           append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Prune 
FM data before '||prunedate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(ALARM_EVENT_TIME_INDEX) */ rowid from 
alarm_event_table

                     where  AlarmEventTimeStamp < prunedate and rownum <= 
rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from alarm_event_table at '|| sysdate);

           counter:=0;

           rows_processed:=0;

     LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(ACTIVE_ALARM_TIME_INDEX) */ ackindex, rowid from 
ACTIVE_ALARM_TABLE

                     where  ActiveAlarmTimeStamp < prunedate

                       and alarmstatus = 1

                       and ackindex <> 0

                       and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     ackwid_tab_v(counter) := i.ackindex;

                     IF counter = rows_to_be_deleted THEN

                       FOR rid IN 1 .. counter LOOP

                         DELETE FROM ACTIVE_ALARM_TABLE

                         WHERE rowid = rowid_tab_v(rid);

    DELETE FROM ACK_TABLE

                         WHERE ackindex = ackwid_tab_v(rid) AND ackindex <> -1;

                    END LOOP;

                    COMMIT;

                       rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                FOR rid IN 1 .. counter LOOP

                  DELETE FROM ACTIVE_ALARM_TABLE

                  WHERE rowid = rowid_tab_v(rid);

           DELETE FROM ACK_TABLE

                  WHERE ackindex = ackwid_tab_v(rid) AND ackindex <> -1;

               END LOOP;

                COMMIT;

                rows_processed:=rows_processed+counter;

                counter := 0;

              END IF;

              exit;

           END IF;

      END LOOP;

      append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from active_alarm_table at '||sysdate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(SYSLOG_MESSAGE_TS_I) */ rowid from 
SYSLOG_MESSAGE_TABLE

                     where  TimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted 
'||rows_processed||' rows from SYSLOG_MESSAGE_TABLE at '|| sysdate);

      append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','Finish 
prune FM at '||sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

        create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_fm.err','Error in 
pruning fm log '||SQLERRM||', '||SQLCODE);

   END;

PROCEDURE prune_admin_job_table( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');

           dbms_output.put_line('Prune data before '||prunedate);

	   
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Starti
ng prune admin job table at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Prune 
admin job  table before '||prunedate) ;

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(admin_job_ScheduledTIme_index) */ rowid  from 
ADMIN_JOB_TABLE

                     where  ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Delete
d '||rows_processed||' rows from ADMIN_JOB_TABLE at '|| sysdate);

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Finish 
prune admin job table at '||sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

               
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.err','Error 
in prune admin job  table '||SQLERRM||', '||SQLCODE);

   END;

END prune;

show errors;

3.1.5 Security_Pkg

CREATE OR REPLACE PACKAGE security_pkg as

   type      security_tab_type is table of rowid index by binary_integer;

   security_tab   security_tab_type;

   security_index binary_integer;

   procedure sysAdminEnable;

   procedure sysAdminDisable;

end security_pkg;

show errors;

3.2 CTM Stored Procedures

This section describes the stored procedures that are defined in CTM tables. The procedures are listed in alphabetical order.

3.2.1 Add_Circuit_Ctps_To_Met

CREATE or REPLACE PROCEDURE add_circuit_ctps_to_met

    p_CktNodeId IN NUMBER,

    p_CktUniqueId IN NUMBER

AS

    p_CustomerId varchar2(256);

    p_ServiceId varchar2(256);

    p_CktType number(2);

    p_PHYSICALLOC number(20);

    p_ifindex number(20);

    p_objecttype number(10);

    p_MODULETYPE number(8);

    p_NEDBAccessID number(10);

BEGIN

    BEGIN

        select c.CktType, c.CustomerId, c.ServiceId,

               c.CKTSRCMODULETYPE,

               c.CKTSRCPHYSICALLOC,

               c.CKTSRCIFINDEX,

               c.CKTSRCOBJECTTYPE, n.NEDBAccessID

          into p_CktType, p_CustomerId, p_ServiceId, p_MODULETYPE,

               p_PHYSICALLOC, p_ifindex, p_objecttype, p_NEDBAccessID

          from circuit_tbl c, ne_info_table n

         where c.CktNodeId = p_CktNodeId

           and c.CktUniqueId = p_CktUniqueId

           and c.CKTSRCNODEID = n.NENodeID

           and c.CKTSRCNODEID != 0;

        EXCEPTION

            WHEN NO_DATA_FOUND then

                goto END_OF_PROCEDURE;

    END;

    BEGIN

        insert into Managed_Et_tab

            Mng_Entity_Type,

            Mng_Entity_Id_Num,

            Mng_Entity_Id2_Num,

            NeDBAccessId,

            FIELD1_Num,

            FIELD2_Num,

            FIELD3_Num,

            FIELD4_Num,

            Customer_Id_Str,

            Service_Id_Str

        values

            p_CktType,

            p_CktNodeId,

            p_CktUniqueId,

            p_NEDBAccessID,

            p_MODULETYPE,

            p_PHYSICALLOC,

            p_ifindex,

            p_objecttype,

            p_CustomerId,

            p_ServiceId

);

        EXCEPTION

            WHEN DUP_VAL_ON_INDEX then

                update Managed_Et_tab

set

                    FIELD4_Num = p_objecttype,

                    Customer_Id_Str = p_CustomerId,

                    Service_Id_Str = p_ServiceId

                where Mng_Entity_Type = p_CktType

                  and Mng_Entity_Id_Num = p_CktNodeId

                  and Mng_Entity_Id2_Num = p_CktUniqueId

                  and NeDBAccessId = p_NEDBAccessID

                  and FIELD1_Num = p_MODULETYPE

                  and FIELD2_Num = p_PHYSICALLOC

                  and FIELD3_Num = p_ifindex;

    END;

    BEGIN

        select c.CKTSECSRCMODULETYPE,

               c.CKTSECSRCPHYSICALLOC,

               c.CKTSECSRCIFINDEX,

               c.CKTSECSRCOBJECTTYPE, n.NEDBAccessID

          into p_MODULETYPE,

               p_PHYSICALLOC, p_ifindex, p_objecttype, p_NEDBAccessID

          from circuit_tbl c, ne_info_table n

         where c.CktNodeId = p_CktNodeId

           and c.CktUniqueId = p_CktUniqueId

           and c.CKTSECSRCNODEID = n.NENodeID

           and c.CKTSECSRCNODEID != 0;

        EXCEPTION

            WHEN NO_DATA_FOUND then

                goto NEXT_SELECTION;

    END;

    BEGIN

        insert into Managed_Et_tab

            Mng_Entity_Type,

            Mng_Entity_Id_Num,

            Mng_Entity_Id2_Num,

            NeDBAccessId,

            FIELD1_Num,

            FIELD2_Num,

            FIELD3_Num,

            FIELD4_Num,

            Customer_Id_Str,

            Service_Id_Str

        values

            p_CktType,

            p_CktNodeId,

            p_CktUniqueId,

            p_NEDBAccessID,

            p_MODULETYPE,

            p_PHYSICALLOC,

            p_ifindex,

            p_objecttype,

            p_CustomerId,

            p_ServiceId

);

        EXCEPTION

            WHEN DUP_VAL_ON_INDEX then

                update Managed_Et_tab

set

                    FIELD4_Num = p_objecttype,

                    Customer_Id_Str = p_CustomerId,

                    Service_Id_Str = p_ServiceId

                where Mng_Entity_Type = p_CktType

                  and Mng_Entity_Id_Num = p_CktNodeId

                  and Mng_Entity_Id2_Num = p_CktUniqueId

                  and NeDBAccessId = p_NEDBAccessID

                  and FIELD1_Num = p_MODULETYPE

                  and FIELD2_Num = p_PHYSICALLOC

                  and FIELD3_Num = p_ifindex;

    END;

    <<NEXT_SELECTION>>

        null;

    FOR rec IN (

        select t.CktDestModuleType, t.CktDestPhysicalLoc, t.CktDestIfIndex,

               t.CktDestObjectType, n.NEDBAccessID

          from circuit_dest_tbl t, ne_info_table n

         where t.CktNodeId = p_CktNodeId

           and t.CktUniqueId = p_CktUniqueId

           and t.CktDestNodeId = n.NENodeID

           and t.CktDestNodeId != 0

    LOOP

        BEGIN

            insert into Managed_Et_tab

                Mng_Entity_Type,

                Mng_Entity_Id_Num,

                Mng_Entity_Id2_Num,

                NeDBAccessId,

                FIELD1_Num,

                FIELD2_Num,

                FIELD3_Num,

                FIELD4_Num,

                Customer_Id_Str,

                Service_Id_Str

            values

                p_CktType,

                p_CktNodeId,

                p_CktUniqueId,

                rec.NEDBAccessID,

                rec.CktDestModuleType,

                rec.CktDestPhysicalLoc,

                rec.CktDestIfIndex,

                rec.CktDestObjectType,

                p_CustomerId,

                p_ServiceId

);

            EXCEPTION

                WHEN DUP_VAL_ON_INDEX then

                    update Managed_Et_tab

set

                        FIELD4_Num = rec.CktDestObjectType,

                        Customer_Id_Str = p_CustomerId,

                        Service_Id_Str = p_ServiceId

                    where Mng_Entity_Type = p_CktType

                      and Mng_Entity_Id_Num = p_CktNodeId

                      and Mng_Entity_Id2_Num = p_CktUniqueId

                      and NeDBAccessId = rec.NEDBAccessID

                      and FIELD1_Num = rec.CktDestModuleType

                      and FIELD2_Num = rec.CktDestPhysicalLoc

                      and FIELD3_Num = rec.CktDestIfIndex;

        END;

    END LOOP;

    FOR rec IN (

        select v.DESTNEMODULETYPE CktDestModuleType,

               v.DESTPHYSICALLOC CktDestPhysicalLoc,

               v.INTERFACEID CktDestIfIndex,

               v.DESTOBJECTTYPE CktDestObjectType, v.DESTNEDBACCESSID NEDBAccessID

          from circuit_span_view v

         where v.NODEID = p_CktNodeId

           and v.UNIQUEID = p_CktUniqueId

    LOOP

        BEGIN

            insert into Managed_Et_tab

                Mng_Entity_Type,

                Mng_Entity_Id_Num,

                Mng_Entity_Id2_Num,

                NeDBAccessId,

                FIELD1_Num,

                FIELD2_Num,

                FIELD3_Num,

                FIELD4_Num,

                Customer_Id_Str,

                Service_Id_Str

            values

                p_CktType,

                p_CktNodeId,

                p_CktUniqueId,

                rec.NEDBAccessID,

                rec.CktDestModuleType,

                rec.CktDestPhysicalLoc,

                rec.CktDestIfIndex,

                rec.CktDestObjectType,

                p_CustomerId,

                p_ServiceId

);

            EXCEPTION

                WHEN DUP_VAL_ON_INDEX then

                    update Managed_Et_tab

set

                        FIELD4_Num = rec.CktDestObjectType,

                        Customer_Id_Str = p_CustomerId,

                        Service_Id_Str = p_ServiceId

                    where Mng_Entity_Type = p_CktType

                      and Mng_Entity_Id_Num = p_CktNodeId

                      and Mng_Entity_Id2_Num = p_CktUniqueId

                      and NeDBAccessId = rec.NEDBAccessID

                      and FIELD1_Num = rec.CktDestModuleType

                      and FIELD2_Num = rec.CktDestPhysicalLoc

                      and FIELD3_Num = rec.CktDestIfIndex;

        END;

    END LOOP;

    <<END_OF_PROCEDURE>>

        null;

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

END;

3.2.2 Add_Log_Partition

Note The add_log_partition procedure applies only to medium, large, and high-end configurations.

create or replace procedure add_log_partition AS

        partition_to_be_added     varchar2(9);

        cursor_handle   integer;

        statement_txt varchar2(500);

        rows_processed  number := 0;

        p_tablespace_name varchar2(25);

        new_partition_date date;

        new_partition_name varchar2(9);

        ORACLE_SID    varchar2(20);

        ORACLE_BASE   varchar2(20);

        add_flag number;

BEGIN

          execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

           WHERE sectionname='database' and propertyname='dbname';

	   SELECT activevalue INTO ORACLE_BASE

            FROM ctm_config_table

           WHERE sectionname='database' and propertyname='db-oracle-base';

          
           create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_log_partition.log
           ','Starting adding partition at '||sysdate);

           FOR rec IN ( SELECT table_name FROM user_tables

                        WHERE table_name in (

                          'ERROR_LOG_TABLE', 'SERVER_MONITOR_TABLE',

                          'TRANSACTION_LOG_TABLE','SYSLOG_MESSAGE_TABLE',

                          'NE_AUDIT_TRAIL_TABLE', 'ONS158XX_LAC_LOG_TABLE'

))

          LOOP

                add_flag:=1;

                /* define date range for the partition to be added */

                Select max(to_date( substr(partition_name,2,9))),tablespace_name

                  INTO new_partition_date, p_tablespace_name

                From user_segments

                Where segment_name =rec.table_name

                group by tablespace_name;

                IF (sysdate >= new_partition_date) THEN

                    new_partition_date:=sysdate+1;

                ELSIF (new_partition_date >= sysdate+3) THEN

                   add_flag:=0;

                Else

                    new_partition_date:= new_partition_date+1;

                END IF;

                IF (add_flag=1) THEN

                    partition_to_be_added:= 'p'||to_char(new_partition_date,'mmddyyyy');

                    /* add the new partition */

                    cursor_handle := DBMS_SQL.OPEN_CURSOR;

                    statement_txt := 'alter table '||rec.table_name ||' add partition 
'||partition_to_be_added||

                                    ' values less than 
('||''''||new_partition_date||''''||')

                                      TABLESPACE '||p_tablespace_name;

                    DBMS_OUTPUT.PUT_LINE(statement_txt);

                    DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

                    rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                    DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                    
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_log_partition.log','added 
partition '||partition_to_be_added||' to '||rec.table_name);

                ELSE

                    
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_log_partition.log',rec.table_
name||' already has log partitions for the next 3 days');

                END IF;

          END LOOP;

                
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_log_partition.log','Adding 
partition successfully finished at '||sysdate);

EXCEPTION

         WHEN OTHERS THEN

         
create_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_log_partition.err','Error in 
adding pm partition  '||SQLERRM||', '||SQLCODE);

END;

3.2.3 Add_PM_Partition

Note The add_pm_partition procedure applies only to medium, large, and high-end configurations.

create or replace procedure add_pm_partition AS

        partition_to_be_added     varchar2(9);

        subpartition_15min_to_be_added     varchar2(15);

        subpartition_1day_to_be_added     varchar2(14);

        cursor_handle   integer;

        statement_txt varchar2(500);

        rows_processed  number := 0;

        p_tablespace_name varchar2(25);

        new_partition_date date;

        new_partition_name varchar2(9);

        ORACLE_SID    varchar2(20);

        ORACLE_BASE    varchar2(20);

        pm_service_enabled varchar2(15);

        add_flag number;

        rows_services number := 0;

BEGIN

          execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

          WHERE sectionname='database' and propertyname='dbname';

          SELECT activevalue INTO ORACLE_BASE

            FROM ctm_config_table

          WHERE sectionname='database' and propertyname='db-oracle-base';

          
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','Starting 
adding partition at '||sysdate);

--

	  --FOR rec IN ( SELECT table_name FROM user_tables

          --              WHERE table_name like '%PM_TABLE'

          --                AND TABLE_NAME !='ONS158XX_TIMESTAMP_PM_TABLE')

--

	  FOR rec IN ( SELECT DISTINCT ust.table_name FROM user_tables ust , 
pmservice_associated_tab_table pm WHERE ust.table_name = pm.table_name AND pm.service_type 
<> 'NONE')

          LOOP

                add_flag:=1;

                        rows_services := rows_services + 1;

                /* define date range for the partition to be added */

		
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_pm_partition.log',rec.table_
name||' processing.....');

Select max(to_date( substr(partition_name,2,9),'MMDDYYYY')),tablespace_name

                  INTO new_partition_date, p_tablespace_name

                From user_tab_partitions

                Where table_name =rec.table_name

                group by tablespace_name;

                IF (sysdate >= new_partition_date) THEN

                    new_partition_date:=sysdate+1;

                ELSIF (new_partition_date >= sysdate+3) THEN

                   add_flag:=0;

                Else

                    new_partition_date:= new_partition_date+1;

                END IF;

                -- check whether pm-service is enabled, if no, no need to add partitions

		DECLARE

		  CURSOR pm_service_curs IS select table_name, service_type from 
PMSERVICE_ASSOCIATED_TAB_TABLE where table_name = rec.table_name;

                  pmserv_rec pm_service_curs%ROWTYPE;

                begin

		  FOR pmserv_rec IN pm_service_curs

                  LOOP

                   select activevalue INTO pm_service_enabled

		      from ctm_config_table

		      where sectionname = pmserv_rec.service_type and propertyname ='pm-service';

		   exit when pm_service_enabled = 'active';

		  END LOOP;

                end;

                IF (pm_service_enabled ='active' and add_flag=1) THEN

		   
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_pm_partition.log',rec.table_n
ame||' PM Service associated ACTIVE');

                   partition_to_be_added:= 'p'||to_char(new_partition_date,'mmddyyyy');

                   subpartition_15min_to_be_added := 
'p15min_'||to_char(new_partition_date,'mmddyyyy');

                   subpartition_1day_to_be_added := 
'p1day_'||to_char(new_partition_date,'mmddyyyy');

                   /* add the new partition */

                   cursor_handle := DBMS_SQL.OPEN_CURSOR;

                   statement_txt := 'alter table '||rec.table_name ||' add partition 
'||partition_to_be_added||

                                    ' values less than 
('||''''||new_partition_date||''''||')'

                                      || ' TABLESPACE '||p_tablespace_name;

                   statement_txt := statement_txt || ' ('

                                     || ' subpartition ' || subpartition_15min_to_be_added 
||' values(0),'

                                     || ' subpartition ' || subpartition_1day_to_be_added 
||' values(1)'

                                     || ')';

                   --DBMS_OUTPUT.PUT_LINE(statement_txt);

		   
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_pm_partition.log',' DDL 
command = '||statement_txt);

                   DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

                   rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','added 
partition '||partition_to_be_added||' to '||rec.table_name);

               ELSE

                    IF (add_flag = 0 ) THEN

                       
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_pm_partition.log',rec.table_n
ame||' already has partitions for the next 3 days');

                    ELSE

                       
append_file('/ORACLE_BASE/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','pm-service 
is not enabled for this NE Type, no need to add partition to '||rec.table_name||' at this 
time');

                    END IF;

               END IF;

          END LOOP;

IF (rows_services = 0) THEN

	        
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','Error in 
adding  partition caused by problem on configuration table ');

	  ELSE

                
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','Adding 
partition successfully finished at '||sysdate);

          END IF;

EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','add_pm_partition.err','Error in 
adding pm partition  '||SQLERRM||', '||SQLCODE);

END;

show errors;

3.2.4 Alter_User_Permission

create or replace PROCEDURE alter_user_permission

       (UserTypeID   IN   number,

        AddOrRemove  IN   varchar2,

        error_msg    OUT  varchar2)

IS

        r_userid         number(4);

        remove_failed    EXCEPTION;

        add_failed       EXCEPTION;

BEGIN

        IF (AddOrRemove = 'Remove') THEN

           FOR rec IN (select userid FROM user_table

                        where subtypeofUser=userTypeId) LOOP

                      Begin

                        DELETE FROM user_map_table

                         WHERE userid=rec.userid

                           AND groupOrNeid=-2;

                      Exception

                        WHEN OTHERS THEN

                             error_msg:= 'Remove failed for userid '||rec.userid;

                             raise remove_failed;

                      END;

           END LOOP;

           commit;

        END IF;

        IF (AddOrRemove = 'Add') THEN

           FOR rec IN (select userid FROM user_table

                        where subTypeOfUser=UserTypeId) LOOP

                       Begin

                            INSERT INTO user_map_table

                            VALUES (rec.userid, 2, -2);

                       Exception

                            WHEN OTHERS THEN

                                 error_msg:= 'Add failed for userid '||rec.userid;

                                 raise add_failed;

                       END;

           END LOOP;

           commit;

        END IF;

END;

3.2.5 Append_File

create or replace PROCEDURE append_file

       (loc_in IN varchar2, file_in IN varchar2, line_in IN varchar2 :=null)

IS

       file_handle utl_file.file_type;

BEGIN

       file_handle := utl_file.fopen (loc_in, file_in,'A');

       utl_file.put_line(file_handle,line_in);

       utl_file.fclose(file_handle);

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

            dbms_output.put_line('Error in appending log file');

END;

3.2.6 BulkNEAddition

create or replace procedure BulkNEAddition

          (OperationType  IN  number,

           GroupToBeInserted  IN number,

           GroupName          IN varchar2,

           GroupDescription   IN varchar2,

           GroupLocationName  IN varchar2,

           GneToBeInserted    IN number,

           GneSysId           IN varchar2,

           NextHopIpAddr      IN number,

           GroupingOption     IN number,

           GroupPrefix        IN varchar2,

           inUserId             IN number,

           SubNWToBeInserted  IN number,

           SubNwName          IN OUT varchar2,

           SubnetType         IN number,

           SubnetTopology     IN number,

           NWPartitionId      IN number,

		   Is216NE			  IN number,

           InNeSysId            IN varchar2,

           Ipaddr             IN number,

           OperationalState   IN number,

           IsConnected        IN number,

           GneId              IN OUT number,

           NeDescription      IN varchar2,

           NeConfigMode       IN number,

           SnmpCommunityString  IN varchar2,

           ClliCode             IN varchar2,

           NeModelType          IN number,

           NeModelIndex         IN number,

           SubNwId              IN OUT number,

           SystemTitle          IN varchar2,

           UserLabel            IN varchar2,

           VendorName           IN varchar2,

           VersionName          IN varchar2,

           NePmEnabled          IN number,

           DisplayModelName     IN varchar2,

           GwTL1UserName        IN varchar2,

           GwTL1Password        IN varchar2,

	   	   NeDiscState			IN number,

	   	   pmCollectionFlag		IN number,

	   PmTicket		IN varchar2,

           LaunchContext        IN number,

           ParentGroupId        IN number,

           NewGroupId           OUT number,

           NeId                 OUT number,

           error_code           OUT number,

           error_msg            OUT varchar2,

           inNEAliasID            IN varchar2,

           inRoleType             IN varchar2,

           NeVirtualIp          IN number)

IS

           i_groupid            number;

           i_gneid              number;

           i_subNWName          varchar2(64);

           i_subnetid           number;

           i_Neid               number;

           i_parenttype         number;

           assigned_group       number;

           UserProperty         number;

           counter              number:=0;

           i_addnewsubnet       number:=0;

           BulkNeAdd_FAILED          EXCEPTION;

BEGIN

           IF (GroupToBeInserted = 1) THEN

              SELECT groupId_seq.nextval INTO i_groupId from dual;

              INSERT INTO group_info_table

              VALUES(i_groupid, GroupName,GroupDescription,'',GroupLocationName);

           END IF;

           IF (GneToBeInserted = 1) THEN

              SELECT GNEID_SEQ.nextval INTO i_gneid from dual;

              INSERT INTO gne_table

              VALUES(i_gneid, 0,1, GneSysid,NextHopIpaddr,'',GroupingOption,GroupPrefix, 
inUserId);

           ELSE

               i_gneid:=GneId;

           END IF;

           i_addnewsubnet:= SubNWToBeInserted;

           IF (i_addnewsubnet = 0) THEN

             SELECT count(*) INTO counter from subnetwork_table WHERE subnetid=SubNwId;

             IF (counter = 0) THEN

               i_addnewsubnet:=1;

			 ELSE

			   i_addnewsubnet:=0;

             END IF;

           END IF;

           IF (i_addnewsubnet = 1) THEN

              SELECT SUBNETID_SEQ.nextval INTO i_subnetid FROM dual;

              IF (subNwName is null) THEN

                 SELECT 'Subnetwork-'||lpad(i_subnetid,8,'0')

                   INTO i_subNwName from dual;

                 SELECT count(*) INTO counter from subnetwork_table

                  WHERE subnetname=i_subNwName;

                 IF (counter>0) THEN

                    i_subNwName:=i_subNwName||'-1';

                 END IF;

              ELSE

                  i_subNWName:=subNwName;

              END IF;

              INSERT INTO subnetwork_table

              VALUES (i_subnetid, i_subNWName,'','',SubnetType, 
SubnetTopology,-1,NWPartitionId);

           ELSE

               i_subnetid :=SubNwId;

           END IF;

           SELECT NEDBACCESSID_SEQ.nextval INTO i_neid FROM dual;

           INSERT INTO ne_info_table

           VALUES (i_neid,

                   InNeSysID,

                   IpAddr,

'',

                   OperationalState,

                   isConnected,

                   i_gneid,

                   NeDescription,

                   neConfigMode,

                   SnmpCommunityString,

                   ClliCode,

'',

                   NeModelType,

                   NeModelIndex,

0,

                   i_subnetid,

0,

'',

                   systemTitle,

                   userLabel,

                   vendorName,

                   versionName,

0,

0,

0,

                   nepmenabled,

0,

0,

0,

0,

0,

0,

0,

0,

                   DisplayModelName,

                   GwTL1UserName,

                   GwTL1Password,

0,

0,

0,

                   null,

                   null,

		   NeDiscState,

		   pmCollectionFlag,

		   PmTicket,

'',

-1,

'',

0,

0,

0,

                    i_neid,

                   decode(inNEAliasID, null, InNESysid, inNEAliasID),

0,

                   inRoleType,

		   NeVirtualIp,

0);

			IF( Is216NE = 1 ) THEN

				UPDATE NE_INFO_TABLE set NEIPADDR=''

					WHERE NESYSID=InNeSysId;

			END IF;

         BEGIN

            SELECT Properties INTO userProperty

              FROM user_type_table

             WHERE usertypeid = ( select subtypeofuser from user_table where userid = 
InUserId);

            IF (userProperty =2) THEN

               SELECT count(*) INTO assigned_group

                      FROM user_map_table

                     WHERE userid=inUserid

                       AND (GroupOrNEID IN ( SELECT parentid FROM domain_table

                                             WHERE childType=2

                                            START WITH parentid=parentGroupid

                                            CONNECT by prior parentid=childid)

                        OR GroupOrNEID =parentGroupid);

               IF (assigned_group = 0) THEN

                  IF (GroupToBeInserted =1) THEN

                     INSERT INTO user_map_table

                     VALUES (inUserId, 2, i_groupid);

                  ELSE

                     INSERT INTO user_map_table

                     VALUES(inUserId, 3, i_neid);

                  END IF;

               END IF;

           END IF;

       Exception

           WHEN NO_DATA_FOUND

           THEN null;

       END;

           IF (parentGroupId = 0) THEN

                 i_parenttype:=1;

           ELSE

                 i_parenttype:=2;

           END IF;

           IF (GroupToBeInserted = 1 ) THEN

              insert into domain_table

              values(DT_TREENODEID_SEQ.nextval, i_parenttype, parentGroupid, 2, 
i_groupid);

              INSERT INTO domain_table

              VALUES (DT_TREENODEID_SEQ.nextval,  2, i_groupid, 3,i_neid);

           ELSE

              INSERT INTO domain_table

              VALUES (DT_TREENODEID_SEQ.nextval,  i_parenttype, parentGroupid, 3,i_neid);

           END IF;

           INSERT INTO Cerent_Ne_Group_Table VALUES (i_neid, GroupingOption);

           commit;

           neId:= i_neid;

           newGroupId:=i_groupid;

           subNwname:=i_subNwName;

           subNwId:=i_subnetId;

           GneId:=i_GneId;

 --create_file('/oraclesw9i','bulkneadd.log','neid:'||neId||' newGroupId:'||newGroupId||' 
subNwname:'||subNwname||' subNwId:'||subNwId||' GneId:'||GneId);

EXCEPTION

         WHEN OTHERS THEN

              error_code := SQLCODE;

              error_msg:= SQLERRM;

              raise BulkNeAdd_Failed;

              rollback;

END;

3.2.7 Clear_AllUnack_Alarms

CREATE or REPLACE procedure clear_allunack_alarms

IS

    counter number :=0;

BEGIN

    UPDATE ctm_config_table

       SET activevalue=1

     WHERE propertyname='clear-unack-alarms'

       AND sectionname='ui';

    commit;

    FOR rec IN (select rowid from active_alarm_table

                   where alarmstatus=1

                     and ackindex=0 ) LOOP

         UPDATE active_alarm_table

            SET ackindex=-1

          WHERE rowid=rec.rowid;

         counter:=counter+1;

         IF mod(counter,1000)=0 THEN

            commit;

         END IF;

    END LOOP;

    dbms_output.put_line('Total '||counter||' cleared alarms are autoacknowledged');

    commit;

    UPDATE ctm_config_table

       SET activevalue=0

     WHERE propertyname='clear-unack-alarms'

       AND sectionname='ui';

END;

3.2.8 Create_File

create or replace PROCEDURE create_file

       (loc_in IN varchar2, file_in IN varchar2, line_in IN varchar2 :=null)

IS

       file_handle utl_file.file_type;

BEGIN

       file_handle := utl_file.fopen (loc_in, file_in, 'W');

       IF line_in IS NOT NULL THEN

          utl_file.put_line(file_handle, line_in);

       ELSE

          utl_file.put_line(file_handle, 'Nothing to write');

       END IF;

       utl_file.fclose(file_handle);

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

            dbms_output.put_line('Error in creating log file');

END;

3.2.9 Execute_Dml_Statement

CREATE or REPLACE procedure execute_dml_statement

    p_statement_txt in varchar2

IS

    pragma AUTONOMOUS_TRANSACTION;

BEGIN

    BEGIN

        execute immediate p_statement_txt;

    END;

END;

3.2.10 Execute_Statement

CREATE or REPLACE PROCEDURE execute_statement

(p_statement_txt1  varchar2,

 param1          varchar2,

 p_statement_txt2  varchar2,

 param2         varchar2,

 p_statement_txt3  varchar2)

IS

   cursor_handle  number:=DBMS_SQL.OPEN_CURSOR;

   rows_processed number:=0;

   statement_txt  varchar2(500);

BEGIN

   statement_txt := p_statement_txt1 ||param1||P_statement_txt2|| param2 
||p_statement_txt3;

   dbms_output.put_line(statement_txt);

   DBMS_SQL.PARSE (cursor_handle, statement_txt, DBMS_SQL.NATIVE);

   rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

   DBMS_SQL.CLOSE_CURSOR(cursor_handle);

EXCEPTION

   WHEN OTHERS THEN

        IF DBMS_SQL.IS_OPEN(cursor_handle) THEN

           DBMS_SQL.CLOSE_CURSOR(cursor_handle);

        END IF;

END execute_statement;

3.2.11 Login_Disable

The Login_Disable procedure disables a user's login if that user does not log in for a specified period of time.

CREATE OR REPLACE PROCEDURE Login_Disable

IS

   Baseline       date;

   difference     number;

   CURSOR usertable IS

   SELECT * FROM USER_TABLE;

BEGIN

  for rec in usertable loop

   if rec.AUTODISABLEINTERVAL <> 0 then

     if rec.LASTLOGINTIME is NULL then

      Baseline := rec.PASSWORDSETTIME;

     else

      Baseline := rec.LASTLOGINTIME;

     end if;

         difference := sysdate - Baseline;

     if (difference >= rec.AUTODISABLEINTERVAL) then

     update user_table set USERLOGINDISABLED = 1 where userid = rec.userid;

	 commit;

     END IF;

   END IF;

  END LOOP;

END Login_Disable;

3.2.12 PortName

CREATE or REPLACE PROCEDURE PortName

          (p_nedbaccessid IN number,

           p_physicalloc IN number,

           p_ifindex IN number,

           p_moduletype IN number,

           p_objectindex IN number,

           p_portname OUT varchar2)

IS

   o_portname   varchar2(256);

BEGIN

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND ifindex=p_ifindex

           AND moduleType=p_moduletype

           AND objectindex=p_objectindex;

        p_portname := o_portname;

  EXCEPTION

       WHEN TOO_MANY_ROWS

       THEN null;

END PortName;

3.2.13 Prune_Audittrail

3.2.13.1 Prune_Audittrail Procedure for Small Configurations

CREATE or REPLACE PROCEDURE prune_audittrail( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

             rows_to_be_deleted  NUMBER := 100000 ;

             ORACLE_SID      varchar2(40);

	     ORACLE_BASE   varchar2(20);

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

      WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

      WHERE sectionname='database' and propertyname='db-oracle-base';

      IF ( intervaldays != -1 ) THEN

           execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

           prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');

           dbms_output.put_line('Prune data before '||prunedate);

           
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Starting 
prune audittrail at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Prune 
audit trail table before '||prunedate);

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(NE_AUDIT_TRAIL_TS_I) */ rowid from 
NE_Audit_Trail_Table

                     where  TIMESTAMP < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM NE_Audit_Trail_Table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM NE_Audit_Trail_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Deleted 
'||rows_processed||' rows from NE_Audit_Trail_Table at '|| sysdate);

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Finish 
prune audittrail at '||sysdate);

     LOOP

           counter:=0;

           FOR i IN (select /*+ INDEX_FFS(ONS158XX_LACLOG_TIMESTAMP_I) */ rowid from 
ONS158XX_LAC_LOG_TABLE

                     where  DateTime < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_LAC_LOG_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_LAC_LOG_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

     
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Deleted 
'||rows_processed||' rows from ONS158XX_LAC_LOG_TABLE at '|| sysdate);

   ELSE

               dbms_output.put_line('User choose to keep all data, no prune at this 
time');

               
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','User 
choose to keep all data, no prune');

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

         
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_audittrail.err','Error in 
audit trail log '||SQLERRM||', '||SQLCODE);

   END prune_audittrail;

show error;

3.2.13.2 prune_audittrail Procedure for Medium, Large, and High-End Configurations

CREATE OR REPLACE PROCEDURE prune_audittrail( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

             ORACLE_SID  varchar2(40);

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      IF ( intervaldays != -1 ) THEN

           prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');

           dbms_output.put_line('Prune data before '||prunedate);

           
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','Starting 
prune audittrail at '||sysdate);

           prune.prune_log('NE_AUDIT_TRAIL', intervaldays, 'prune_audittrail.log');

           prune.prune_log('ONS158XX_LAC_LOG_TABLE', intervaldays, 
'prune_audittrail.log');

     ELSE

           dbms_output.put_line('User choose to keep all data, no prune at this time');

           
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_audittrail.log','User choose 
to keep all data, no prune');

     END IF;

     EXCEPTION

         WHEN OTHERS THEN

         
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_audittrail.err','Error in 
audit trail log '||SQLERRM||', '||SQLCODE);

   END;

show error;

3.2.14 Prune_NE

create or replace procedure prune_ne

     (p_nesysid    IN varchar2)

IS

      p_nedbaccessid        number;

      p_nenodeid            number;

      p_neipaddr            number;

      prune_state           number:=0;

      prune_process_state   number :=0;

      counter               number:=0;

      rows_deleted          number:=3000;

      rows_processed        number:=0;

      rows_deleted_total    number:=0;

      cursor_handle         INTEGER ;

      statement_txt         varchar2(500);

      prune_date            varchar2(30);

      ORACLE_SID            varchar2(20);

      ORACLE_BASE           varchar2(20);

      CURSOR IsGne(p_nesysid IN ne_info_table.nesysid%TYPE) IS

      SELECT gneid

        FROM gne_table

       WHERE gnesysid=p_nesysid;

      r_gneid ne_info_table.gneid%TYPE;

      INVALID_NESYSID       EXCEPTION;

      PRAGMA EXCEPTION_INIT (INVALID_NESYSID, -20003);

      PRUNE_FAILED          EXCEPTION;

      PRAGMA EXCEPTION_INIT (PRUNE_FAILED, -20004);

BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='dbname';

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

       WHERE sectionname='database' and propertyname='db-oracle-base';

      /* Check if this ne is a GNE and if it has nes  */

      OPEN IsGne(p_nesysid);

      FETCH IsGne INTO r_gneid;

      IF IsGne%FOUND THEN

         dbms_output.put_line('This is a GNE , check if it has children');

         SELECT count(*) INTO counter

           FROM ne_info_table

          WHERE gneid=r_gneid ;

         IF (counter > 1 ) THEN

            dbms_output.put_line('This GNE has the following Children.');

            FOR rec IN (SELECT nesysid FROM ne_info_table

                         WHERE gneid=r_gneid and nesysid <> p_nesysid ) LOOP

                dbms_output.put_line('NE: '||rec.nesysid);

            END LOOP;

            dbms_output.put_line('Can not delete a GNE that has children!');

            
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','Ca
n not delete a GNE that has children!');

            prune_process_state :=1;

         END IF;

      END IF;

      CLOSE IsGne;

      IF (prune_process_state=0) THEN

          dbms_output.put_line('process prune this nesysid');

          SELECT to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') INTO prune_date FROM dual;

          
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','Pr
ocess prune '||p_nesysid||' at '||prune_date);

          /* get the nedbaccessid from nesysid  */

           SELECT nedbaccessid, nenodeid, neipaddr INTO p_nedbaccessid, p_nenodeid, 
p_neipaddr

             FROM ne_info_table

            WHERE nesysid=p_nesysid;

          /* get a list of table name that has nedbaccessid column from data dictionary

          || and prune data from each table

*/

           FOR i IN ( SELECT a.table_name,a.column_name

                        FROM user_tab_columns a,

                             user_objects    b

                       WHERE a.table_name = b.object_name

                         AND b.object_type='TABLE'

                         AND (

                            (a.column_name like 'NEDBACCESSID%' and a.column_name != 
'NEDBACCESSIDCOLINDB') OR

                            (a.column_name like '%NODE_ID%' and a.table_name like 
'%_DATA') OR

                            (a.column_name like 'NODE_ID%' and a.table_name

                                                                IN (

                                                                  'SCMNODE',

                                                                  'SCMENABLELIST',

                                                                  'COLL_ERR_LOG',

                                                                  'COLL_INFO',

                                                                  'COLL_INFO',

                                                                  'COLL_INFO',

                                                                  'COLL_SUMM_LIST',

                                                                  'COMP_FILE_LIST',

                                                                  'DESTROYNODE',

                                                                  'FILE_ERR_LOG',

                                                                  'FILE_INFO',

                                                                  'FILE_QUEUE_LOG',

                                                                  'FILE_TRANSFER_LOG',

                                                                  'PEND_FILE_LIST',

                                                                  'SCMCARDCOLL',

                                                                  'SCMCARDCOLLHOST',

                                                                  'SCMCARDCOLLSTATUS',

                                                                  'SCMCARDENABLE',

                                                                  'SCMDB_NODE_INFO',

                                                                  'SCMENABLELIST',

                                                                  'SCMENABLESUBTYPE',

                                                                  'SCMNODE',

                                                                  'SCMNODECOLL',

                                                                  'SCMNODECOLLHOST',

                                                                  'SCMNODECOLLSTATUS',

                                                                  'SCMNODEENABLE',

                                                                  'SYNC_INFO',

                                                                  'SYNC_LIST'

                            ) OR

                            a.column_name='CKTNODEID'    OR

                            a.column_name='CHILDID'      OR

                            a.column_name='GROUPORNEID')

                         ORDER BY table_name)

           LOOP

                     SELECT  decode(i.table_name,'NE_INFO_TABLE'   ,1,

                                                 'PURGED_NE_TABLE' ,1,

                                                 'USER_NE_TABLE'   ,1,

                                                                    0) INTO prune_state 
FROM dual;

                     IF (prune_state = 0 ) THEN

                        rows_deleted_total :=0;

                        cursor_handle := DBMS_SQL.OPEN_CURSOR;

                        IF (i.table_name='ACTIVE_ALARM_TABLE') THEN

                           statement_txt := 'DELETE FROM '||i.table_name ||' Where 
nedbaccessid = '||p_nedbaccessid ||

                                            ' OR (nedbaccessid=0 and activealarmtype not 
in (5003,5012) AND ModuleOrIfIndex='||p_nedbaccessid||' )

                                             AND rownum < = '||rows_deleted;

                        END IF;

                        IF (i.column_name like 'NEDBACCESSID%' and 
i.table_name<>'ACTIVE_ALARM_TABLE') THEN

                            statement_txt := 'DELETE FROM '||i.table_name ||' Where 
'||i.column_name ||' = '||p_nedbaccessid ||

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

                        IF (i.column_name like '%NODE_ID%' and 
i.table_name<>'ACTIVE_ALARM_TABLE') THEN

                            statement_txt := 'DELETE FROM '||i.table_name ||' Where 
'||i.column_name ||' = '||p_nedbaccessid ||

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

                        -- purge all circuit tables based on cktnodeid

                        IF (i.column_name='CKTNODEID' ) THEN

                            statement_txt := 'DELETE FROM '||i.table_name ||' Where 
cktnodeid = '||p_nenodeid ||

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

                        IF (i.column_name='CHILDID') THEN

                            statement_txt :='DELETE FROM '||i.table_name||

                                            ' WHERE childtype=3 AND childid = 
'||p_nedbaccessid||

                                            ' AND rownum < = '||rows_deleted;

                        END IF;

                        -- this is user_map_table

                        IF (i.column_name='GROUPORNEID') THEN

                            statement_txt := 'DELETE FROM '||i.table_name ||

                                             ' WHERE treenodetype=3 AND grouporneid = 
'||p_nedbaccessid||

                                             ' AND rownum <= '||rows_deleted;

                        END IF;

                        
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','**
* Executing { '|| statement_txt || ' }');

                        DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

                        LOOP

                             rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                             rows_deleted_total:=rows_deleted_total+rows_processed;

                             commit;

                             IF rows_processed=0 THEN

                                exit;

                             END IF;

                        END LOOP;

                        DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                        
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||rows_deleted_total||' from '||i.table_name);

                    END IF;

           END LOOP;

           /* prune CIRCUIT_DEST_TBL based on CktDestNodeId */

           counter:=0;

 	IF ( p_nenodeid != 0 ) THEN

           FOR rec IN ( SELECT rowid,cktnodeid,cktuniqueid FROM CIRCUIT_DEST_TBL

                         WHERE CktDestNodeId=p_nenodeid)

           LOOP

                      DELETE FROM CIRCUIT_DEST_TBL

                      WHERE  rowid= rec.rowid;

                      /* UPDATE state of the circuits associated with this destination to 
incomplete */

                      UPDATE circuit_tbl

                         SET CktState=4

                       WHERE cktnodeid=rec.cktnodeid

                         AND cktuniqueid=rec.cktuniqueid

                         AND CktState != 16;

                      counter := counter+1;

                      IF mod(counter,300) = 0 THEN

                         commit;

                      END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
CIRCUIT_DEST_TBL');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from CIRCUIT_DEST_TBL');

           /* prune CIRCUIT_SPAN_TBL based on CktSpanSrcNodeId and CktSpanDestNodeId */

           counter:=0;

           FOR rec IN ( SELECT rowid,cktnodeid, cktuniqueid FROM CIRCUIT_SPAN_TBL

                        WHERE  CktSpanSrcNodeId=p_nenodeid

                           OR  CktSpanDestNodeId=p_nenodeid)

           LOOP

                      DELETE FROM CIRCUIT_SPAN_TBL

                       WHERE rowid=rec.rowid;

                      /* UPDATE the state of the circuits associated with the span to 
incomplete */

                      UPDATE circuit_tbl

                         SET cktstate=4

                       WHERE cktnodeid=rec.cktnodeid

                         AND cktuniqueid=rec.cktuniqueid

                         AND cktstate != 16;

                      counter := counter+1;

                      IF mod(counter,300) = 0 THEN

                         commit;

                      END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
CIRCUIT_SPAN_TBL');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from CIRCUIT_SPAN_TBL');

 END IF;

           /* prune ONS155XX_GRANULAR_CC_TBL based on GCCSrcNodeId and GCCDestNodeId */

           counter:=0;

           FOR rec IN ( SELECT rowid FROM ONS155XX_GRANULAR_CC_TBL

                        WHERE  GCCSrcNodeId=p_nedbaccessid

                           OR  GCCDestNodeId=p_nedbaccessid)

           LOOP

                      DELETE FROM ONS155XX_GRANULAR_CC_TBL

                       WHERE rowid=rec.rowid;

                      counter := counter+1;

                      IF mod(counter,300) = 0 THEN

                         commit;

                      END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
ONS155XX_GRANULAR_CC_TBL');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from ONS155XX_GRANULAR_CC_TBL');

           /* prune ONS155XX_CIRCUIT_CC_TBL based on CktCCNodeId */

           counter:=0;

           FOR rec IN ( SELECT rowid,cktnodeid, cktuniqueid FROM ONS155XX_CIRCUIT_CC_TBL

                        WHERE  CktCCNodeId=p_nedbaccessid )

           LOOP

                      DELETE FROM ONS155XX_CIRCUIT_CC_TBL

                       WHERE rowid=rec.rowid;

                      /* UPDATE the state of the circuits associated with the span to 
incomplete */

                      UPDATE circuit_tbl

                         SET cktstate=4

                       WHERE cktnodeid=rec.cktnodeid

                         AND cktuniqueid=rec.cktuniqueid

                         AND cktstate != 16;

                      counter := counter+1;

                      IF mod(counter,300) = 0 THEN

                         commit;

                      END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
ONS155XX_CIRCUIT_CC_TBL');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from ONS155XX_CIRCUIT_CC_TBL');

           /* prune ONS155XX_PATH_TBL based on PATHSrcNodeId and PATHDestNodeId */

           counter:=0;

           FOR rec IN ( SELECT rowid FROM ONS155XX_PATH_TBL

                        WHERE  PathSrcNodeId=p_nedbaccessid

                           OR  PathDestNodeId=p_nedbaccessid)

           LOOP

                      DELETE FROM ONS155XX_PATH_TBL

                       WHERE rowid=rec.rowid;

                      counter := counter+1;

                      IF mod(counter,300) = 0 THEN

                         commit;

                      END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
ONS155XX_PATH_TBL');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from ONS155XX_PATH_TBL');

           /* prune data from link_table */

           counter:=0;

           FOR rec IN ( SELECT rowid FROM link_table

                         WHERE linksrcnode=p_nedbaccessid OR

                               linkdstnode=p_nedbaccessid)

           LOOP

                       DELETE FROM link_table

                       WHERE rowid = rec.rowid;

                       counter := counter+1;

                       IF mod(counter,300) = 0 THEN

                          commit;

                       END IF;

           END LOOP;

           --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
link_table');

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from link_table');

          /* prune data from ne_info_table  */

          counter := 0;

                      DELETE from ne_info_table

                       WHERE nedbaccessid=p_nedbaccessid;

                      counter := counter+1;

                         commit;

          --DBMS_output.PUT_LINE('------Deleted rows committed: '||counter||' from 
ne_info_table');

          
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from ne_info_table');

          /* prune proxy_server_table */

                    DELETE from proxy_server_table

                     WHERE neipaddr=p_neipaddr;

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','De
leted rows committed '||counter||' from proxy_server_table');

          /* prune gne table */

                   DELETE from gne_table

                    WHERE gnesysid=p_nesysid;

                    commit;

          prune_date:=to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');

          
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','pr
une '||p_nesysid||' is finished successfully at '||prune_date);

    END IF;

EXCEPTION

     WHEN NO_DATA_FOUND THEN

          DBMS_OUTPUT.PUT_LINE('No such network element');

          
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','No 
such network element');

          raise INVALID_NESYSID;

     WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE(SQLERRM);

          DBMS_OUTPUT.PUT_LINE(SQLCODE);

          DBMS_OUTPUT.PUT_LINE('Error in pruning NE!');

          
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','Er
ror in pruning NE!'||SQLERRM||','||SQLCODE);

          raise PRUNE_FAILED;

END;

show errors;

3.2.15 Prune_PM

3.2.15.1 Prune_PM Procedure for Small Configurations

declare

begin

     execute immediate 'create table  rowid_temp (myrowid  rowid)';

exception

     when OTHERS then

          null;

end;

Create or replace procedure prune_pm (

   intervaldays IN number,

   is24h_flag IN number default(2)

IS

       counter         number := 0;

       rows_processed  number := 0;

       cursor_handle   integer;

       cursor_handle1   integer;

       cursor_handle2   integer;

       statement_txt   varchar2(500);

       statement_txt1   varchar2(500);

       statement_txt2   varchar2(500);

       prunedate       date;

       rows_to_be_deleted number := 100000;

       rows_deleted_total number := 0;

       sec_convertor number :=86400;

       table_name varchar2(127);

       days number := 0;

       deleted_rowid    ROWID;

       ORACLE_SID       varchar2(20);

       ORACLE_BASE      varchar2(20);

       LOGDIR   varchar2(80);

       LOGFILE  varchar2(40);

	   mgm number;

	   deleted_total_rows  number;

   BEGIN

       SELECT activevalue INTO ORACLE_SID

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='dbname';

       SELECT activevalue INTO ORACLE_BASE

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='db-oracle-base';

       LOGDIR := ORACLE_BASE|| '/admin/' || ORACLE_SID || '/udump';

       if ( is24h_flag = 0 ) then

          LOGFILE := 'prune_15min_pm.log';

       elsif ( is24h_flag = 1 ) then

          LOGFILE := 'prune_1day_pm.log';

       elsif ( is24h_flag = 2 ) then

          LOGFILE := 'prune_both_pm.log';

       else

          LOGFILE := 'prune_pm.log';

       end if;

       IF ( intervaldays != -1) THEN

            prunedate:=sysdate-intervaldays;

            create_file(LOGDIR, LOGFILE,'Starting prune pm at '||sysdate);

            append_file(LOGDIR, LOGFILE,'prune pm data before '||prunedate);

            rows_processed:=0;

            if (is24h_flag = 0) then

                append_file(LOGDIR, LOGFILE,'pruning "15min" pm data only at '||sysdate);

            elsif (is24h_flag = 1) then

                append_file(LOGDIR, LOGFILE,'pruning "1day" pm data only at '||sysdate);

            else

                append_file(LOGDIR, LOGFILE,'pruning both "15min" and "1day" pm data at 
'||sysdate);

            end if;

----- MGX8880 PM {

	SELECT decode(instr(activevalue,'mgm'),0,0,1) INTO mgm FROM ctm_config_table

	 where sectionname='installation' and propertyname='modules';

	 IF ( mgm = 1) THEN

            -- delete entries in data tables

            FOR i IN ( SELECT a.table_name,a.column_name

                        FROM user_tab_columns a,

                             user_objects    b

                       WHERE a.table_name = b.object_name

                         AND b.object_type='TABLE'

                         AND (a.column_name like '%TIMESTAMP%' and a.table_name like 
'%_DATA')

            LOOP

                rows_deleted_total :=0;

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

                statement_txt := 'DELETE FROM '||i.table_name ||' WHERE '||i.column_name 
||' < ' ||sec_convertor|| '*(to_date(' ||

                                    '''' || prunedate || '''' || ', ' || '''' || 
'mm/dd/yyyy hh24:mi:ss' ||

                                    '''' || ') - to_date(' || '''' || '01/01/1970 
00:00:00' ||

                                    '''' || ', ' || '''' || 'mm/dd/yyyy hh24:mi:ss' || 
'''' || '))'

                                    || ' AND rownum <= '||rows_to_be_deleted;

--                append_file(LOGDIR,LOGFILE,'*** Executing { '|| statement_txt || ' }');

                DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

                LOOP

                    rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                    rows_deleted_total:=rows_deleted_total+rows_processed;

                    commit;

                    IF rows_processed=0 THEN

                      exit;

                    END IF;

                END LOOP;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                append_file(LOGDIR,LOGFILE,'Deleted '||rows_deleted_total||' rows from 
'||i.table_name || ' at '||sysdate  );

            END LOOP;

            append_file(LOGDIR,LOGFILE,'drop entire tables for scaling tables');

			declare

                CURSOR cursor_frags IS

                    SELECT object_name, to_date ( sysdate, 'mm/dd/yyyy hh24:mi:ss' ) - 
to_date (substr(object_name, length(object_name) - 7, 8), 'yymmddhh24')

       	            FROM user_objects

       		        WHERE object_name like '%_DATA_________' AND object_type = 'TABLE';

            begin

				OPEN cursor_frags;

				LOOP

   					FETCH cursor_frags INTO table_name, days;

   					EXIT WHEN cursor_frags%NOTFOUND;

   					append_file(LOGDIR,LOGFILE,'*** Fetch table_name = '|| table_name || ' 
days = ' || days);

    				IF (days > intervaldays) THEN

            			rows_deleted_total :=0;

            			cursor_handle1 := DBMS_SQL.OPEN_CURSOR;

				execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY 
HH:MI:SS AM'' ';

            			statement_txt := 'DROP TABLE '|| table_name;

            			append_file(LOGDIR,LOGFILE,'*** Executing { '|| statement_txt || ' }');

            			DBMS_SQL.PARSE(cursor_handle1, statement_txt, DBMS_SQL.NATIVE);

                		rows_processed:=DBMS_SQL.EXECUTE(cursor_handle1);

                		commit;

            			DBMS_SQL.CLOSE_CURSOR (cursor_handle1);

            			append_file(LOGDIR,LOGFILE,'Dropped ' || table_name || ' at '||sysdate  );

            			rows_deleted_total :=0;

            			cursor_handle2 := DBMS_SQL.OPEN_CURSOR;

            			statement_txt := 'DELETE FROM scmfragdatatables WHERE data_table_name = '|| 
'''' || table_name || '''';

            			append_file(LOGDIR,LOGFILE,'*** Executing { '|| statement_txt || ' }');

            			DBMS_SQL.PARSE(cursor_handle2, statement_txt, DBMS_SQL.NATIVE);

                		rows_processed:=DBMS_SQL.EXECUTE(cursor_handle2);

                		commit;

            			DBMS_SQL.CLOSE_CURSOR (cursor_handle2);

           			append_file(LOGDIR,LOGFILE,'Deleted from scmfragdatatables - ' || table_name 
|| ' at '||sysdate  );

    				END IF;

				END LOOP;

				append_file(LOGDIR,LOGFILE,'Finished dropping scaling tables');

				CLOSE cursor_frags;

			END;

   END IF;

----- }

            FOR rec IN ( select a.index_name,a.table_name,b.column_name from 
user_constraints a, user_cons_columns b

						 where a.constraint_name=b.constraint_name

     					 and a.table_name like '%PM_TABLE'

						 and a.table_name !='ONS158XX_TIMESTAMP_PM_TABLE'

						 and a.constraint_type='P'

						 and b.column_name like '%TIMESTAMP%')

            LOOP

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

                if (is24h_flag = 2) then

                    statement_txt := 'SELECT /*+ INDEX_FFS('||rec.index_name||') */ rowid 
FROM '||

                                     rec.table_name||' where '||rec.column_name ||'< 
'||''''||prunedate||'''';

                end if;

		if (is24h_flag != 2) then

		   if (substr(rec.table_name, 1, 6) != 'ONS155') then

                        statement_txt := 'SELECT /*+ INDEX_FFS('||rec.index_name||') */ 
rowid FROM '||

                                         rec.table_name||' where '||rec.column_name ||'< 
'||''''||prunedate||''''||

                                         ' and is24h = '||is24h_flag;

                   else

			statement_txt := 'SELECT /*+ INDEX_FFS('||rec.index_name||') */ rowid FROM '||

	          			rec.table_name||' where '||rec.column_name ||'< '||''''||prunedate||''''||

					' and interval= '||is24h_flag;

                   end if;

                end if;

                DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);

		DBMS_SQL.DEFINE_COLUMN_ROWID(cursor_handle,1,deleted_rowid);

                rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                counter := 0;

		deleted_total_rows:=0;

                LOOP

                    IF DBMS_SQL.FETCH_ROWS(cursor_handle) > 0 then

                        DBMS_SQL.COLUMN_VALUE (cursor_handle,1, deleted_rowid);

			insert into rowid_temp

			values(deleted_rowid);

			commit;

                        counter := counter + 1;

                        IF counter = rows_to_be_deleted THEN

                           statement_txt1 := 'declare

		        			   TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

						   rowid_tab_v rowid_tab;

		                              begin

						  select myrowid bulk collect INTO rowid_tab_v from rowid_temp;

             					  FORALL rid IN 1 .. '||counter ||'

         					  DELETE FROM '||rec.table_name||' WHERE rowid =rowid_tab_v(rid);

	                            	      end;';

                       	   execute immediate statement_txt1;

		 	   deleted_total_rows:=deleted_total_rows+counter;

			   counter:=0;

	               	   execute immediate 'truncate table rowid_temp';

                        END IF;

                   ELSE

			exit;

                   END IF;

                END LOOP;

               -- DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                        IF counter < rows_to_be_deleted THEN

                          IF counter > 0 THEN

                              statement_txt2 := 'declare

		                   			TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

							rowid_tab_v rowid_tab;

					         Begin

					        	select myrowid bulk collect INTO rowid_tab_v from rowid_temp;

					         	FORALL rid IN 1 .. '||counter||'

						        DELETE FROM '||rec.table_name||'

						        WHERE rowid = rowid_tab_v(rid);

						        end;';

                              execute immediate statement_txt2;

     			      deleted_total_rows:=deleted_total_rows+counter;

	             	      counter:=0;

			      execute immediate 'truncate table rowid_temp';

                          END IF;

                       END IF;

                 DBMS_SQL.CLOSE_CURSOR (cursor_handle);

		 append_file(LOGDIR,LOGFILE,'Deleted '||deleted_total_rows||' for 
'||rec.table_name);

            END LOOP;

                append_file(LOGDIR,LOGFILE,'prune pm successfully finished at '||sysdate);

      ELSE

           append_file(LOGDIR,LOGFILE,'User choose to keep all the data at '||sysdate);

      END IF;

   EXCEPTION

       WHEN OTHERS THEN

           create_file(LOGDIR, 'prune_pm.err','Error in pruning pm '||SQLERRM||', 
'||SQLCODE);

   END prune_pm;

show error;

3.2.15.2 prune_pm Procedure for Medium, Large, and High-End Configurations

create or replace procedure Prune_pm (

 intervaldays in number,

 is24h_flag in number default(2)

AS

        partition_to_be_pruned    varchar2(9);

        cursor_handle   integer;

        cursor_handle_1 integer;

        statement_txt   varchar2(500);

        statement_txt_1 varchar2(500);

        rows_processed  number := 0;

        rows_processed_1 number :=0;

        rows_deleted_total number := 0;

        rows_to_be_deleted number := 100000;

	only_partition exception;

        PRAGMA EXCEPTION_INIT (only_partition,-14083);

        prunedate         date;

        deleted_rows       number;

        ORACLE_SID       varchar2(20);

	ORACLE_BASE      varchar2(20);

        p_partition_name varchar(30);

        p_15min_subpartition_name varchar(30);

        p_1day_subpartition_name varchar(30);

       LOGDIR   varchar2(80);

       LOGFILE  varchar2(40);

BEGIN

dbms_output.enable;

       SELECT activevalue INTO ORACLE_SID

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='dbname';

       SELECT activevalue INTO ORACLE_BASE

         FROM ctm_config_table

        WHERE sectionname='database' and propertyname='db-oracle-base';

       IF ( intervaldays != -1) THEN

          prunedate := sysdate-intervaldays;

       LOGDIR := ORACLE_BASE|| '/admin/' || ORACLE_SID || '/udump';

       if ( is24h_flag = 0 ) then

          LOGFILE := 'prune_15min_pm.log';

       elsif ( is24h_flag = 1 ) then

          LOGFILE := 'prune_1day_pm.log';

       elsif ( is24h_flag = 2 ) then

          LOGFILE := 'prune_both_pm.log';

       else

          LOGFILE := LOGFILE;

       end if;

dbms_output.put_line('creating file: ' || LOGDIR||'/'||LOGFILE);

          create_file(LOGDIR,LOGFILE,'Starting prune pm at '||sysdate);

          append_file(LOGDIR,LOGFILE,'prune pm data before '||prunedate);

          execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS 
AM'' ';

          --prunedate:= trunc(sysdate-intervaldays);

            if (is24h_flag = 0) then

                append_file(LOGDIR,LOGFILE,'pruning "15min" pm data only at '||sysdate);

            elsif (is24h_flag = 1) then

                append_file(LOGDIR,LOGFILE,'pruning "1day" pm data only at '||sysdate);

            else

                append_file(LOGDIR,LOGFILE,'pruning both "15min" and "1day" pm data at 
'||sysdate);

            end if;

----- MGX8880 PM {

            FOR i IN ( SELECT a.table_name,a.column_name

                        FROM user_tab_columns a,

                             user_objects    b

                       WHERE a.table_name = b.object_name

                         AND b.object_type='TABLE'

                         AND (a.column_name like '%TIMESTAMP%' and a.table_name like 
'%_DATA')

            LOOP

                rows_deleted_total :=0;

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

		execute immediate 'alter session set nls_date_format = ''MM/DD/YYYY HH:MI:SS AM'' 
';

                statement_txt := 'DELETE FROM '||i.table_name ||' WHERE '||i.column_name 
||' < (to_date(' ||

                                    '''' || prunedate || '''' || ', ' || '''' || 
'mm/dd/yyyy hh24:mi:ss' ||

                                    '''' || ') - to_date(' || '''' || '01/01/1970 
00:00:00' ||

                                    '''' || ', ' || '''' || 'mm/dd/yyyy hh24:mi:ss' || 
'''' || '))'

                                    || ' AND rownum <= '||rows_to_be_deleted;

                --append_file(LOGDIR,LOGFILE,'*** Executing { '|| statement_txt || ' }');

                DBMS_SQL.PARSE(cursor_

Table Of Contents

Packages, Procedures, Functions, and Triggers

3.1 CTM Packages

3.1.1 Body Security_Pkg

3.1.2 CTMAlarm

3.1.3 MSMCircuit

3.1.4 Prune_Package

3.1.4.1 Prune_Package for Small Configurations

3.1.4.2 prune_package for Medium, Large, and High-End Configurations

3.1.5 Security_Pkg

3.2 CTM Stored Procedures

3.2.1 Add_Circuit_Ctps_To_Met

3.2.2 Add_Log_Partition

3.2.3 Add_PM_Partition

3.2.4 Alter_User_Permission

3.2.5 Append_File

3.2.6 BulkNEAddition

3.2.7 Clear_AllUnack_Alarms

3.2.8 Create_File

3.2.9 Execute_Dml_Statement

3.2.10 Execute_Statement

3.2.11 Login_Disable

3.2.12 PortName

3.2.13 Prune_Audittrail

3.2.13.1 Prune_Audittrail Procedure for Small Configurations

3.2.13.2 prune_audittrail Procedure for Medium, Large, and High-End Configurations

3.2.14 Prune_NE

3.2.15 Prune_PM

3.2.15.1 Prune_PM Procedure for Small Configurations

3.2.15.2 prune_pm Procedure for Medium, Large, and High-End Configurations