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

Packages, Procedures, Functions, and Triggers

3.1 CTM Stored Procedures

3.1.1 Add_Circuit_CTPS_TO_MET

3.1.2 Add_Log_Partition

3.1.3 Add_PM_Partition

3.1.4 Alarm-handling

3.1.5 Alter_User_Permission

3.1.6 Append_File

3.1.7 BulkNEAddition

3.1.8 Clear_AllUnack_Alarms

3.1.9 Create_File

3.1.10 Drop and Recreate Sequence Numbers During Migration (Reset_Seq)

3.1.11 Execute_DML_Statement

3.1.12 Execute_Statement

3.1.17 Prune the Database

3.1.18 Update_Portnames

3.2 CTM Functions

3.2.1 CollapsedDestNodeName

3.2.2 CollapsedEqptPortName

3.2.3 CollapsedIfIndex

3.2.4 CollapsedModelType

3.2.5 CollapsedModuleName

3.2.6 CollapsedObjectIndex

3.2.7 CollapsedPhy

3.2.8 CollapsedPort

3.2.9 CollapsedPortModuleName

3.2.10 ConvertAXXIfIndex

3.2.11 ConvertLinkModuleType

3.2.17 Get_ENET_Value

3.2.18 Get_ENET_Value2

3.2.19 GetBGFromBGObj

3.2.20 GetBGFromQoSObj

3.2.21 GetEqptInfoPhysicalLoc

3.2.22 GetInterfaceID

3.2.23 GetLowBits

3.2.24 GetModelTypeFromPhysicalLoc

3.2.25 GetModuleName

3.2.26 GetModuleName1

3.2.27 GetNEDBACCESSID

3.2.28 GetObjectTypeFromIfIndex

3.2.29 GetObjectTypeFromIfIndex2

3.2.30 GetObjTypeFromIfIndex

3.2.31 GetPhysicalLoc

3.2.32 GetPIM

3.2.33 GetPMParameterValue

3.2.34 GetPolicyMapType

3.2.35 GetPort_XTC

3.2.36 GetPort

3.2.37 GetPortModuleName

3.2.38 GetPortName

3.2.39 GetPortWithPIMPPM

3.2.40 GetPPM

3.2.41 GetSlot

3.2.42 GetSlotModuleName

3.2.43 GetSlotModuleType

3.2.44 GetSubIf

3.2.45 MapObjectIndex

3.2.46 MergedInvalidityList

3.2.47 RemoveFirstByteIF

3.2.48 RemoveLowBits

3.3 CTM Triggers

3.3.1 AAT_Before_Trigger

3.3.2 AAT_Trigger

3.3.3 Act_Trigger

3.3.4 Aft_Del_L2CktTable_Trigger

3.3.5 Aft_Del_L2Interface_Trigger

3.3.6 Aft_Ins_L2Interface_Trigger

3.3.7 Aft_InsDel_BridgeGroup_Trigger

3.3.8 CktDest_Trigger

3.3.9 Deleted_User_Trigger

3.3.10 NE_Model_State_Ins_Trig

3.3.11 NE_Model_Type_Del_Trig

3.3.12 NIT_Trigger

3.3.13 UT_Trigger

Packages, Procedures, Functions, and Triggers

This chapter describes the stored packages, procedures, functions, and triggers defined in CTM tables.

3.1 CTM Stored Procedures

3.1.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.1.2 Add_Log_Partition

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);

BEGIN

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

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

          
create_file('/oraclesw/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',

                          'NE_AUDIT_TRAIL_TABLE', 'ONS158XX_LAC_LOG_TABLE'

))

          LOOP

                /* 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;

                new_partition_date:= new_partition_date+1;

                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('/oraclesw/admin/'||ORACLE_SID||'/udump','add_log_partition.log','added 
partition '||partition_to_be_added||' to '||rec.table_name);

          END LOOP;

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

EXCEPTION

         WHEN OTHERS THEN

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

END;

show error

var jobno number;

exec DBMS_JOB.SUBMIT(:jobno,'add_log_partition;',TRUNC(SYSDATE)+12/24, 
'TRUNC(SYSDATE+1)+12/24');

3.1.3 Add_PM_Partition

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);

BEGIN

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

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

          
create_file('/oraclesw9i/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')

          LOOP

                /* 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_tab_partitions

                Where table_name =rec.table_name

                group by tablespace_name;

                new_partition_date:= new_partition_date+1;

                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);

                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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_pm_partition.log','added 
partition '||partition_to_be_added||' to '||rec.table_name);

          END LOOP;

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

EXCEPTION

         WHEN OTHERS THEN

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

END;

3.1.4 Alarm-handling

create or replace TYPE link_id_tabtype is Table of number;

create TYPE AlarmInfoType as OBJECT (

        nedbaccessid                number,

        Alarmseqnum                 number,

        ModuleOrIfIndex             number,

        ActiveAlarmTimeStamp        date,

        ActiveAlarmType             number,

        ActiveAlarmSeverity         number,

        ActiveAlarmServEff          number,

        ActiveAlarmAdditionalInfo   varchar2(512),

        AckIndex                    number,

        ActiveAlarmFlag             number,

        ActiveAlarmComment          varchar2(2014),

        ClearAlarmTimeStamp         date,

        NEAlarmTimeStamp            date,

        NEAlarmClearTimeStamp       date,

        ModuleType                  number,

        Physicalloc                 number,

        alarmstatus                 number,

        linkid                      link_id_tabtype,

        actiontype                  number,

        externalcondition           varchar2(1024),

        ModelType                   number,

        ObjectType                  number,

        StrObjInstance              VARCHAR2(256)

);

create TYPE AlarmInfoListType as table of AlarmInfoType;

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)

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)

           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);

           exception

              when DUP_VAL_ON_INDEX then

                   dbms_output.put_line('Duplicate alarm');

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

                      select 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 alarmstatus=0

                         AND ModelType = p_ModelType

                         AND ObjectType = p_ObjectType

                         AND StrObjInstance = p_StrObjInstance;

                      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

                       WHERE nedbaccessid=p_nedbaccessid

                         AND moduleorifindex=p_ModuleOrIfIndex

                         AND ActiveAlarmType=p_ActiveAlarmType

                         AND NEAlarmTimeStamp=p_NEAlarmTimeStamp

                         AND Physicalloc=p_Physicalloc

                         AND ModelType = p_ModelType

                         AND ObjectType = p_ObjectType

                         AND StrObjInstance = p_StrObjInstance;

                      --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);

                 statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
activealarmflag=0, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';

                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;

                 statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
activealarmflag =2, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';

                 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;

                statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')' ;

                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-1

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-1

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-1

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-1

                         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);

                statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
activealarmflag=0, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';

                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;

                 statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
activealarmflag =2, 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';

                 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;

                statement_txt:='Update active_alarm_table set 
alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), 
NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')' ;

                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-1

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-1

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-1

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-1

                         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

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

);

      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

);

    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.5 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.1.6 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.1.7 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 varchar)

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;

           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;

           IF (SubNWToBeInserted = 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,

);

			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.1.8 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.1.9 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.1.10 Drop and Recreate Sequence Numbers During Migration (Reset_Seq)

CTM uses the reset_seq stored procedure to drop and recreate sequence numbers during migration.

CREATE or REPLACE PROCEDURE reset_seq

(p_statement_txt1  varchar2,

 seq_name          varchar2,

 p_statement_txt2  varchar2,

 seq_number        number)

IS

 cursor_handle  number:=DBMS_SQL.OPEN_CURSOR;

 rows_processed number:=0;

 statement_txt  varchar2(500);

BEGIN

 statement_txt := p_statement_txt1 ||' '||seq_name||P_statement_txt2||' '||seq_number;

 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;

 RAISE;

END reset_seq;

3.1.11 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.1.12 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.1.13 OT_MEMFP

CREATE OR REPLACE PACKAGE ot_memfp IS

    c_part CONSTANT INTEGER := 16;

    uninit_seq_num CONSTANT INTEGER := -1;

FUNCTION sequence_num_part(model_type IN INTEGER) RETURN INTEGER;

FUNCTION low_part(model_type IN INTEGER) RETURN INTEGER;

FUNCTION high_part(model_type IN INTEGER) RETURN INTEGER;

FUNCTION move_part(new_seq_num IN INTEGER, old_model_type IN INTEGER) RETURN INTEGER;

PROCEDURE change_node_id(old_node_id IN INTEGER, new_node_id IN INTEGER, status OUT 
INTEGER);

PROCEDURE update_seq_num(nid IN INTEGER, mtype IN INTEGER, seq_num INTEGER);

PROCEDURE get_seq_num(nid IN INTEGER, mtype IN INTEGER, result IN OUT INTEGER);

PROCEDURE del_partition(nid IN INTEGER, mtype IN INTEGER, rowcount OUT INTEGER);

PROCEDURE insert_empty_blob(nid IN INTEGER, mtype IN INTEGER, ndx IN INTEGER);

END;

/* -----------------------------------------------------------------------*/

DECLARE

    table_name VARCHAR(32);

    pk_name VARCHAR(32);

    details VARCHAR(512);

    phy_crit VARCHAR(512);

/**

     * Centralized logging

     * @param str - user string to be logged

*/

    PROCEDURE log(str IN VARCHAR2) IS

    BEGIN

    	DBMS_OUTPUT.PUT_LINE(str);

    END log;

/**

     * Convert PSQL BOOLEAN type to a SQL parameter string

*/

    FUNCTION boolean_to_varchar2(flag IN BOOLEAN) RETURN VARCHAR2 IS

    BEGIN

        IF flag = TRUE THEN

           RETURN 'true';

        ELSE

           RETURN 'false';

        END IF;

    END boolean_to_varchar2;

/**

     * Utility method to help check for the existence of user tables,

     * constraints, etc.

     * @return true iff there is a row in system table: <tableName> with

     * scolumn <colName> with value <criteria>

*/

    FUNCTION doesExist(criteria IN VARCHAR2, tableName IN VARCHAR2, colName IN VARCHAR2) 
RETURN BOOLEAN IS

        cnt INTEGER;

        exist BOOLEAN;

        str VARCHAR(128);

        crit VARCHAR(32);

    BEGIN

        crit := UPPER(criteria); -- all schema names are presumed stored in upper-case

        str := 'SELECT COUNT(*) FROM ' || tableName || ' WHERE ' || colName || ' = :crit';

        EXECUTE IMMEDIATE str INTO cnt USING IN crit;

        exist := cnt > 0;

        RETURN exist;

    END doesExist;

/**

     * Utility method to re-create a table.

     * If the table already existed it's dropped AND any associated integrity constraints 
are dropped as well

*/

    PROCEDURE recreateTable(table_name IN VARCHAR2, record_typedef IN VARCHAR2, 
opt_phy_crit IN VARCHAR2) IS

        systable CONSTANT VARCHAR(32) := 'user_tables';

        syscol CONSTANT VARCHAR(32) := 'TABLE_NAME';

        banner CONSTANT VARCHAR(32) := '*** table ';

        exist BOOLEAN;

    BEGIN

        exist := doesExist(table_name, systable, syscol);

        log(banner || table_name || ' already exists? ' || boolean_to_varchar2(exist));

        IF exist THEN

            BEGIN

                -- drop the table since it exists. Cascade the drop to any integrity 
constraints...

                EXECUTE IMMEDIATE 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS';

            END;

        END IF;

        EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name || ' (' || details || ' )' || ' ' 
|| opt_phy_crit;

        IF (exist) THEN

            log(banner || table_name || ' re-created...');

        ELSE

            log(banner || table_name || ' created...');

        END IF;

        log('');

    END recreateTable;

/**

     * Utility method to help re-create a LOB column's criteria

     * This allows things like the (BLOB) column chunk size to be set

*/

    FUNCTION recreateLobCriteria(table_name IN VARCHAR2, col_name IN VARCHAR2, details IN 
VARCHAR2) RETURN VARCHAR IS

    BEGIN

        RETURN 'LOB (' || col_name || ') STORE AS ( ' || details || ' )';

    END recreateLobCriteria;

/**

     * Utility method to re-create a primary key.

     * If the key already existed it's dropped

*/

    PROCEDURE recreatePKConstraint(pk_name IN VARCHAR2, table_name IN VARCHAR2, keys IN 
VARCHAR2) IS

        systable CONSTANT VARCHAR(32) := 'ALL_CONSTRAINTS';

        syscol CONSTANT VARCHAR(32) := 'CONSTRAINT_NAME';

        banner CONSTANT VARCHAR(32) := '*** constraint ';

        exist BOOLEAN;

    BEGIN

        exist := doesExist(pk_name, systable, syscol);

        log(banner || pk_name || ' already exists? ' || boolean_to_varchar2(exist));

        IF exist THEN

            BEGIN

                -- drop the existing constraint

                 EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name || ' DROP CONSTRAINT ' || 
pk_name;

            END;

        END IF;

        -- add the new constraint

        EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name || ' ADD CONSTRAINT ' || pk_name || 
' PRIMARY KEY (' || keys || ' )';

        IF (exist) THEN

            log(banner || pk_name || ' re-created...');

        ELSE

            log(banner || pk_name || ' created...');

        END IF;

        log('');

    END recreatePKConstraint;

BEGIN

    -- 454 product-line model instance state table

    -- Attribute data is represented in the data stream

    -- Works across 454 product type AND versions that support

    -- model state persistence

--

    table_name := 'ne_model_state';

    details :=

        ' node_id      INT       NOT NULL,' || -- node id

        ' model_type   INT       NOT NULL,' || -- model classname mapped to a unique model 
class type + added user data - if needed (e.g. slot id)

        ' model_index  INT       NOT NULL,' || -- model tindex

        ' data         BLOB      DEFAULT empty_blob()' -- all attribute state

    -- keep the blob chunk size down as small as possible

    phy_crit :=

        'CHUNK 512'

    recreateTable(table_name, details, recreateLobCriteria(table_name, 'data', phy_crit));

    -- primary key for this table consists of the following...

    pk_name := table_name || '_pk';    -- convention is table_name + "_pk"

    details := 'node_id, model_type, model_index';

    recreatePKConstraint(pk_name, table_name, details);

    --  454 product-line model 'type' state

    --  Primary attribute support is a model-type wide sequence number

--

    table_name := 'ne_model_type';

    details :=

        'node_id        INT        NOT NULL,' || -- node id

        'model_type     INT        NOT NULL,' || -- model classname mapped to a unique 
model class type + opt user data (e.g. slot id)

        'signature      BLOB,'                || -- can be used to store class-portion 
(i.e. ObjectStreamClass part) of the object stream

        'sequence_num   INT'                     -- if you want to maintain a single one 
for the whole object

    recreateTable(table_name, details, '');

    -- primary key for this table consists of the following...

    pk_name := table_name || '_pk';    -- convention is table_name + "_pk"

    details := 'node_id, model_type';

    recreatePKConstraint(pk_name, table_name, details);

    EXCEPTION

        -- global exception handler

        WHEN OTHERS THEN

           DECLARE

               error_code NUMBER := SQLCODE;

               error_msg VARCHAR(512) := SQLERRM;

           BEGIN

               -- log errors to DBMS output

               DBMS_OUTPUT.PUT_LINE('err: ' || TO_CHAR(error_code) || ' : ' || error_msg);

           END;

END;

CREATE OR REPLACE PACKAGE BODY ot_memfp IS

/**

     * @return sequence number partition representative

*/

    FUNCTION sequence_num_part(model_type IN INTEGER) RETURN INTEGER IS

        result INTEGER;

    BEGIN

        result := c_part * FLOOR(model_type / c_part);

        RETURN result;

    END sequence_num_part;

/**

     * @return lowest # item in partition

*/

    FUNCTION low_part(model_type IN INTEGER) RETURN INTEGER IS

        result INTEGER;

    BEGIN

        result := sequence_num_part(model_type);

        RETURN result;

    END low_part;

/**

     * @return highest # item in partition

*/

    FUNCTION high_part(model_type IN INTEGER) RETURN INTEGER IS

        result INTEGER;

    BEGIN

        result := low_part(model_type) + c_part - 1;

        RETURN result;

    END high_part;

/**

     * @return move a model_type into a new partition as identified by the new_seq_num

     * model type

*/

    FUNCTION move_part(new_seq_num IN INTEGER, old_model_type IN INTEGER) RETURN INTEGER 
IS

        result INTEGER;

    BEGIN

        result := c_part * ROUND(new_seq_num / c_part, 0) + (old_model_type MOD c_part);

        RETURN result;

    END move_part;

/**

     * stored procedure to change node id. If new_node_id already exists,

     * the procedure exits

     * @return -1 if new_node_id already exists / 0 if runs 100% to completion

*/

   PROCEDURE change_node_id(old_node_id IN INTEGER, new_node_id IN INTEGER, status OUT 
INTEGER) IS

        exist BOOLEAN;

        tmp INTEGER;

        ok CONSTANT INTEGER := 0;

        no_change CONSTANT INTEGER := 1;

        new_id_already_exists CONSTANT INTEGER := -1;

   BEGIN

        status := no_change;

        IF (old_node_id = new_node_id) THEN

            RETURN;

        END IF;

        tmp := NULL;

        -- new node id should not already exist

        -- ...test for this

        BEGIN

            SELECT 1 INTO tmp FROM ne_model_type

                WHERE EXISTS (SELECT 1 FROM ne_model_type WHERE node_id = new_node_id);

        EXCEPTION

            WHEN NO_DATA_FOUND THEN NULL; -- ignore 'SELECT INTO' except. when no rows 
selected

            WHEN TOO_MANY_ROWS THEN tmp := 1; -- set tmp to 1 'SELECT INTO' except. when 
>1 rows selected

        END;

        exist := NOT tmp IS NULL;

        status := new_id_already_exists;

        IF (NOT exist) THEN

            UPDATE ne_model_type

                SET node_id = new_node_id

                WHERE node_id = old_node_id;

            tmp := SQL%ROWCOUNT;

            IF (tmp = 0) THEN

                status := no_change;

            ELSE

                status := ok;

            END IF;

        END IF;

    END;

/**

     * procedure to update the sequence number. The procedure takes

     * care of the conversion of model type to sequence # model type

     * @return corr. update count i.e. # of rows effected

*/

    PROCEDURE update_seq_num(nid IN INTEGER, mtype IN INTEGER, seq_num INTEGER) IS

        seq_num_model_type INTEGER;

        tmp INTEGER;

    BEGIN

        seq_num_model_type := sequence_num_part(mtype);

        BEGIN

            UPDATE ne_model_type SET sequence_num = seq_num

                WHERE node_id = nid AND model_type = seq_num_model_type;

            tmp := SQL%ROWCOUNT;

            IF tmp = 0 THEN

                INSERT INTO ne_model_type (node_id, model_type, sequence_num)

                    VALUES (nid, seq_num_model_type, seq_num); -- try inserting instead

            END IF;

        END;

    END;

/**

     * stored procedure to update the sequence number. The procedure takes

     * care of the conversion of model type to sequence # model type

*/

    PROCEDURE get_seq_num(nid IN INTEGER, mtype IN INTEGER, result IN OUT INTEGER) IS

    	seq_num_model_type INTEGER;

    BEGIN

    	seq_num_model_type := sequence_num_part(mtype);

        BEGIN

            SELECT sequence_num INTO result FROM ne_model_type

               WHERE node_id = nid AND model_type = seq_num_model_type;

        EXCEPTION

    	    WHEN NO_DATA_FOUND THEN NULL; -- ignore 'SELECT INTO' except. when no rows 
selected

        END;

    END;

/**

     * stored procedure to delete the entire partition identified by mtype

     # last-changed seq # is reset for the assoc. slot

*/

    PROCEDURE del_partition(nid IN INTEGER, mtype IN INTEGER, rowcount OUT INTEGER) IS

        low INTEGER;

        high INTEGER;

    BEGIN

        low  := low_part(mtype);

        high := high_part(mtype);

        UPDATE ne_model_type

            SET sequence_num = uninit_seq_num

            WHERE node_id = nid AND low <= model_type AND model_type <= high;

        DELETE FROM ne_model_state

            WHERE node_id = nid AND low <= model_type AND model_type <= high;

        rowcount := SQL%ROWCOUNT;

    END;

/**

     * creates an empty blob (if one does not already exist)

*/

    PROCEDURE insert_empty_blob(nid IN INTEGER, mtype IN INTEGER, ndx IN INTEGER) IS

    BEGIN

        BEGIN

            INSERT into ne_model_state (node_id, model_type, model_index, data)

                VALUES (nid, mtype, ndx, empty_blob());

        EXCEPTION

            -- global exception handler

            WHEN DUP_VAL_ON_INDEX THEN NULL; -- ignore err if a corr. row already exists

        END;

    END insert_empty_blob;

--BEGIN -- pkg body

END;

3.1.14 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.1.15 Prune_NE

create or replace procedure prune_ne

     (p_nesysid    IN varchar2)

IS

      p_nedbaccessid        number;

      p_nenodeid            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);

      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';

      /* 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Can 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Process prune '||p_nesysid||' at '||prune_date);

          /* get the nedbaccessid from nesysid  */

           SELECT nedbaccessid, nenodeid INTO p_nedbaccessid, p_nenodeid

             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='CKTNODEID'    OR

                              a.column_name='CHILDID'      OR

                              a.column_name='GROUPORNEID'))

           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;

                        -- 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;

                        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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted 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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Deleted rows committed '||counter||' from ne_info_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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
prune '||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('/oraclesw9i/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('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_ne'||p_nesysid||'.log','
Error in pruning NE!'||SQLERRM||','||SQLCODE);

          raise PRUNE_FAILED;

END;

3.1.16 Prune_PM

3.1.16.1 Prune_PM Procedure for Small Configurations

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;

       statement_txt   varchar2(500);

       prunedate       date;

       TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

       rowid_tab_v rowid_tab;

       rows_to_be_deleted number := 100000;

       ORACLE_SID       varchar2(20);

       LOGDIR   varchar2(80);

       LOGFILE  varchar2(40);

   BEGIN

       SELECT activevalue INTO ORACLE_SID

         FROM ctm_config_table

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

       LOGDIR := '/oraclesw9i/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;

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

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

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

            execute_statement('set transaction use rollback segment  
','RB_huge','','','');

            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;

          LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454DS3_TIMESTAMP_I) */ rowid from 
ons15454_ds3_pm_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ons15454_ds3_pm_table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454DS3_TIMESTAMP_I) */ rowid from 
ons15454_ds3_pm_table

                       where timestamp < prunedate and rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                     ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ons15454_ds3_pm_table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ons15454_ds3_pm_table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ons15454_DS3_PM_table at '||sysdate  );

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

          LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454PATH_TIMESTAMP_INDEX) */ rowid from 
ons15454_sonet_path_pm_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ons15454_sonet_path_pm_table

                                 WHERE rowid = rowid_tab_v(rid);

                          COMMIT;

                          rows_processed:=rows_processed+counter;

                     END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454PATH_TIMESTAMP_INDEX) */ rowid from 
ons15454_sonet_path_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                 ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ons15454_sonet_path_pm_table

                                 WHERE rowid = rowid_tab_v(rid);

                          COMMIT;

                          rows_processed:=rows_processed+counter;

                     END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                  execute_statement('set transaction use rollback segment  ','RB_BIG', 
'','','');

                  FORALL rid IN 1 .. counter

                      DELETE FROM ons15454_sonet_path_pm_table

                      WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_SONET_PATH_PM_TABLE at '||sysdate);

            counter :=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

            rows_processed:=0;

         LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454LINE_TIMESTAMP_I) */ rowid from 
ons15454_sonet_line_pm_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

                         execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_sonet_line_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454LINE_TIMESTAMP_I) */ rowid from 
ons15454_sonet_line_pm_table

                       where timestamp < prunedate and rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      IF counter = rows_to_be_deleted THEN

                         execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_sonet_line_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                      DELETE FROM ons15454_sonet_line_pm_table

                      WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_SONET_LINE_PM_TABLE at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SEC_TIMESTAMP_I) */ rowid from 
ons15454_sonet_sec_pm_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

                         execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_sonet_sec_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SEC_TIMESTAMP_I) */ rowid from 
ons15454_sonet_sec_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      IF counter = rows_to_be_deleted THEN

                         execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_sonet_sec_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 commit;

                 execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                 FORALL rid IN 1 .. counter

                      DELETE FROM ons15454_sonet_sec_pm_table

                      WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_SONET_SEC_PM_TABLE at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

          LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454ENET_TIMESTAMP_I) */ rowid from 
ons15454_enet_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_enet_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454ENET_TIMESTAMP_I) */ rowid from 
ons15454_enet_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ons15454_enet_pm_table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ons15454_enet_pm_table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_ENET_PM_TABLE at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454DS1_TIMESTAMP_I) */ rowid from 
ons15454_ds1_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454_DS1_PM_TABLE

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454DS1_TIMESTAMP_I) */ rowid from 
ons15454_ds1_pm_table

                       where timestamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454_DS1_PM_TABLE

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454_DS1_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_DS1_PM_TABLE at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454VT_TIMESTAMP_INDEX) */ rowid from 
ONS15454_Sonet_VT_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454_Sonet_VT_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454VT_TIMESTAMP_INDEX) */ rowid from 
ONS15454_Sonet_VT_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454_Sonet_VT_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454_Sonet_VT_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454_Sonet_VT_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS1454SDHRS_TIMESTAMP_I) */ rowid from 
ONS15454SDH_RS_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_RS_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS1454SDHRS_TIMESTAMP_I) */ rowid from 
ONS15454SDH_RS_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_RS_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_RS_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_RS_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHMS_TIMESTAMP_I) */ rowid from 
ONS15454SDH_MS_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_MS_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHMS_TIMESTAMP_I) */ rowid from 
ONS15454SDH_MS_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_MS_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_MS_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_MS_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHHO_TIMESTAMP_I) */ rowid from 
ONS15454SDH_HO_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_HO_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHHO_TIMESTAMP_I) */ rowid from 
ONS15454SDH_HO_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_HO_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_HO_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_HO_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHLO_TIMESTAMP_I) */ rowid from 
ONS15454SDH_LO_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_LO_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHLO_TIMESTAMP_I) */ rowid from 
ONS15454SDH_LO_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_LO_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_LO_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_LO_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE1_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E1_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E1_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE1_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E1_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E1_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_E1_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_E1_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE3_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E3_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E3_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE3_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E3_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E3_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_E3_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_E3_PM_Table at '||sysdate);

            counter :=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE4_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E4_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E4_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15454SDHE4_TIMESTAMP_I) */ rowid from 
ONS15454SDH_E4_PM_Table

                       where TimeStamp < prunedate AND rownum <= rows_to_be_deleted

                       and is24h = is24h_flag

                ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      execute_statement('set transaction use rollback segment 
','RB_BIG','','','');

                      IF counter = rows_to_be_deleted THEN

                         FORALL rid IN 1 .. counter

                                DELETE FROM ONS15454SDH_E4_PM_Table

                                WHERE rowid = rowid_tab_v(rid);

                         COMMIT;

                         rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            commit;

            execute_statement('set transaction use rollback segment  ','RB_BIG','','','');

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                 FORALL rid IN 1 .. counter

                               DELETE FROM ONS15454SDH_E4_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                 COMMIT;

                 rows_processed:=rows_processed+counter;

                 counter := 0;

               END IF;

               EXIT;

            END IF;

         END LOOP;

         append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15454SDH_E4_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_SEC_PM_TS_INDEX) */ rowid from 
ONS155XX_SONET_SEC_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155XX_SONET_SEC_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_SEC_PM_TS_INDEX) */ rowid from 
ONS155XX_SONET_SEC_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155XX_SONET_SEC_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS155XX_SONET_SEC_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155XX_SONET_SEC_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

       LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_PHY_PM_TS_INDEX) */ rowid from 
ONS155xx_Phy_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Phy_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_PHY_PM_TS_INDEX) */ rowid from 
ONS155xx_Phy_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Phy_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS155xx_Phy_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               exit;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155xx_Phy_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_OPT_POWER_PM_TS_INDEX) */ rowid 
from ONS155xx_Opt_Power_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Opt_Power_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                null;

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_OPT_POWER_PM_TS_INDEX) */ rowid 
from ONS155xx_Opt_Power_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Opt_Power_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS155xx_Opt_Power_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155xx_Opt_Power_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15501_OPT_POWER_PM_TS_INDEX) */ rowid 
from ONS15501_Opt_Power_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15501_Opt_Power_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15501_OPT_POWER_PM_TS_INDEX) */ rowid 
from ONS15501_Opt_Power_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15501_Opt_Power_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS15501_Opt_Power_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15501_Opt_Power_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_CDL_PM_TS_INDEX) */ rowid from 
ONS155xx_CDL_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_CDL_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS155xx_CDL_PM_TS_INDEX) */ rowid from 
ONS155xx_CDL_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_CDL_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS155xx_CDL_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155xx_CDL_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_ETHER_HIST_TS_INDEX) */ rowid from 
ONS155xx_Ether_Hist_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Ether_Hist_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_ETHER_HIST_TS_INDEX) */ rowid from 
ONS155xx_Ether_Hist_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_Ether_Hist_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS155xx_Ether_Hist_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155xx_Ether_Hist_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

         LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_FCM_PE_TS_INDEX) */ rowid from 
ONS155xx_FCM_PE_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_FCM_PE_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS155XX_FCM_PE_TS_INDEX) */ rowid from 
ONS155xx_FCM_PE_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and interval = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS155xx_FCM_PE_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1. .. counter

                               DELETE FROM ONS155xx_FCM_PE_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS155xx_FCM_PE_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter := 0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_IOC_W_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_IOC_W_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_IOC_W_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_IOC_W_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_IOC_W_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                     ) LOOP

                      counter := counter + 1;

                      rowid_tab_v(counter) := i.rowid;

                      IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_IOC_W_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                      END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                  commit;

                  execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_IOC_W_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               exit;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_IOC_W_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

       LOOP

            counter :=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_TRANSPON_PM_TBL_TSTAMP_IDX_I) */ rowid 
from ONS158XX_TRANSPONDER_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_TRANSPONDER_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_TRANSPON_PM_TBL_TSTAMP_IDX_I) */ rowid 
from ONS158XX_TRANSPONDER_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_TRANSPONDER_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF  counter > 0 THEN

                   execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                   FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_TRANSPONDER_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                   COMMIT;

                   rows_processed:=rows_processed+counter;

                   counter := 0;

               END IF;

               exit;

            END IF;

       END LOOP;

       append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_TRANSPONDER_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

       LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_LSM_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_LSM_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_LSM_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_LSM_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_LSM_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_LSM_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                  commit;

                  execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_LSM_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

       END LOOP;

       append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_LSM_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

       LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_SCF_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_SCF_PM_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;

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_SCF_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_SCF_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_SCF_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          commit;

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_SCF_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_SCF_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

       END LOOP;

       append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_SCF_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

       LOOP

           counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_AMP_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_AMPLIFIER_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_AMPLIFIER_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_AMP_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_AMPLIFIER_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_AMPLIFIER_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_AMPLIFIER_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_AMPLIFIER_PM_TABLE  at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_WD_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_WD_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_WD_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_WD_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_WD_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_WD_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_WD_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_WD_PM_TABLE at '|| sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_OADM_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OADM_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OADM_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_OADM_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OADM_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OADM_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_OADM_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               exit;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_OADM_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_WD_PM_RX_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_WD_RX_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_WD_RX_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_WD_PM_RX_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_WD_RX_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_WD_RX_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_WD_RX_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_WD_RX_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_OSU_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OSU_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OSU_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_OSU_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OSU_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OSU_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_OSU_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               exit;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_OSU_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_BCS_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_BCS_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_BCS_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_BCS_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_BCS_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_BCS_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted THEN

               IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_BCS_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

        append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_BCS_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_OAD_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OAD_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OAD_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_OAD_PM_TBL_TSTAMP_IDX_I) */ rowid from 
ONS158XX_OAD_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_OAD_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                   FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_OAD_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                   COMMIT;

                   rows_processed:=rows_processed+counter;

                   counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

            append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_OAD_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(P_CMP_PM_Tbl_TStamp_idx_i) */ rowid from 
ONS158XX_CMP_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_CMP_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(P_CMP_PM_Tbl_TStamp_idx_i) */ rowid from 
ONS158XX_CMP_PM_TABLE

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS158XX_CMP_PM_TABLE

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                   FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_CMP_PM_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                   COMMIT;

                   rows_processed:=rows_processed+counter;

                   counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

            append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS158XX_CMP_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15600SEC_TIMESTAMP_I) */ rowid from 
ONS15600_Sonet_Sec_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15600_Sonet_Sec_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15600SEC_TIMESTAMP_I) */ rowid from 
ONS15600_Sonet_Sec_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15600_Sonet_Sec_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                   FORALL rid IN 1 .. counter

                               DELETE FROM ONS15600_Sonet_Sec_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                   COMMIT;

                   rows_processed:=rows_processed+counter;

                   counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

            append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15600_Sonet_Sec_PM_TABLE at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15600LINE_TIMESTAMP_I) */ rowid from 
ONS15600_Sonet_Line_PM_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

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15600_Sonet_Line_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            else

                FOR i IN (select /*+ INDEX_FFS(ONS15600LINE_TIMESTAMP_I) */ rowid from 
ONS15600_Sonet_Line_PM_Table

                      where  TimeStamp < prunedate and rownum <= rows_to_be_deleted

                      and is24h = is24h_flag

                ) LOOP

                       counter := counter + 1;

                       rowid_tab_v(counter) := i.rowid;

                       IF counter = rows_to_be_deleted THEN

                          execute_statement('set transaction use rollback segment  
','RB_BIG','','','');

                          FORALL rid IN 1 .. counter

                                 DELETE FROM ONS15600_Sonet_Line_PM_Table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                       END IF;

                END LOOP;

            end if;

            IF counter < rows_to_be_deleted  THEN

               IF counter > 0 THEN

                   FORALL rid IN 1 .. counter

                               DELETE FROM ONS15600_Sonet_Line_PM_Table

                               WHERE rowid = rowid_tab_v(rid);

                   COMMIT;

                   rows_processed:=rows_processed+counter;

                   counter := 0;

               END IF;

               EXIT;

            END IF;

        END LOOP;

            append_file(LOGDIR, LOGFILE,'Deleted '||rows_processed||' rows from 
ONS15600_Sonet_Line_PM_Table at '||sysdate);

            counter:=0;

            rows_processed:=0;

            execute_statement('set transaction use rollback segment  
','RB_HUGE','','','');

        LOOP

            counter:=0;

            if (is24h_flag = 2) then

                FOR i IN (select /*+ INDEX_FFS(ONS15600Path_TIMESTAMP_INDEX) */ rowid from 
ONS15600_Sonet_Path_PM_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

                          execute_st

Table Of Contents

Packages, Procedures, Functions, and Triggers

3.1 CTM Stored Procedures

3.1.1 Add_Circuit_CTPS_TO_MET

3.1.2 Add_Log_Partition

3.1.3 Add_PM_Partition

3.1.4 Alarm-handling

3.1.5 Alter_User_Permission

3.1.6 Append_File

3.1.7 BulkNEAddition

3.1.8 Clear_AllUnack_Alarms

3.1.9 Create_File

3.1.10 Drop and Recreate Sequence Numbers During Migration (Reset_Seq)

3.1.11 Execute_DML_Statement

3.1.12 Execute_Statement

3.1.13 OT_MEMFP

3.1.14 PortName

3.1.15 Prune_NE

3.1.16 Prune_PM

3.1.16.1 Prune_PM Procedure for Small Configurations