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

Packages, Procedures, Functions, and Triggers

3.1 CTM Packages

3.1.1 Body DB_Info_Utility

3.1.2 Body Security_Pkg

3.1.3 Body Conversion_Utility

3.1.4 Conversion_Utility

3.1.5 CTMAlarm

3.1.6 DB_Info_Utility

3.1.7 getIPSLA_IPADDR

3.1.8 getIPSLA_SUBNETMASK

3.1.9 MSMCircuit

3.1.10 Prune_Package

3.1.11 Security_Pkg

3.1.12 Service_Availability

3.2 CTM Stored Procedures

3.2.1 Add_Circuit_Ctps_To_Met

3.2.2 Add_Log_Partition

3.2.3 Add_PM_Partition

3.2.4 Alter_User_Permission

3.2.5 Append_File

3.2.6 BulkNEAddition

3.2.7 Clear_AllUnack_Alarms

3.2.8 Create_File

3.2.9 Execute_Dml_Statement

3.2.10 Execute_Statement

3.2.11 GET_NESYSID

3.2.12 Login_Disable

3.2.13 PortName

3.2.14 Prune_Audittrail

3.2.19 Update_Portnames

3.3 CTM Functions

3.3.1 CollapsedDestNodeName

3.3.2 CollapsedDestNodeName

3.3.3 CollapsedEqptPortName

3.3.4 CollapsedIfIndex

3.3.5 CollapsedIfIndex

3.3.6 CollapsedModelType

3.3.7 CollapsedModuleName

3.3.8 CollapsedObjectIndex

3.3.9 CollapsedPhy

3.3.10 CollapsedPort

3.3.11 CollapsedPortModuleName

3.3.12 ConvertAxxIfIndex

3.3.13 ConvertLinkModuleType

3.3.21 Get_Enet_Value

3.3.22 Get_Enet_Value2

3.3.23 GetBccBwInfo

3.3.24 GetBccClassInfo

3.3.25 GetBccClassRPR80217

3.3.26 GetBccCosCommitGrp

3.3.27 GetBccCosInfo

3.3.28 GetBGFromBGObj

3.3.29 GetBGFromQoSObj

3.3.30 GetCktDestNodeId

3.3.31 GetCktSecSrcIfIndex

3.3.32 GetCktSecSrcPhysicalLoc

3.3.33 GetCktSecSrcPortName

3.3.34 GetCktSrcIfIndex

3.3.35 GetCktSrcPhysicalLoc

3.3.36 GetCktSrcPortName

3.3.37 GetCollapsedPortname

3.3.38 GetEqptInfoPhysicalLoc

3.3.39 GetIfCosGrp

3.3.40 GetInterfaceId

3.3.41 GetLowBits

3.3.42 GetMgx88xxMgmtState

3.3.43 GetMgx88xxSyncMode

3.3.44 GetMLcardAddnlInfo

3.3.45 GetModelTypeFromPhysicalLoc

3.3.46 GetModuleName

3.3.47 GetModuleName_PM

3.3.48 GetModuleName1

3.3.49 GetModuleName2

3.3.50 GetgetProtectMLcardConfigState

3.3.51 GetNEDBAccessID

3.3.52 GetNEIPAddr

3.3.53 GetObjectTypeFromIfIndex

3.3.54 GetObjectTypeFromIfIndex2

3.3.55 GetOutIPAddr

3.3.56 GetPhysicalLoc

3.3.57 GetPhysicalLocWithPIMPPM

3.3.58 GetPIM

3.3.59 GetPMParameterValue

3.3.60 GetPolicyMapType

3.3.61 GetPort

3.3.62 GetPort_XTC

3.3.63 GetPortModuleName

3.3.64 GetPortModuleName2

3.3.65 GetPortName

3.3.66 GetPortWithPIMPPM

3.3.67 GetPosPortState

3.3.68 GetPosPortStateForRPR802

3.3.69 GetPPM

3.3.70 GetProtectMLCardInfo

3.3.71 GetQoSPhLoc

3.3.72 GetShelf

3.3.73 GetSlot

3.3.74 GetSlotModuleName

3.3.75 GetSlotModuleType

3.3.76 GetSpanTblCktSpanDestNodeId

3.3.77 GetSpanTblCktSpanSrcNodeId

3.3.78 GetSubIf

3.3.79 GetValidCktSecSrcNodeId

3.3.80 GetValidCktSrcNodeId

3.3.81 GetValidModelType

3.3.82 GetValidModuleType

3.3.83 GetValidObjectType

3.3.84 Has_Next

3.3.85 IsIPv6

3.3.86 MapObjectIndex

3.3.87 MergedInvalidityList

3.3.88 NE_Belongs_to_APC_Domain

3.3.89 RemoveFirstByteIf

3.3.90 RemoveLowBits

3.3.91 Time_Diff

3.3.92 TYPE AlarmInfoListType

3.3.93 TYPE AlarmInfoType

3.3.94 TYPE Link_Id_Tabtype

3.4 CTM Triggers

3.4.1 AAT_Before_Trigger

3.4.2 AAT_New_Trigger

3.4.3 Act_Trigger

3.4.4 Aft_Del_L2CktTable_Trigger

3.4.5 Aft_Del_L2Interface_Trigger

3.4.6 Aft_Ins_L2Interface_Trigger

3.4.7 Aft_InsDel_Bridgegroup_Trigger

3.4.8 Circuit_Tbl_Alias_Trigger

3.4.9 Ckt_Tbl_Alias_Trigger

3.4.10 Deleted_User_Trigger

3.4.11 Link_Tbl_Alias_Trigger

3.4.12 NE_Desc_Trigger

3.4.13 NIT_Trigger_New

3.4.14 Security_Aft_Row_All

3.4.15 Security_Aft_Stm_All

3.4.16 Security_Bef_Stm_All

3.4.17 SoTL1_Trigger

3.4.18 Ut_Trigger

3.4.19 VT_Trigger

Packages, Procedures, Functions, and Triggers

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

•CTM Packages

•CTM Stored Procedures

•CTM Functions

•CTM Triggers

3.1 CTM Packages

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

3.1.1 Body DB_Info_Utility

CREATE OR REPLACE

PACKAGE BODY DB_INFO_UTILITY AS

procedure check_archivelog_conf

IS

  SYSTEM_DB_INFO_NOTAVAILABLE EXCEPTION;

  p_status_archivelog            varchar2(13):=null;

  p_CTM_archivelog_status        varchar2(13):=null;

BEGIN

  select upper(LOG_MODE) into p_status_archivelog  from CTMANAGER.INFO_DB_TABLE;

  select upper(ACTIVEVALUE) into p_CTM_archivelog_status from CTM_CONFIG_TABLE

         where SECTIONNAME = 'database' and PROPERTYNAME = 'db-archivelog-enabled';

  IF ( p_status_archivelog = 'NOARCHIVELOG' ) THEN

     IF ( p_CTM_archivelog_status = 'TRUE' ) THEN

      -- CTM Status wrong Archivelog not enabled

      UPDATE CTM_CONFIG_TABLE SET ACTIVEVALUE = 'false' , PERMANENTVALUE = 'false'

             WHERE SECTIONNAME = 'database' and PROPERTYNAME = 'db-archivelog-enabled';

     END IF;

  ELSE

     IF ( p_CTM_archivelog_status = 'FALSE' ) THEN

      -- CTM Status wrong Archivelog enabled

      UPDATE CTM_CONFIG_TABLE SET ACTIVEVALUE = 'true' , PERMANENTVALUE = 'true'

             WHERE SECTIONNAME = 'database' and PROPERTYNAME = 'db-archivelog-enabled';

     END IF;

  END IF;

 EXCEPTION

            when no_data_found then

                raise SYSTEM_DB_INFO_NOTAVAILABLE;

END;

END DB_INFO_UTILITY;

show errors;

3.1.2 Body Security_Pkg

CREATE OR REPLACE PACKAGE body security_pkg AS

   PROCEDURE sysAdminEnable AS

   BEGIN

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

   END sysAdminEnable;

   PROCEDURE sysAdminDisable AS

   BEGIN

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

   END sysAdminDisable;

END security_pkg;

show errors;

3.1.3 Body Conversion_Utility

CREATE OR REPLACE PACKAGE BODY CONVERSION_UTILITY AS

function to_base( p_dec in number, p_base in number )

return varchar2

is

	l_str	varchar2(255) default NULL;

	l_num	number	default p_dec;

	l_hex	varchar2(16) default '0123456789ABCDEF';

begin

	if ( p_dec is null or p_base is null )

	then

		return null;

	end if;

	if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then

		raise PROGRAM_ERROR;

	end if;

	loop

		l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;

		l_num := trunc( l_num/p_base );

		exit when ( l_num = 0 );

	end loop;

	return l_str;

end to_base;

function to_dec

( p_str in varchar2,

  p_from_base in number default 16 ) return number

is

	l_num   number default 0;

	l_hex   varchar2(16) default '0123456789ABCDEF';

begin

	if ( p_str is null or p_from_base is null )

	then

		return null;

	end if;

	for i in 1 .. length(p_str) loop

		l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;

	end loop;

	return l_num;

end to_dec;

function to_hex( p_dec in number ) return varchar2

is

begin

	return to_base( p_dec, 16 );

end to_hex;

function to_bin( p_dec in number ) return varchar2

is

begin

	return to_base( p_dec, 2 );

end to_bin;

function to_oct( p_dec in number ) return varchar2

is

begin

	return to_base( p_dec, 8 );

end to_oct;

END;

show errors;

3.1.4 Conversion_Utility

CREATE OR REPLACE PACKAGE CONVERSION_UTILITY AS

function to_base( p_dec in number, p_base in number )

return varchar2;

function to_dec

( p_str in varchar2,

  p_from_base in number default 16 ) return number;

function to_hex( p_dec in number ) return varchar2;

function to_bin( p_dec in number ) return varchar2;

function to_oct( p_dec in number ) return varchar2;

END;

show errors;

3.1.5 CTMAlarm

Create or Replace package CTMAlarm is

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

INVALID_ALARM_STATUS         EXCEPTION;

PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

ALARM_NOT_FOUND              EXCEPTION;

PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

Procedure alarm_handle_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

);

Procedure clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure add_clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure alarm_handle

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

);

Procedure clear_alarms

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure add_clear_alarms

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

);

Procedure bulk_alarm_handle

    p_AlarmInfoList     IN AlarmInfoListType

);

Procedure bulk_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

);

Procedure bulk_add_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

);

END CTMAlarm;

create or replace package body CTMAlarm is

Procedure alarm_handle_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2)

IS

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

        counter             number:=0;

        total_num_linkid    number:=0;

BEGIN

     IF (p_alarmstatus = 0) THEN

        -- this is the new alarm

        begin

           INSERT INTO active_alarm_table (

                  NEDBACCESSID,

                  ALARMSEQNUM,

                  MODULEORIFINDEX,

                  ACTIVEALARMTIMESTAMP,

                  ACTIVEALARMTYPE,

                  ACTIVEALARMSEVERITY,

                  ACTIVEALARMSERVEFF,

                  ACTIVEALARMADDITIONALINFO,

                  ACKINDEX,

                  ACTIVEALARMFLAG,

                  NEALARMTIMESTAMP,

                  ACTIVEALARMINDEX,

                  MODULETYPE,

                  PHYSICALLOC,

                  ALARMSTATUS,

                  EXTERNALCONDITION,

                  MODELTYPE,

                  OBJECTTYPE,

                  STROBJINSTANCE,

                  DIRECTION,

                  LOCATION)

           Values (

                  p_nedbaccessid,

                  p_alarmseqnum,

                  p_moduleorifindex,

                  p_activealarmtimestamp,

                  p_activealarmtype,

                  p_activealarmseverity,

                  p_activealarmserveff,

                  p_activealarmadditionalinfo,

                  p_ackindex,

                  p_activealarmflag,

                  p_nealarmtimestamp,

                  activealarmindex_seq.nextval,

                  p_moduletype,

                  p_physicalloc,

                  p_alarmstatus,

                  p_externalcondition,

                  p_ModelType,

                  p_ObjectType,

                  p_StrObjInstance,

                  p_StrDirection,

                  p_StrLocation);

           exception

              when DUP_VAL_ON_INDEX then

                   dbms_output.put_line('Duplicate alarm');

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

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

                       where nedbaccessid=p_nedbaccessid

                         AND moduleorifindex=p_ModuleOrIfIndex

                         AND ActiveAlarmType=p_ActiveAlarmType

                         AND NEAlarmTimeStamp=p_NEAlarmTimeStamp

                         AND Physicalloc=p_Physicalloc

                         AND ObjectType = p_ObjectType

                         AND alarmstatus=0;

                      UPDATE active_alarm_table

                         SET activealarmflag=0,

                             alarmstatus=0,

                             clearalarmtimestamp=to_date(''),

                             nealarmcleartimestamp=to_date(''),

                             ActiveAlarmSeverity=p_ActiveAlarmSeverity,

                             ActiveAlarmServEff=p_ActiveAlarmServEff,

                             ActiveAlarmAdditionalInfo=p_ActiveAlarmAdditionalInfo,

                             ModuleType=p_ModuleType,

                             StrObjInstance = p_StrObjInstance

                       WHERE nedbaccessid=p_nedbaccessid

                         AND moduleorifindex=p_ModuleOrIfIndex

                         AND ActiveAlarmType=p_ActiveAlarmType

                         AND NEAlarmTimeStamp=p_NEAlarmTimeStamp

                         AND Physicalloc=p_Physicalloc

                         AND ObjectType = p_ObjectType

                         AND ModelType = p_ModelType;

                      --commit;

                     IF ( counter = 1) THEN

                         return;

                     END IF;

           --commit;

           end;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

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

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

                   IF (p_activealarmseverity = 4) THEN

                       UPDATE link_table

                          SET NumWarningAlarms=NumWarningAlarms+1

                        WHERE linkid=p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 5) Then

                       UPDATE link_table

                          SET NumMinorAlarms=NumMinorAlarms+1

                        WHERE linkid = p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 6) THEN

                       UPDATE link_table

                          SET NumMajorAlarms=NumMajorAlarms+1

                        WHERE linkid = p_linkid(table_row);

                        --commit;

                   END IF;

                   IF (p_activealarmseverity = 7) THEN

                       UPDATE link_table

                          SET NumCriticalAlarms=NumCriticalAlarms+1

                        WHERE linkid= p_linkid(table_row);

                        --commit;

                   END IF;

               END IF;

            END LOOP;

      ELSE

           raise INVALID_ALARM_STATUS;

      END IF;

 END;

Procedure clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2)

IS

        rows_processed               number :=0;

        cursor_handle                INTEGER;

        statement_txt                varchar2(4000):= null;

        total_num_linkid    number:=0;

        --ALARM_NOT_FOUND              EXCEPTION;

        --PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

BEGIN

        ---- alarm resync

        IF (p_alarmstatus = 1 ) THEN

            IF (p_actiontype=1) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

                dbms_output.put_line(p_nedbaccessid);

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

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

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

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

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

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

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

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

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

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

                --dbms_output.put_line(statement_txt);

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

            IF (p_actiontype=2) THEN

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

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

        ------ Normal clear alarms

             IF (p_actiontype = 0) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

              --  dbms_output.put_line(statement_txt);

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

             END IF;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

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

                    IF (p_activealarmseverity = 4) THEN

                        UPDATE link_table

                           SET NumWarningAlarms=NumWarningAlarms-rows_processed

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-rows_processed

                         WHERE linkid= p_linkid(table_row)

                           AND NumCriticalAlarms > 0;

                         --commit;

                    END IF;

               END IF;

           END LOOP;

      ELSE

          raise INVALID_ALARM_STATUS;

      END IF;

 END;

Procedure add_clear_alarms_impl

       (p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition                 IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance                    IN varchar2)

IS

        rows_processed               number :=0;

        cursor_handle                INTEGER;

        statement_txt                varchar2(4000):= null;

        total_num_linkid    number:=0;

        --ALARM_NOT_FOUND              EXCEPTION;

        --PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);

        --INVALID_ALARM_STATUS         EXCEPTION;

        --PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);

BEGIN

        ---- alarm resync

        IF (p_alarmstatus = 1 ) THEN

            IF (p_actiontype=1) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

                dbms_output.put_line(p_nedbaccessid);

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

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

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

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

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

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

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

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

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

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

                dbms_output.put_line(statement_txt);

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

            IF (p_actiontype=2) THEN

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

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

            END IF;

        ------ Normal clear alarms

             IF (p_actiontype = 0) THEN

                cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

              --  dbms_output.put_line(statement_txt);

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

                rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                --commit;

                IF (rows_processed=0) THEN

                   dbms_output.put_line('Alarm_not_found');

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

                   raise ALARM_NOT_FOUND;

                END IF;

                DBMS_SQL.CLOSE_CURSOR (cursor_handle);

             END IF;

           total_num_linkid := p_linkid.count;

           FOR table_row IN 1 .. total_num_linkid LOOP

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

                    IF (p_activealarmseverity = 4) THEN

                        UPDATE link_table

                           SET NumWarningAlarms=NumWarningAlarms-rows_processed

                         WHERE linkid=p_linkid(table_row)

                           AND NumWarningAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 5) Then

                        UPDATE link_table

                           SET NumMinorAlarms=NumMinorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMinorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 6) THEN

                        UPDATE link_table

                           SET NumMajorAlarms=NumMajorAlarms-rows_processed

                         WHERE linkid = p_linkid(table_row)

                           AND NumMajorAlarms > 0;

                         --commit;

                    END IF;

                    IF (p_activealarmseverity = 7) THEN

                        UPDATE link_table

                           SET NumCriticalAlarms=NumCriticalAlarms-rows_processed

                         WHERE linkid= p_linkid(table_row)

                           AND NumCriticalAlarms > 0;

                         --commit;

                    END IF;

               END IF;

           END LOOP;

      ELSE

          raise INVALID_ALARM_STATUS;

      END IF;

EXCEPTION

                when ALARM_NOT_FOUND then

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

                IF (p_actiontype = 1 OR p_actiontype = 2) THEN

                        INSERT INTO active_alarm_table (

                                    NEDBACCESSID,

                                    ALARMSEQNUM,

                                    MODULEORIFINDEX,

                                    ACTIVEALARMTIMESTAMP,

                                    ACTIVEALARMTYPE,

                                    ACTIVEALARMSEVERITY,

                                    ACTIVEALARMSERVEFF,

                                    ACTIVEALARMADDITIONALINFO,

                                    ACKINDEX,

                                    ACTIVEALARMFLAG,

                                    CLEARALARMTIMESTAMP,

                                    NEALARMTIMESTAMP,

                                    NEALARMCLEARTIMESTAMP,

 				ACTIVEALARMINDEX,

                                    MODULETYPE,

                                    PHYSICALLOC,

                                    ALARMSTATUS,

                                    EXTERNALCONDITION,

                                    MODELTYPE,

                                    OBJECTTYPE,

                                    STROBJINSTANCE)

                           Values (

                                    p_nedbaccessid,

                                    p_alarmseqnum,

                                    p_moduleorifindex,

                                    p_activealarmtimestamp,

                                    p_activealarmtype,

                                    p_activealarmseverity,

                                    p_activealarmserveff,

                                    p_activealarmadditionalinfo,

                                    p_ackindex,

                                    p_activealarmflag,

                                    p_clearalarmtimestamp,

                                    p_nealarmtimestamp,

                                    p_nealarmcleartimestamp,

    				activealarmindex_seq.nextval,

                                    p_moduletype,

                                    p_physicalloc,

                                    p_alarmstatus,

                                    p_externalcondition,

                                    p_ModelType,

                                    p_ObjectType,

                                    p_StrObjInstance);

                        --commit;

                END IF;

 END;

Procedure alarm_handle

        p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ActiveAlarmComment         IN varchar2 default null,

        p_NEAlarmTimeStamp           IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2,

        p_StrDirection               IN varchar2,

        p_StrLocation                IN varchar2

IS

BEGIN

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

    alarm_handle_impl

        p_nedbaccessid,

        p_Alarmseqnum,

        p_ModuleOrIfIndex,

        p_ActiveAlarmTimeStamp,

        p_ActiveAlarmType,

        p_ActiveAlarmSeverity,

        p_ActiveAlarmServEff,

        p_ActiveAlarmAdditionalInfo,

        p_AckIndex,

        p_ActiveAlarmFlag,

        p_ActiveAlarmComment,

        p_NEAlarmTimeStamp,

        p_ModuleType,

        p_Physicalloc,

        p_alarmstatus,

        p_linkid,

        p_actiontype,

        p_externalcondition,

        p_ModelType,

        p_ObjectType,

        p_StrObjInstance,

        p_StrDirection,

        p_StrLocation

);

      commit;

 END;

Procedure clear_alarms

        p_nedbaccessid               IN  number,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_clearalarmtimestamp        IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN varchar2

IS

BEGIN

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

        clear_alarms_impl

             p_nedbaccessid,

             p_ModuleOrIfIndex,

             p_ActiveAlarmType,

             p_ActiveAlarmSeverity,

             p_clearalarmtimestamp,

             p_NEAlarmClearTimeStamp,

             p_Physicalloc,

             p_alarmstatus,

             p_linkid,

             p_actiontype,

             p_ModelType,

             p_ObjectType,

             p_StrObjInstance

);

        commit;

END;

Procedure add_clear_alarms

        p_nedbaccessid               IN  number,

        p_Alarmseqnum                IN  number default 0,

        p_ModuleOrIfIndex            IN  number,

        p_ActiveAlarmTimeStamp       IN  date,

        p_ActiveAlarmType            IN  number,

        p_ActiveAlarmSeverity        IN  number,

        p_ActiveAlarmServEff         IN  number,

        p_ActiveAlarmAdditionalInfo  IN varchar2,

        p_AckIndex                   IN number,

        p_ActiveAlarmFlag            IN number,

        p_ClearAlarmTimeStamp        IN date,

        p_NEAlarmTimeStamp           IN date,

        p_NEAlarmClearTimeStamp      IN date,

        p_ModuleType                 IN number,

        p_Physicalloc                IN number,

        p_alarmstatus                IN number,

        p_linkid                     IN link_id_tabtype,

        p_actiontype                 IN number,

        p_externalcondition          IN varchar2,

        p_ModelType                  IN number,

        p_ObjectType                 IN number,

        p_StrObjInstance             IN VARCHAR2

IS

BEGIN

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

        add_clear_alarms_impl

            p_nedbaccessid,

            p_Alarmseqnum,

            p_ModuleOrIfIndex,

            p_ActiveAlarmTimeStamp,

            p_ActiveAlarmType,

            p_ActiveAlarmSeverity,

            p_ActiveAlarmServEff,

            p_ActiveAlarmAdditionalInfo,

            p_AckIndex,

            p_ActiveAlarmFlag,

            p_ClearAlarmTimeStamp,

            p_NEAlarmTimeStamp,

            p_NEAlarmClearTimeStamp,

            p_ModuleType,

            p_Physicalloc,

            p_alarmstatus,

            p_linkid,

            p_actiontype,

            p_externalcondition,

            p_ModelType,

            p_ObjectType,

            p_StrObjInstance

);

        commit;

END;

Procedure bulk_alarm_handle

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

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

        alarm_handle_impl (

            p_AlarmInfoList(i).nedbaccessid,

            p_AlarmInfoList(i).Alarmseqnum,

            p_AlarmInfoList(i).ModuleOrIfIndex,

            p_AlarmInfoList(i).ActiveAlarmTimeStamp,

            p_AlarmInfoList(i).ActiveAlarmType,

            p_AlarmInfoList(i).ActiveAlarmSeverity,

            p_AlarmInfoList(i).ActiveAlarmServEff,

            p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,

            p_AlarmInfoList(i).AckIndex,

            p_AlarmInfoList(i).ActiveAlarmFlag,

            p_AlarmInfoList(i).ActiveAlarmComment,

            p_AlarmInfoList(i).NEAlarmTimeStamp,

            p_AlarmInfoList(i).ModuleType,

            p_AlarmInfoList(i).Physicalloc,

            p_AlarmInfoList(i).alarmstatus,

            p_AlarmInfoList(i).linkid,

            p_AlarmInfoList(i).actiontype,

            p_AlarmInfoList(i).externalcondition,

            p_AlarmInfoList(i).ModelType,

            p_AlarmInfoList(i).ObjectType,

            p_AlarmInfoList(i).StrObjInstance,

            p_AlarmInfoList(i).StrDirection,

            p_AlarmInfoList(i).StrLocation

);

    END LOOP;

    commit;

END;

Procedure bulk_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

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

        clear_alarms_impl

             p_AlarmInfoList(i).nedbaccessid,

             p_AlarmInfoList(i).ModuleOrIfIndex,

             p_AlarmInfoList(i).ActiveAlarmType,

             p_AlarmInfoList(i).ActiveAlarmSeverity,

             p_AlarmInfoList(i).clearalarmtimestamp,

             p_AlarmInfoList(i).NEAlarmClearTimeStamp,

             p_AlarmInfoList(i).Physicalloc,

             p_AlarmInfoList(i).alarmstatus,

             p_AlarmInfoList(i).linkid,

             p_AlarmInfoList(i).actiontype,

             p_AlarmInfoList(i).ModelType,

             p_AlarmInfoList(i).ObjectType,

             p_AlarmInfoList(i).StrObjInstance

);

    END LOOP;

    commit;

END;

Procedure bulk_add_clear_alarms

    p_AlarmInfoList     IN AlarmInfoListType

IS

    i number;

BEGIN

    For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST

    LOOP

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

        add_clear_alarms_impl

            p_AlarmInfoList(i).nedbaccessid,

            p_AlarmInfoList(i).Alarmseqnum,

            p_AlarmInfoList(i).ModuleOrIfIndex,

            p_AlarmInfoList(i).ActiveAlarmTimeStamp,

            p_AlarmInfoList(i).ActiveAlarmType,

            p_AlarmInfoList(i).ActiveAlarmSeverity,

            p_AlarmInfoList(i).ActiveAlarmServEff,

            p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,

            p_AlarmInfoList(i).AckIndex,

            p_AlarmInfoList(i).ActiveAlarmFlag,

            p_AlarmInfoList(i).ClearAlarmTimeStamp,

            p_AlarmInfoList(i).NEAlarmTimeStamp,

            p_AlarmInfoList(i).NEAlarmClearTimeStamp,

            p_AlarmInfoList(i).ModuleType,

            p_AlarmInfoList(i).Physicalloc,

            p_AlarmInfoList(i).alarmstatus,

            p_AlarmInfoList(i).linkid,

            p_AlarmInfoList(i).actiontype,

            p_AlarmInfoList(i).externalcondition,

            p_AlarmInfoList(i).ModelType,

            p_AlarmInfoList(i).ObjectType,

            p_AlarmInfoList(i).StrObjInstance

);

    END LOOP;

    commit;

END;

END CTMAlarm;

3.1.6 DB_Info_Utility

CREATE OR REPLACE

PACKAGE DB_INFO_UTILITY AS

  procedure check_archivelog_conf;

END DB_INFO_UTILITY;

show errors;

3.1.7 getIPSLA_IPADDR

CREATE OR REPLACE function getIPSLA_IPADDR (VAR_NEID number,VAR_SLOTNUMBER number,VAR_BG 
number) return VARCHAR2

IS

    ipAddr VARCHAR2(50);

    cnt number;

BEGIN

    select COUNT(*) into cnt

    from L2_INTERFACE_TABLE

    where NEID = VAR_NEID

      and SLOT_NUMBER = VAR_SLOTNUMBER

      and BRIDGE_GROUP_NUMBER = VAR_BG

      and IP_ADDRESS is not null

      and INTERFACE_TYPE = 190;

    if (cnt = 0) then

      ipAddr := 'N/A';

      else

        select MAX(IP_ADDRESS) into ipAddr

        from L2_INTERFACE_TABLE

          where NEID = VAR_NEID

            and SLOT_NUMBER = VAR_SLOTNUMBER

            and BRIDGE_GROUP_NUMBER = VAR_BG

            and IP_ADDRESS is not null

            and INTERFACE_TYPE = 190;

    end if;

    return ipAddr;

END getIPSLA_IPADDR;

3.1.8 getIPSLA_SUBNETMASK

CREATE OR REPLACE function getIPSLA_SUBNETMASK (VAR_NEID number,VAR_SLOTNUMBER 
number,VAR_BG number) return VARCHAR2

IS

    subNetMask VARCHAR2(50);

    cnt number;

BEGIN

    select COUNT(*) into cnt

    from L2_INTERFACE_TABLE

    where NEID = VAR_NEID

      and SLOT_NUMBER = VAR_SLOTNUMBER

      and BRIDGE_GROUP_NUMBER = VAR_BG

      and SUBNET_MASK is not null

      and INTERFACE_TYPE = 190;

    if (cnt = 0) then

      subNetMask := 'N/A';

      else

        select MAX(SUBNET_MASK) into subNetMask

        from L2_INTERFACE_TABLE

          where NEID = VAR_NEID

            and SLOT_NUMBER = VAR_SLOTNUMBER

            and BRIDGE_GROUP_NUMBER = VAR_BG

            and SUBNET_MASK is not null

            and INTERFACE_TYPE = 190;

    end if;

    return subNetMask;

END getIPSLA_SUBNETMASK;

show errors;

3.1.9 MSMCircuit

--CIRCUIT_BS_TBL

create TYPE circuit_bs_tbl_struct as OBJECT (

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER,

        CKTNAME                 VARCHAR2 (64),

        CKTDESCRIPTION	        VARCHAR2 (256),

        CUSTOMERID              VARCHAR2 (256),

        SERVICEID               VARCHAR2 (256),

        CKTTYPE	                NUMBER,

        CKTSIZE	                NUMBER,

        CKTDIRECTION            NUMBER,

        CKTISMONITOR	        NUMBER,

        CKTSTATE                NUMBER,

        CKTENHANCEDSTATE        NUMBER,

        CKTPROTECTIONTYPE       NUMBER,

        CKTADDITIONALINFO       VARCHAR2(64),

        CKTCOMMENT              VARCHAR2 (2014),

        CKTUSElAP               NUMBER,

        CKTOCHNCCHANNEL         NUMBER,

        CKTOCHNCDIR             NUMBER,

        ISDUPLICATENAME         NUMBER,

        NPID                    NUMBER,

        CKTALIASNAME	VARCHAR2 (64)

);

create TYPE circuit_bs_tbl_struct_list as table of circuit_bs_tbl_struct;

create TYPE circuit_identity_struct as OBJECT (

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER

);

create TYPE circuit_identity_struct_list as table of circuit_identity_struct;

--CIRCUIT_CTP_TBL

create TYPE circuit_ctp_tbl_struct as OBJECT (

        CTP_TYPE                NUMBER,

        CKTNODEID               NUMBER,

        CKTUNIQUEID             NUMBER,

        CTPNODEID               NUMBER,

        CTPMODULETYPE           NUMBER,

        CTPIFINDEX              NUMBER,

        CTPOBJECTINDEX          NUMBER,

        CTPPHYSICALLOC          NUMBER,

        CKTMODELTYPE            NUMBER,

        CTPDROPPROT             NUMBER,

        CKTCTPPATHPROT          NUMBER,

        CTPDROPPROTTYPE         NUMBER,

        CTPPORTNAME             VARCHAR2 (64),

        CTPADDITIONALINFO       VARCHAR2 (64)

);

create TYPE circuit_ctp_tbl_struct_list as table of circuit_ctp_tbl_struct;

--CIRCUIT_SPAN_BS_TBL

create TYPE circuit_span_bs_tbl_struct as OBJECT (

        CKTNODEID                       NUMBER,

        CKTUNIQUEID                     NUMBER,

        CKTLINKID                       NUMBER,

        SPAN_TYPE                       NUMBER,

        SRC_CTP_TYPE                    NUMBER,

        SRC_CTPNODEID                   NUMBER,

        SRC_CTPMODULETYPE               NUMBER,

        SRC_CTPIFINDEX                  NUMBER,

        SRC_CTPOBJECTINDEX              NUMBER,

        SRC_CTPPHYSICALLOC              NUMBER,

        SRC_CKTMODELTYPE                NUMBER,

        DST_CTP_TYPE                    NUMBER,

        DST_CTPNODEID                   NUMBER,

        DST_CTPMODULETYPE               NUMBER,

        DST_CTPIFINDEX                  NUMBER,

        DST_CTPOBJECTINDEX              NUMBER,

        DST_CTPPHYSICALLOC              NUMBER,

        DST_CKTMODELTYPE                NUMBER,

        CKTSPANSRCSTATE		        NUMBER,

        CKTSPANDESTSTATE                NUMBER,

        ISCKTFORWARDING       	        NUMBER,

        SCKTSPANINUPSR      	        NUMBER,

        ISCKTSPANWORKING   	        NUMBER,

        ISCKTSPANACTIVE    	        NUMBER,

        CKTSPANPROTOP	                NUMBER,

        CKTSPANCDLFLOWID                NUMBER,

        CKTSPANSRCPORTNAME              VARCHAR2(64),

        CKTSPANDESTPORTNAME             VARCHAR2(64)

);

create TYPE circuit_span_bs_tbl_st_list as table of circuit_span_bs_tbl_struct;

Create or Replace package MSMCircuit is

Procedure create_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

);

Procedure update_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

);

Procedure update_circuit_ctps

        p_nedbaccessid            IN NUMBER,

        p_ckt_min_physicalloc     IN NUMBER,

        p_ckt_max_physicalloc     IN NUMBER,

        p_cktmoduletype           IN NUMBER,

        cktmoduletype_upd_flag    IN NUMBER,

        p_cktportname             IN VARCHAR2,

        cktportname_udp_flag      IN NUMBER

);

Procedure delete_circuit

        circuit_id_list           IN circuit_identity_struct_list,

        delcomp                   IN NUMBER

);

Procedure delete_circuits_on_nodeList

        circuit_id_list           IN circuit_identity_struct_list

);

END MSMCircuit;

create or Replace package body MSMCircuit is

Procedure create_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

IS

    p_npid NUMBER(1);

    i number;

    create_circuit_failed EXCEPTION;

    i_CTPACCESSID NUMBER;

    i_CKTSPANBSID NUMBER;

    i_CKTSPANSRCCTPACCESSID NUMBER;

    i_CKTSPANDSTCTPACCESSID NUMBER;

    p_CKTCTPNeDBAccessId NUMBER;

BEGIN

    i := circuit_bs_tbl_data.FIRST;

    p_npid := circuit_bs_tbl_data(i).NPID;

--    BEGIN

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

--        select sub.NPID into p_npid

--        from ne_info_table ne, subnetwork_table sub

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

--        and   ne.NeSubNetworkid=sub.subnetid;

--

--        exception

--            when no_data_found then

--                raise create_circuit_failed;

--    END;

    --Remove all old data from the tables if any

    BEGIN

        delete from circuit_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_ctp_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_span_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    insert into circuit_bs_tbl

    values (

        circuit_bs_tbl_data(i).CKTNODEID,

        circuit_bs_tbl_data(i).CKTUNIQUEID,

        circuit_bs_tbl_data(i).CKTNAME,

        circuit_bs_tbl_data(i).CKTDESCRIPTION,

        circuit_bs_tbl_data(i).CUSTOMERID,

        circuit_bs_tbl_data(i).SERVICEID,

        circuit_bs_tbl_data(i).CKTTYPE,

        circuit_bs_tbl_data(i).CKTSIZE,

        circuit_bs_tbl_data(i).CKTDIRECTION,

        circuit_bs_tbl_data(i).CKTISMONITOR,

        circuit_bs_tbl_data(i).CKTSTATE,

        circuit_bs_tbl_data(i).CKTENHANCEDSTATE,

        circuit_bs_tbl_data(i).CKTPROTECTIONTYPE,

        circuit_bs_tbl_data(i).CKTADDITIONALINFO,

        circuit_bs_tbl_data(i).CKTCOMMENT,

        circuit_bs_tbl_data(i).CKTUSElAP,

        circuit_bs_tbl_data(i).CKTOCHNCCHANNEL,

        circuit_bs_tbl_data(i).CKTOCHNCDIR,

        circuit_bs_tbl_data(i).ISDUPLICATENAME,

        p_npid,

        circuit_bs_tbl_data(i).CKTALIASNAME

);

    For I IN circuit_ctp_tbl_data.FIRST .. circuit_ctp_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_ctp_tbl_data(i).CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CTPACCESSID,

            circuit_ctp_tbl_data(i).CTP_TYPE,

            circuit_ctp_tbl_data(i).CKTNODEID,

            circuit_ctp_tbl_data(i).CKTUNIQUEID,

            circuit_ctp_tbl_data(i).CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_ctp_tbl_data(i).CTPMODULETYPE,

            circuit_ctp_tbl_data(i).CTPIFINDEX,

            circuit_ctp_tbl_data(i).CTPOBJECTINDEX,

            circuit_ctp_tbl_data(i).CTPPHYSICALLOC,

            circuit_ctp_tbl_data(i).CKTMODELTYPE,

            circuit_ctp_tbl_data(i).CTPDROPPROT,

            circuit_ctp_tbl_data(i).CKTCTPPATHPROT,

            circuit_ctp_tbl_data(i).CTPDROPPROTTYPE,

            circuit_ctp_tbl_data(i).CTPPORTNAME,

            circuit_ctp_tbl_data(i).CTPADDITIONALINFO,

            p_npid

);

    END LOOP;

    if (circuit_span_bs_tbl_data.count = 0) then

        goto skip_circuit_span_bs_tbl;

    end if;

    For I IN circuit_span_bs_tbl_data.FIRST .. circuit_span_bs_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).SRC_CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANSRCCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANSRCCTPACCESSID,

            circuit_span_bs_tbl_data(i).SRC_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).SRC_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).SRC_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).SRC_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).SRC_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            null,

            p_npid

);

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).DST_CTPNODEID;

        exception

            when no_data_found then

                raise create_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANDSTCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).DST_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).DST_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).DST_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).DST_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).DST_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            null,

            p_npid

);

        SELECT CKTSPANBSID_SEQ.nextval INTO i_CKTSPANBSID FROM dual;

        insert into circuit_span_bs_tbl

        values (

            i_CKTSPANBSID,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).CKTLINKID,

            circuit_span_bs_tbl_data(i).SPAN_TYPE,

            i_CKTSPANSRCCTPACCESSID,

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCSTATE,

            circuit_span_bs_tbl_data(i).CKTSPANDESTSTATE,

            circuit_span_bs_tbl_data(i).ISCKTFORWARDING,

            circuit_span_bs_tbl_data(i).SCKTSPANINUPSR,

            circuit_span_bs_tbl_data(i).ISCKTSPANWORKING,

            circuit_span_bs_tbl_data(i).ISCKTSPANACTIVE,

            circuit_span_bs_tbl_data(i).CKTSPANPROTOP,

            circuit_span_bs_tbl_data(i).CKTSPANCDLFLOWID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            p_npid

);

    END LOOP;

    <<skip_circuit_span_bs_tbl>>

        null;

    commit;

END;

Procedure update_circuit

        circuit_bs_tbl_data       IN circuit_bs_tbl_struct_list,

        circuit_ctp_tbl_data      IN circuit_ctp_tbl_struct_list,

        circuit_span_bs_tbl_data  IN circuit_span_bs_tbl_st_list

IS

    p_npid NUMBER(1);

    i number;

    i_CTPACCESSID NUMBER;

    i_CKTSPANBSID NUMBER;

    update_circuit_failed EXCEPTION;

    i_CKTSPANSRCCTPACCESSID NUMBER;

    i_CKTSPANDSTCTPACCESSID NUMBER;

    p_CKTCTPNeDBAccessId NUMBER;

BEGIN

    i := circuit_bs_tbl_data.FIRST;

    p_npid := circuit_bs_tbl_data(i).NPID;

    BEGIN

        update circuit_bs_tbl

set

            CKTNAME = circuit_bs_tbl_data(i).CKTNAME,

            CKTTYPE = circuit_bs_tbl_data(i).CKTTYPE,

            CKTSIZE = circuit_bs_tbl_data(i).CKTSIZE,

            CKTISMONITOR = circuit_bs_tbl_data(i).CKTISMONITOR,

            CKTSTATE = circuit_bs_tbl_data(i).CKTSTATE,

            CKTENHANCEDSTATE = circuit_bs_tbl_data(i).CKTENHANCEDSTATE,

            CKTPROTECTIONTYPE = circuit_bs_tbl_data(i).CKTPROTECTIONTYPE,

            CKTADDITIONALINFO = circuit_bs_tbl_data(i).CKTADDITIONALINFO,

            CKTUSElAP = circuit_bs_tbl_data(i).CKTUSElAP,

            CKTOCHNCCHANNEL = circuit_bs_tbl_data(i).CKTOCHNCCHANNEL,

            CKTOCHNCDIR = circuit_bs_tbl_data(i).CKTOCHNCDIR,

            ISDUPLICATENAME = circuit_bs_tbl_data(i).ISDUPLICATENAME

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

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

        exception

            when no_data_found then

                raise update_circuit_failed;

    END;

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

    BEGIN

        delete from circuit_ctp_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    BEGIN

        delete from circuit_span_bs_tbl

        where CKTNODEID = circuit_bs_tbl_data(i).CKTNODEID

        and   CKTUNIQUEID = circuit_bs_tbl_data(i).CKTUNIQUEID;

        exception

            when no_data_found then

                null;

    END;

    For I IN circuit_ctp_tbl_data.FIRST .. circuit_ctp_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_ctp_tbl_data(i).CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CTPACCESSID,

            circuit_ctp_tbl_data(i).CTP_TYPE,

            circuit_ctp_tbl_data(i).CKTNODEID,

            circuit_ctp_tbl_data(i).CKTUNIQUEID,

            circuit_ctp_tbl_data(i).CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_ctp_tbl_data(i).CTPMODULETYPE,

            circuit_ctp_tbl_data(i).CTPIFINDEX,

            circuit_ctp_tbl_data(i).CTPOBJECTINDEX,

            circuit_ctp_tbl_data(i).CTPPHYSICALLOC,

            circuit_ctp_tbl_data(i).CKTMODELTYPE,

            circuit_ctp_tbl_data(i).CTPDROPPROT,

            circuit_ctp_tbl_data(i).CKTCTPPATHPROT,

            circuit_ctp_tbl_data(i).CTPDROPPROTTYPE,

            circuit_ctp_tbl_data(i).CTPPORTNAME,

            circuit_ctp_tbl_data(i).CTPADDITIONALINFO,

            p_npid

);

    END LOOP;

    if (circuit_span_bs_tbl_data.count = 0) then

        goto skip_circuit_span_bs_tbl;

    end if;

    For I IN circuit_span_bs_tbl_data.FIRST .. circuit_span_bs_tbl_data.LAST

    LOOP

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).SRC_CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANSRCCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANSRCCTPACCESSID,

            circuit_span_bs_tbl_data(i).SRC_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).SRC_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).SRC_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).SRC_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).SRC_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).SRC_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            null,

            p_npid

);

        BEGIN

            select NEDBACCESSID into p_CKTCTPNeDBAccessId

            from ne_info_table

            where NENODEID = circuit_span_bs_tbl_data(i).DST_CTPNODEID;

        exception

            when no_data_found then

                raise update_circuit_failed;

        END;

        SELECT CTPACCESSID_SEQ.nextval INTO i_CKTSPANDSTCTPACCESSID FROM dual;

        insert into circuit_ctp_tbl

        values (

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).DST_CTP_TYPE,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).DST_CTPNODEID,

            p_CKTCTPNeDBAccessId,

            circuit_span_bs_tbl_data(i).DST_CTPMODULETYPE,

            circuit_span_bs_tbl_data(i).DST_CTPIFINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPOBJECTINDEX,

            circuit_span_bs_tbl_data(i).DST_CTPPHYSICALLOC,

            circuit_span_bs_tbl_data(i).DST_CKTMODELTYPE,

            null,

            null,

            null,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            null,

            p_npid

);

        SELECT CKTSPANBSID_SEQ.nextval INTO i_CKTSPANBSID FROM dual;

        insert into circuit_span_bs_tbl

        values (

            i_CKTSPANBSID,

            circuit_span_bs_tbl_data(i).CKTNODEID,

            circuit_span_bs_tbl_data(i).CKTUNIQUEID,

            circuit_span_bs_tbl_data(i).CKTLINKID,

            circuit_span_bs_tbl_data(i).SPAN_TYPE,

            i_CKTSPANSRCCTPACCESSID,

            i_CKTSPANDSTCTPACCESSID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCSTATE,

            circuit_span_bs_tbl_data(i).CKTSPANDESTSTATE,

            circuit_span_bs_tbl_data(i).ISCKTFORWARDING,

            circuit_span_bs_tbl_data(i).SCKTSPANINUPSR,

            circuit_span_bs_tbl_data(i).ISCKTSPANWORKING,

            circuit_span_bs_tbl_data(i).ISCKTSPANACTIVE,

            circuit_span_bs_tbl_data(i).CKTSPANPROTOP,

            circuit_span_bs_tbl_data(i).CKTSPANCDLFLOWID,

            circuit_span_bs_tbl_data(i).CKTSPANSRCPORTNAME,

            circuit_span_bs_tbl_data(i).CKTSPANDESTPORTNAME,

            p_npid

);

    END LOOP;

    <<skip_circuit_span_bs_tbl>>

        null;

    commit;

END;

Procedure update_circuit_ctps

        p_nedbaccessid            IN NUMBER,

        p_ckt_min_physicalloc     IN NUMBER,

        p_ckt_max_physicalloc     IN NUMBER,

        p_cktmoduletype           IN NUMBER,

        cktmoduletype_upd_flag    IN NUMBER,

        p_cktportname             IN VARCHAR2,

        cktportname_udp_flag      IN NUMBER

IS

    rows_processed    number :=0;

    cursor_handle     INTEGER :=  DBMS_SQL.OPEN_CURSOR;

    statement_txt     varchar2(500);

BEGIN

    --Nothing to be updated

    if(cktmoduletype_upd_flag = 0 and cktportname_udp_flag = 0) then

        return;

    end if;

    statement_txt := 'update circuit_ctp_tbl set ';

    if (cktmoduletype_upd_flag = 1) then

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

    end if;

    if (cktportname_udp_flag = 1) then

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

    end if;

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

                 || ' and CKTCTPPHYSICALLOC between ' || p_ckt_min_physicalloc

                 || ' and ' || p_ckt_max_physicalloc;

    --dbms_output.put_line(statement_txt);

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

    rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

    IF (rows_processed=0) THEN

        dbms_output.put_line('No record found');

        --DBMS_SQL.CLOSE_CURSOR (cursor_handle);

    END IF;

    DBMS_SQL.CLOSE_CURSOR (cursor_handle);

    commit;

END;

Procedure delete_circuit

        circuit_id_list           IN circuit_identity_struct_list,

        delcomp                   IN NUMBER

IS

    i number;

BEGIN

    For I IN circuit_id_list.FIRST .. circuit_id_list.LAST

    LOOP

        if(delcomp = 0) then

            goto continue_loop;

        end if;

        BEGIN

            delete from circuit_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VCG_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        <<continue_loop>>

        null;

        BEGIN

            delete from circuit_ctp_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_span_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID

            and   CKTUNIQUEID = circuit_id_list(i).CKTUNIQUEID;

            exception

                when no_data_found then

                    null;

        END;

    END LOOP;

    commit;

END;

Procedure delete_circuits_on_nodeList

        circuit_id_list           IN circuit_identity_struct_list

IS

    i number;

BEGIN

    For I IN circuit_id_list.FIRST .. circuit_id_list.LAST

    LOOP

        BEGIN

            delete from circuit_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VCG_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_ctp_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from circuit_span_bs_tbl

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

        BEGIN

            delete from CIRCUIT_VLAN_TBL

            where CKTNODEID = circuit_id_list(i).CKTNODEID;

            exception

                when no_data_found then

                    null;

        END;

    END LOOP;

    commit;

END;

END MSMCircuit;

3.1.10 Prune_Package

3.1.10.1 Prune_Package for Small Configurations

rem Pruning Package and change prune schedule package

CREATE OR REPLACE PACKAGE Prune AS

   row_count           NUMBER :=   0 ;

   rows_to_be_deleted  NUMBER := 100000 ;

   ORACLE_SID          varchar2(40);

   ORACLE_BASE         varchar2(20);

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

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

   PROCEDURE prune_auditlog( intervaldays IN Number);

   PROCEDURE prune_errorlog(intervaldays IN number) ;

   PROCEDURE prune_fm( intervaldays IN number);

   PROCEDURE prune_server_monitor( intervaldays IN number);

   PROCEDURE prune_admin_job_table( intervaldays IN number);

END Prune ;

show errors;

CREATE OR REPLACE PACKAGE BODY Prune AS

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

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne, ne_info_Table ne

   WHERE  gne.gnesysid = ne.nesysid AND

          ne.nesysid   = iv_NESysId ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId  AND

          ne.nesysid <> iv_NESysId ;

   lv_GNEId ne_info_table.GNEId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         FOR rec IN GneChildren(lv_GNEId) LOOP

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

         END LOOP ;

      END IF ;

      CLOSE IsThisAGNE ;

   END CheckGne ;

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

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne

   WHERE  gne.gnesysid = iv_NESysid ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId AND

          ne.nesysid <> iv_NESysid ;

   lv_GNEId   ne_info_table.GNEId%TYPE   := NULL ;

   lv_NESysId ne_info_table.NESysId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         OPEN  GneChildren (lv_GNEId ) ;

         FETCH GneChildren INTO lv_NESysId ;

         IF GNEChildren%NOTFOUND THEN

            DELETE gne_table

            WHERE GNEId = lv_GNEId ;

            COMMIT ;

         END IF ;

      END IF ;

      CLOSE IsThisAGNE ;

   END  PruneGNE ;

   PROCEDURE prune_auditlog( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             rows_processed  number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

           prunedate:=sysdate-intervaldays;

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

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

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

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

                     where timestamp < prunedate ) LOOP

                       rowid_tab_v(counter) := i.rowid;

                       counter := counter + 1;

                       IF counter = rows_to_be_deleted THEN

                          FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                                 DELETE FROM transaction_log_table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                                 counter := 0;

                       END IF;

           END LOOP;

           IF counter < rows_to_be_deleted AND counter > 0 THEN

               FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM transaction_log_table

                               WHERE rowid = rowid_tab_v(rid);

               COMMIT;

               rows_processed:=rows_processed+counter;

               counter := 0;

           END IF;

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

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

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

               COMMIT ;

      ELSE

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

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

      END IF;

   EXCEPTION

        WHEN OTHERS THEN

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

   END;

   PROCEDURE prune_errorlog(intervaldays IN number) AS

             prunedate     date;

             counter       number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1) THEN

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

           prunedate:= sysdate-intervaldays;

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

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

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

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

                     where timestamp < prunedate ) LOOP

                     rowid_tab_v(counter) := i.rowid;

                     counter := counter + 1;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM error_log_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                        counter := 0;

                     END IF;

           END LOOP;

           IF counter < rows_to_be_deleted AND counter > 0 THEN

               FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM error_log_table

                               WHERE rowid = rowid_tab_v(rid);

               COMMIT;

               rows_processed:=rows_processed+counter;

               counter := 0;

           END IF;

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

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

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

       ELSE

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

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

       END IF;

     EXCEPTION

         WHEN OTHERS THEN

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

   END;

PROCEDURE prune_fm(intervaldays IN number ) AS

             prunedate    date;

             counter      number :=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

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

             ackwid_tab_v ackwid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1) THEN

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

           prunedate:= sysdate-intervaldays;

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  AlarmEventTimeStamp < prunedate and rownum <= 
rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

           counter:=0;

           rows_processed:=0;

     LOOP

           counter:=0;

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

                     where  ActiveAlarmTimeStamp < prunedate

                       and alarmstatus = 1

                       and ackindex <> 0

                       and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     ackwid_tab_v(counter) := i.ackindex;

                     IF counter = rows_to_be_deleted THEN

                       FOR rid IN 1 .. counter LOOP

                         DELETE FROM ACTIVE_ALARM_TABLE

                         WHERE rowid = rowid_tab_v(rid);

    DELETE FROM ACK_TABLE

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

                    END LOOP;

                    COMMIT;

                       rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                FOR rid IN 1 .. counter LOOP

                  DELETE FROM ACTIVE_ALARM_TABLE

                  WHERE rowid = rowid_tab_v(rid);

           DELETE FROM ACK_TABLE

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

               END LOOP;

                COMMIT;

                rows_processed:=rows_processed+counter;

                counter := 0;

              END IF;

              exit;

           END IF;

      END LOOP;

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  TimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END;

   PROCEDURE prune_server_monitor( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

       IF ( intervaldays != -1 ) THEN

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

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

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  CollectionTime < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM SERVER_MONITOR_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SERVER_MONITOR_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END;

PROCEDURE prune_admin_job_table( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

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

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END;

END prune;

show errors;

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

CREATE OR REPLACE PACKAGE Prune AS

   row_count           NUMBER :=   0 ;

   rows_to_be_deleted  NUMBER := 100000 ;

   ORACLE_SID          varchar2(40);

   ORACLE_BASE         varchar2(20);

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

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

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

   PROCEDURE prune_auditlog( intervaldays IN Number);

   PROCEDURE prune_errorlog(intervaldays IN number) ;

   PROCEDURE prune_fm( intervaldays IN number);

   PROCEDURE prune_server_monitor( intervaldays IN number);

   PROCEDURE prune_admin_job_table( intervaldays IN number);

END Prune ;

show errors;

CREATE OR REPLACE PACKAGE BODY Prune AS

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

AS

        partition_to_be_pruned    varchar2(9);

        cursor_handle   integer;

        cursor_handle_1 integer;

        statement_txt   varchar2(500);

        statement_txt_1 varchar2(500);

        rows_processed  number := 0;

        rows_processed_1 number :=0;

        prunedate         date;

        deleted_rows       number;

        ORACLE_SID       varchar2(20);

	ORACLE_BASE      varchar2(20);

        err_file         varchar2(500);

   BEGIN

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

       SELECT activevalue INTO ORACLE_SID

         FROM ctm_config_table

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

       SELECT activevalue INTO ORACLE_BASE

         FROM ctm_config_table

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

       IF ( intervaldays != -1) THEN

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

          prunedate:= sysdate-intervaldays;

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

          prunedate:= trunc(sysdate-intervaldays);

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

                        WHERE table_name = p_table_name

                          AND column_name like '%TIME%'

                          AND column_position=1)

          LOOP

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

               cursor_handle_1 := DBMS_SQL.OPEN_CURSOR;

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

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

               --DBMS_OUTPUT.PUT_LINE(statement_txt_1);

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

               DBMS_SQL.DEFINE_COLUMN ( cursor_handle_1,1, deleted_rows);

               rows_processed := DBMS_SQL.EXECUTE(cursor_handle_1);

               IF DBMS_SQL.FETCH_ROWS(cursor_handle_1)=0 then

                  EXIT;

               ELSE

                    DBMS_SQL.COLUMN_VALUE (cursor_handle_1,1, deleted_rows);

                    deleted_rows :=nvl(deleted_rows,0);

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

               END IF;

               DBMS_SQL.CLOSE_CURSOR (cursor_handle_1);

               /* prune partitions before the prune intervaldays  */

               FOR i IN ( SELECT partition_name FROM user_segments

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

                          AND   segment_name=rec.table_name)

               LOOP

                        /* drop all the old partition */

                        --dbms_output.put_line(i.partition_name);

                        cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

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

                        rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                        DBMS_SQL.CLOSE_CURSOR (cursor_handle);

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

                END LOOP;

          END LOOP;

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

     END IF;

EXCEPTION

         WHEN OTHERS THEN

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

   END prune_log;

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

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne, ne_info_Table ne

   WHERE  gne.gnesysid = ne.nesysid AND

          ne.nesysid   = iv_NESysId ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId  AND

          ne.nesysid <> iv_NESysId ;

   lv_GNEId ne_info_table.GNEId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         FOR rec IN GneChildren(lv_GNEId) LOOP

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

         END LOOP ;

      END IF ;

      CLOSE IsThisAGNE ;

   END CheckGne ;

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

   CURSOR IsThisAGNE IS

   SELECT gne.gneid

   FROM   gne_table gne

   WHERE  gne.gnesysid = iv_NESysid ;

   CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS

   SELECT ne.nesysid

   FROM   ne_info_table ne

   WHERE  ne.gneid    = iv_gneId AND

          ne.nesysid <> iv_NESysid ;

   lv_GNEId   ne_info_table.GNEId%TYPE   := NULL ;

   lv_NESysId ne_info_table.NESysId%TYPE := NULL ;

   BEGIN

      OPEN  IsThisAGNE ;

      FETCH IsThisAGNE INTO lv_GNEId ;

      IF IsThisAGNE%FOUND THEN

         OPEN  GneChildren (lv_GNEId ) ;

         FETCH GneChildren INTO lv_NESysId ;

         IF GNEChildren%NOTFOUND THEN

            DELETE gne_table

            WHERE GNEId = lv_GNEId ;

            COMMIT ;

         END IF ;

      END IF ;

      CLOSE IsThisAGNE ;

   END  PruneGNE ;

   PROCEDURE prune_auditlog( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             rows_processed  number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

           prunedate:=sysdate-intervaldays;

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

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

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

      ELSE

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

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

      END IF;

   EXCEPTION

        WHEN OTHERS THEN

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

   END;

   PROCEDURE prune_errorlog(intervaldays IN number) AS

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

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

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

   END;

PROCEDURE prune_fm(intervaldays IN number ) AS

             prunedate    date;

             counter      number :=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

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

             ackwid_tab_v ackwid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1) THEN

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

           prunedate:= sysdate-intervaldays;

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  AlarmEventTimeStamp < prunedate and rownum <= 
rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM alarm_event_table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

           counter:=0;

           rows_processed:=0;

     LOOP

           counter:=0;

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

                     where  ActiveAlarmTimeStamp < prunedate

                       and alarmstatus = 1

                       and ackindex <> 0

                       and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     ackwid_tab_v(counter) := i.ackindex;

                     IF counter = rows_to_be_deleted THEN

                       FOR rid IN 1 .. counter LOOP

                         DELETE FROM ACTIVE_ALARM_TABLE

                         WHERE rowid = rowid_tab_v(rid);

    DELETE FROM ACK_TABLE

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

                    END LOOP;

                    COMMIT;

                       rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                FOR rid IN 1 .. counter LOOP

                  DELETE FROM ACTIVE_ALARM_TABLE

                  WHERE rowid = rowid_tab_v(rid);

           DELETE FROM ACK_TABLE

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

               END LOOP;

                COMMIT;

                rows_processed:=rows_processed+counter;

                counter := 0;

              END IF;

              exit;

           END IF;

      END LOOP;

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  TimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM SYSLOG_MESSAGE_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END;

PROCEDURE prune_admin_job_table( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

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

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) 
LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ADMIN_JOB_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END;

END prune;

show errors;

3.1.10.3 Prune_TCA_Event

create or replace PROCEDURE prune_tca_event( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             rows_processed  number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             oracle_sid varchar2(20);

	     ORACLE_BASE   varchar2(20);

             rows_to_be_deleted number:=100000;

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

           prunedate:=sysdate-intervaldays;

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

           
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_tcaevent.log','Starting 
prune TCA EVENTs at '||sysdate);

           
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_tcaevent.log','Prune TCA 
EVENT table before '||prunedate);

           FOR i IN (select /*+ INDEX_FFS(TCA_EVENT_TS_I) */ rowid from tca_event_table

                     where NEEVENTTIMESTAMP < prunedate ) LOOP

                       rowid_tab_v(counter) := i.rowid;

                       counter := counter + 1;

                       IF counter = rows_to_be_deleted THEN

                          FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                                 DELETE FROM tca_event_table

                                 WHERE rowid = rowid_tab_v(rid);

                                 COMMIT;

                                 rows_processed:=rows_processed+counter;

                                 counter := 0;

                       END IF;

           END LOOP;

           IF counter < rows_to_be_deleted AND counter > 0 THEN

               FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST

                               DELETE FROM tca_event_table

                               WHERE rowid = rowid_tab_v(rid);

               COMMIT;

               rows_processed:=rows_processed+counter;

               counter := 0;

           END IF;

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

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

               
append_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_tcaevent.log','Finish 
prune TCA EVENT table at '||sysdate);

               COMMIT ;

      ELSE

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

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

      END IF;

   EXCEPTION

        WHEN OTHERS THEN

        
create_file(ORACLE_BASE||'/admin/'||ORACLE_SID||'/udump','prune_tcaevent.err','Error in 
pruning tca event '||SQLERRM||', '||SQLCODE);

   END;

show errors;

3.1.11 Security_Pkg

CREATE OR REPLACE PACKAGE security_pkg as

   type      security_tab_type is table of rowid index by binary_integer;

   security_tab   security_tab_type;

   security_index binary_integer;

   procedure sysAdminEnable;

   procedure sysAdminDisable;

end security_pkg;

show errors;

3.1.12 Service_Availability

create or replace package service_availability as

   procedure service_activated (instance_name IN VARCHAR2);

   procedure service_deactivated (instance_name IN VARCHAR2);

   procedure service_crashed (instance_name IN VARCHAR2);

   procedure service_restarted (instance_name IN VARCHAR2);

	procedure prune (intervaldays IN NUMBER);

end service_availability;

show errors;

CREATE OR REPLACE

package body service_availability AS

   PROCEDURE service_activated (instance_name IN VARCHAR2) AS

   BEGIN

     -- INSERTING into SERVICE_AVAILABILITY_TABLE

     Insert into "SERVICE_AVAILABILITY_TABLE" 
("SERVICE_INSTANCE_NAME","STATUS","FIRST_ACTIVATED","LAST_CRASH","RUNNING_SINCE","PAST_UPT
IME","DEACTIVATED","PERC_UPTIME") values (instance_name,1,sysdate,null,sysdate,0,null,0);

   COMMIT;

   END service_activated;

   PROCEDURE service_deactivated (instance_name IN VARCHAR2) AS

     past_uptime_var number:=0;

     perc_uptime_var number:=0;

     first_activated_var date;

     running_since_var date;

   BEGIN

SELECT PAST_UPTIME into past_uptime_var FROM SERVICE_AVAILABILITY_TABLE where 
SERVICE_INSTANCE_NAME=instance_name AND

(STATUS=1 OR STATUS=2);

     SELECT FIRST_ACTIVATED into first_activated_var FROM SERVICE_AVAILABILITY_TABLE where

SERVICE_INSTANCE_NAME=instance_name AND (STATUS=1 OR STATUS=2);

     SELECT RUNNING_SINCE into running_since_var FROM SERVICE_AVAILABILITY_TABLE where 
SERVICE_INSTANCE_NAME=instance_name

AND (STATUS=1 OR STATUS=2);

     past_uptime_var:=TIME_DIFF(running_since_var,sysdate) + past_uptime_var;

     perc_uptime_var:=(past_uptime_var/(TIME_DIFF(first_activated_var,sysdate)))*100;

     --append_file('/temp','time_diff.log','past_uptime '||past_uptime_var);

     UPDATE SERVICE_AVAILABILITY_TABLE SET STATUS=0, PAST_UPTIME=past_uptime_var, 
DEACTIVATED=sysdate,

PERC_UPTIME=perc_uptime_var where SERVICE_INSTANCE_NAME=instance_name AND (STATUS=1 OR 
STATUS=2);

COMMIT;

   END service_deactivated;

   PROCEDURE service_crashed (instance_name IN VARCHAR2) AS

     past_uptime_var number:=0;

     perc_uptime_var number:=0;

     first_activated_var date;

     running_since_var date;

   BEGIN

SELECT PAST_UPTIME into past_uptime_var FROM SERVICE_AVAILABILITY_TABLE where 
SERVICE_INSTANCE_NAME=instance_name AND

STATUS=1;

     SELECT FIRST_ACTIVATED into first_activated_var FROM SERVICE_AVAILABILITY_TABLE where

SERVICE_INSTANCE_NAME=instance_name AND STATUS=1;

     SELECT RUNNING_SINCE into running_since_var FROM SERVICE_AVAILABILITY_TABLE where 
SERVICE_INSTANCE_NAME=instance_name

AND STATUS=1;

     past_uptime_var:=TIME_DIFF(running_since_var,sysdate) + past_uptime_var;

     --append_file('/temp','time_diff.log','past_uptime '||past_uptime_var);

     UPDATE SERVICE_AVAILABILITY_TABLE SET STATUS=2, LAST_CRASH=sysdate, 
PAST_UPTIME=past_uptime_var where

SERVICE_INSTANCE_NAME=instance_name AND STATUS=1;

	COMMIT;

   END service_crashed;

   PROCEDURE service_restarted (instance_name IN VARCHAR2) AS

   BEGIN

     UPDATE SERVICE_AVAILABILITY_TABLE SET STATUS=1, RUNNING_SINCE=sysdate where 
SERVICE_INSTANCE_NAME=instance_name AND STATUS=2;

   COMMIT;

   END service_restarted;

PROCEDURE prune (intervaldays IN NUMBER) IS

     prunedate date;

     cnt number;

     ORACLE_SID       varchar2(20);

     ORACLE_BASE      varchar2(20);

     LOGDIR   varchar2(80);

     LOGFILE  varchar2(40);

   BEGIN

   SELECT activevalue INTO ORACLE_SID

   FROM ctm_config_table

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

   SELECT activevalue INTO ORACLE_BASE

   FROM ctm_config_table

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

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

   LOGFILE := 'prune_sa.log';

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

   IF ( intervaldays > 0) THEN

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

     prunedate:=sysdate-intervaldays;

     SELECT count(*) into cnt FROM SERVICE_AVAILABILITY_TABLE where STATUS=0 AND 
DEACTIVATED<prunedate;

     DELETE FROM SERVICE_AVAILABILITY_TABLE where STATUS=0 AND DEACTIVATED<prunedate;

     append_file(LOGDIR, LOGFILE,'Deleted '||cnt||' rows.');

   ELSE

     append_file(LOGDIR,LOGFILE,'Invalid interval '||intervaldays);

   END IF;

   COMMIT;

   END prune;

END service_availability;

show errors;

3.2 CTM Stored Procedures

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

3.2.1 Add_Circuit_Ctps_To_Met

CREATE or REPLACE PROCEDURE add_circuit_ctps_to_met

    p_CktNodeId IN NUMBER,

    p_CktUniqueId IN NUMBER

AS

    p_CustomerId varchar2(256);

    p_ServiceId varchar2(256);

    p_CktType number(2);

    p_PHYSICALLOC number(20);

    p_ifindex number(20);

    p_objecttype number(10);

    p_MODULETYPE number(8);

    p_NEDBAccessID number(10);

BEGIN

    BEGIN

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

               c.CKTSRCMODULETYPE,

               c.CKTSRCPHYSICALLOC,

               c.CKTSRCIFINDEX,

               c.CKTSRCOBJECTTYPE, n.NEDBAccessID

          into p_CktType, p_CustomerId, p_ServiceId, p_MODULETYPE,

               p_PHYSICALLOC, p_ifindex, p_objecttype, p_NEDBAccessID

          from circuit_tbl c, ne_info_table n

         where c.CktNodeId = p_CktNodeId

           and c.CktUniqueId = p_CktUniqueId

           and c.CKTSRCNODEID = n.NENodeID

           and c.CKTSRCNODEID != 0;

        EXCEPTION

            WHEN NO_DATA_FOUND then

                goto END_OF_PROCEDURE;

    END;

    BEGIN

        insert into Managed_Et_tab

            Mng_Entity_Type,

            Mng_Entity_Id_Num,

            Mng_Entity_Id2_Num,

            NeDBAccessId,

            FIELD1_Num,

            FIELD2_Num,

            FIELD3_Num,

            FIELD4_Num,

            Customer_Id_Str,

            Service_Id_Str

        values

            p_CktType,

            p_CktNodeId,

            p_CktUniqueId,

            p_NEDBAccessID,

            p_MODULETYPE,

            p_PHYSICALLOC,

            p_ifindex,

            p_objecttype,

            p_CustomerId,

            p_ServiceId

);

        EXCEPTION

            WHEN DUP_VAL_ON_INDEX then

                update Managed_Et_tab

set

                    FIELD4_Num = p_objecttype,

                    Customer_Id_Str = p_CustomerId,

                    Service_Id_Str = p_ServiceId

                where Mng_Entity_Type = p_CktType

                  and Mng_Entity_Id_Num = p_CktNodeId

                  and Mng_Entity_Id2_Num = p_CktUniqueId

                  and NeDBAccessId = p_NEDBAccessID

                  and FIELD1_Num = p_MODULETYPE

                  and FIELD2_Num = p_PHYSICALLOC

                  and FIELD3_Num = p_ifindex;

    END;

    BEGIN

        select c.CKTSECSRCMODULETYPE,

               c.CKTSECSRCPHYSICALLOC,

               c.CKTSECSRCIFINDEX,

               c.CKTSECSRCOBJECTTYPE, n.NEDBAccessID

          into p_MODULETYPE,

               p_PHYSICALLOC, p_ifindex, p_objecttype, p_NEDBAccessID

          from circuit_tbl c, ne_info_table n

         where c.CktNodeId = p_CktNodeId

           and c.CktUniqueId = p_CktUniqueId

           and c.CKTSECSRCNODEID = n.NENodeID

           and c.CKTSECSRCNODEID != 0;

        EXCEPTION

            WHEN NO_DATA_FOUND then

                goto NEXT_SELECTION;

    END;

    BEGIN

        insert into Managed_Et_tab

            Mng_Entity_Type,

            Mng_Entity_Id_Num,

            Mng_Entity_Id2_Num,

            NeDBAccessId,

            FIELD1_Num,

            FIELD2_Num,

            FIELD3_Num,

            FIELD4_Num,

            Customer_Id_Str,

            Service_Id_Str

        values

            p_CktType,

            p_CktNodeId,

            p_CktUniqueId,

            p_NEDBAccessID,

            p_MODULETYPE,

            p_PHYSICALLOC,

            p_ifindex,

            p_objecttype,

            p_CustomerId,

            p_ServiceId

);

        EXCEPTION

            WHEN DUP_VAL_ON_INDEX then

                update Managed_Et_tab

set

                    FIELD4_Num = p_objecttype,

                    Customer_Id_Str = p_CustomerId,

                    Service_Id_Str = p_ServiceId

                where Mng_Entity_Type = p_CktType

                  and Mng_Entity_Id_Num = p_CktNodeId

                  and Mng_Entity_Id2_Num = p_CktUniqueId

                  and NeDBAccessId = p_NEDBAccessID

                  and FIELD1_Num = p_MODULETYPE

                  and FIELD2_Num = p_PHYSICALLOC

                  and FIELD3_Num = p_ifindex;

    END;

    <<NEXT_SELECTION>>

        null;

    FOR rec IN (

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

               t.CktDestObjectType, n.NEDBAccessID

          from circuit_dest_tbl t, ne_info_table n

         where t.CktNodeId = p_CktNodeId

           and t.CktUniqueId = p_CktUniqueId

           and t.CktDestNodeId = n.NENodeID

           and t.CktDestNodeId != 0

    LOOP

        BEGIN

            insert into Managed_Et_tab

                Mng_Entity_Type,

                Mng_Entity_Id_Num,

                Mng_Entity_Id2_Num,

                NeDBAccessId,

                FIELD1_Num,

                FIELD2_Num,

                FIELD3_Num,

                FIELD4_Num,

                Customer_Id_Str,

                Service_Id_Str

            values

                p_CktType,

                p_CktNodeId,

                p_CktUniqueId,

                rec.NEDBAccessID,

                rec.CktDestModuleType,

                rec.CktDestPhysicalLoc,

                rec.CktDestIfIndex,

                rec.CktDestObjectType,

                p_CustomerId,

                p_ServiceId

);

            EXCEPTION

                WHEN DUP_VAL_ON_INDEX then

                    update Managed_Et_tab

set

                        FIELD4_Num = rec.CktDestObjectType,

                        Customer_Id_Str = p_CustomerId,

                        Service_Id_Str = p_ServiceId

                    where Mng_Entity_Type = p_CktType

                      and Mng_Entity_Id_Num = p_CktNodeId

                      and Mng_Entity_Id2_Num = p_CktUniqueId

                      and NeDBAccessId = rec.NEDBAccessID

                      and FIELD1_Num = rec.CktDestModuleType

                      and FIELD2_Num = rec.CktDestPhysicalLoc

                      and FIELD3_Num = rec.CktDestIfIndex;

        END;

    END LOOP;

    FOR rec IN (

        select v.DESTNEMODULETYPE CktDestModuleType,

               v.DESTPHYSICALLOC CktDestPhysicalLoc,

               v.INTERFACEID CktDestIfIndex,

               v.DESTOBJECTTYPE CktDestObjectType, v.DESTNEDBACCESSID NEDBAccessID

          from circuit_span_view v

         where v.NODEID = p_CktNodeId

           and v.UNIQUEID = p_CktUniqueId

    LOOP

        BEGIN

            insert into Managed_Et_tab

                Mng_Entity_Type,

                Mng_Entity_Id_Num,

                Mng_Entity_Id2_Num,

                NeDBAccessId,

                FIELD1_Num,

                FIELD2_Num,

                FIELD3_Num,

                FIELD4_Num,

                Customer_Id_Str,

                Service_Id_Str

            values

                p_CktType,

                p_CktNodeId,

                p_CktUniqueId,

                rec.NEDBAccessID,

                rec.CktDestModuleType,

                rec.CktDestPhysicalLoc,

                rec.CktDestIfIndex,

                rec.CktDestObjectType,

                p_CustomerId,

                p_ServiceId

);

            EXCEPTION

                WHEN DUP_VAL_ON_INDEX then

                    update Managed_Et_tab

set

                        FIELD4_Num = rec.CktDestObjectType,

                        Customer_Id_Str = p_CustomerId,

                        Service_Id_Str = p_ServiceId

                    where Mng_Entity_Type = p_CktType

                      and Mng_Entity_Id_Num = p_CktNodeId

                      and Mng_Entity_Id2_Num = p_CktUniqueId

                      and NeDBAccessId = rec.NEDBAccessID

                      and FIELD1_Num = rec.CktDestModuleType

                      and FIELD2_Num = rec.CktDestPhysicalLoc

                      and FIELD3_Num = rec.CktDestIfIndex;

        END;

    END LOOP;

    <<END_OF_PROCEDURE>>

        null;

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

END;

3.2.2 Add_Log_Partition

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

create or replace procedure add_log_partition AS

        partition_to_be_added     varchar2(9);

        cursor_handle   integer;

        statement_txt varchar2(500);

        rows_processed  number := 0;

        p_tablespace_name varchar2(25);

        new_partition_date date;

        new_partition_name varchar2(9);

        ORACLE_SID    varchar2(20);

        ORACLE_BASE   varchar2(20);

        add_flag number;

BEGIN

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

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

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

	   SELECT activevalue INTO ORACLE_BASE

            FROM ctm_config_table

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

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

           FOR rec IN ( SELECT table_name FROM user_tables

                        WHERE table_name in (

                          'ERROR_LOG_TABLE', 'SERVER_MONITOR_TABLE',

                          'TRANSACTION_LOG_TABLE','SYSLOG_MESSAGE_TABLE',

                          'NE_AUDIT_TRAIL_TABLE', 'ONS158XX_LAC_LOG_TABLE'

))

          LOOP

                add_flag:=1;

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

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

                  INTO new_partition_date, p_tablespace_name

                From user_segments

                Where segment_name =rec.table_name

                group by tablespace_name;

                IF (sysdate >= new_partition_date) THEN

                    new_partition_date:=sysdate+1;

                ELSIF (new_partition_date >= sysdate+3) THEN

                   add_flag:=0;

                Else

                    new_partition_date:= new_partition_date+1;

                END IF;

                IF (add_flag=1) THEN

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

                    /* add the new partition */

                    cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

                                      TABLESPACE '||p_tablespace_name||' UPDATE INDEXES ';

                    DBMS_OUTPUT.PUT_LINE(statement_txt);

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

                    rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                    DBMS_SQL.CLOSE_CURSOR (cursor_handle);

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

                ELSE

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

                END IF;

          END LOOP;

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

EXCEPTION

         WHEN OTHERS THEN

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

END;

3.2.3 Add_PM_Partition

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

create or replace procedure add_pm_partition AS

        partition_to_be_added     varchar2(9);

        subpartition_15min_to_be_added     varchar2(15);

        subpartition_1day_to_be_added     varchar2(14);

        cursor_handle   integer;

        statement_txt varchar2(500);

        rows_processed  number := 0;

        p_tablespace_name varchar2(25);

        new_partition_date date;

        new_partition_name varchar2(9);

        ORACLE_SID    varchar2(20);

        ORACLE_BASE    varchar2(20);

        pm_service_enabled varchar2(15);

        add_flag number;

        rows_services number := 0;

BEGIN

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

          SELECT activevalue INTO ORACLE_SID

            FROM ctm_config_table

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

          SELECT activevalue INTO ORACLE_BASE

            FROM ctm_config_table

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

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

--

	  --FOR rec IN ( SELECT table_name FROM user_tables

          --              WHERE table_name like '%PM_TABLE'

          --                AND TABLE_NAME !='ONS158XX_TIMESTAMP_PM_TABLE')

--

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

          LOOP

                add_flag:=1;

                        rows_services := rows_services + 1;

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

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

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

                  INTO new_partition_date, p_tablespace_name

                From user_tab_partitions

                Where table_name =rec.table_name

                group by tablespace_name;

                IF (sysdate >= new_partition_date) THEN

                    new_partition_date:=sysdate+1;

                ELSIF (new_partition_date >= sysdate+3) THEN

                   add_flag:=0;

                Else

                    new_partition_date:= new_partition_date+1;

                END IF;

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

		DECLARE

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

                  pmserv_rec pm_service_curs%ROWTYPE;

                begin

		  FOR pmserv_rec IN pm_service_curs

                  LOOP

                   select activevalue INTO pm_service_enabled

		      from ctm_config_table

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

		   exit when pm_service_enabled = 'active';

		  END LOOP;

                end;

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

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

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

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

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

                   /* add the new partition */

                   cursor_handle := DBMS_SQL.OPEN_CURSOR;

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

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

                                      || ' TABLESPACE '||p_tablespace_name;

                   statement_txt := statement_txt || ' ('

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

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

                                     || ')'|| ' UPDATE INDEXES ';

                   --DBMS_OUTPUT.PUT_LINE(statement_txt);

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

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

                   rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

                   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

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

               ELSE

                    IF (add_flag = 0 ) THEN

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

                    ELSE

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

                    END IF;

               END IF;

          END LOOP;

IF (rows_services = 0) THEN

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

	  ELSE

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

          END IF;

EXCEPTION

         WHEN OTHERS THEN

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

END;

show errors;

3.2.4 Alter_User_Permission

create or replace PROCEDURE alter_user_permission

       (UserTypeID   IN   number,

        AddOrRemove  IN   varchar2,

        error_msg    OUT  varchar2)

IS

        r_userid         number(4);

        remove_failed    EXCEPTION;

        add_failed       EXCEPTION;

BEGIN

        IF (AddOrRemove = 'Remove') THEN

           FOR rec IN (select userid FROM user_table

                        where subtypeofUser=userTypeId) LOOP

                      Begin

                        DELETE FROM user_map_table

                         WHERE userid=rec.userid

                           AND groupOrNeid=-2;

                      Exception

                        WHEN OTHERS THEN

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

                             raise remove_failed;

                      END;

           END LOOP;

           commit;

        END IF;

        IF (AddOrRemove = 'Add') THEN

           FOR rec IN (select userid FROM user_table

                        where subTypeOfUser=UserTypeId) LOOP

                       Begin

                            INSERT INTO user_map_table

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

                       Exception

                            WHEN OTHERS THEN

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

                                 raise add_failed;

                       END;

           END LOOP;

           commit;

        END IF;

END;

3.2.5 Append_File

create or replace PROCEDURE append_file

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

IS

       file_handle utl_file.file_type;

BEGIN

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

       utl_file.put_line(file_handle,line_in);

       utl_file.fclose(file_handle);

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

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

END;

3.2.6 BulkNEAddition

create or replace procedure BulkNEAddition

          (OperationType  IN  number,

           GroupToBeInserted  IN number,

           GroupName          IN varchar2,

           GroupDescription   IN varchar2,

           GroupLocationName  IN varchar2,

           GneToBeInserted    IN number,

           GneSysId           IN varchar2,

           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,

	   IpaddrV6           IN raw,

	   isIpV6             IN number default 0,

	   preprovisioned_ip  IN number default 4,

           OperationalState   IN number,

           IsConnected        IN number,

           GneId              IN OUT number,

           NeDescription      IN varchar2,

           NeConfigMode       IN number,

           SnmpCommunityString  IN varchar2,

           ClliCode             IN varchar2,

           NeModelType          IN number,

           NeModelIndex         IN number,

           SubNwId              IN OUT number,

           SystemTitle          IN varchar2,

           UserLabel            IN varchar2,

           VendorName           IN varchar2,

           VersionName          IN varchar2,

           NePmEnabled          IN number,

           DisplayModelName     IN varchar2,

           GwTL1UserName        IN varchar2,

           GwTL1Password        IN varchar2,

	   	   NeDiscState			IN number,

	   	   pmCollectionFlag		IN number,

	   PmTicket		IN varchar2,

           LaunchContext        IN number,

           ParentGroupId        IN number,

           NewGroupId           OUT number,

           NeId                 OUT number,

           error_code           OUT number,

           error_msg            OUT varchar2,

           inNEAliasID	IN varchar2,

           inRoleType	IN varchar2,

           NeVirtualIp          IN number,

	inNeSnmpUname        IN varchar2,

           inNeSnmpAuthProt     IN varchar2,

           inNeSnmpAuthPwd      IN varchar2,

           inNeSnmpPrivPwd      IN varchar2,

           inNeSnmpEid          IN varchar2,

	   isDSS                IN number

IS

           i_groupid            number;

           i_gneid              number;

           i_subNWName          varchar2(64);

           i_subnetid           number;

           i_Neid               number;

           i_parenttype         number;

           assigned_group       number;

           UserProperty         number;

           counter              number:=0;

           i_addnewsubnet       number:=0;

           BulkNeAdd_FAILED          EXCEPTION;

	   NtwPartitionLimit_EXCEEDED EXCEPTION;

	   i_NtwPartitionLimit        number :=0;

	   i_ActualNEsInNPID          number :=0;

BEGIN

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

           IF ((NeModelType = 3) OR (NeModelType = 14) OR (NeModelType = 18) OR 
(NeModelType = 30) OR (NeModelType = 32) ) THEN

             IF ((NeModelType = 14) OR (NeModelType = 30) OR (NeModelType = 32) OR 
(NeModelType = 39)) THEN

                 select to_number(activevalue) into i_NtwPartitionLimit from 
ctm_config_table

		 where sectionname = 'ons15454sdh-net' and propertyname = 'net-threshold';

             ELSE

                 select to_number(activevalue) into i_NtwPartitionLimit from 
ctm_config_table

		 where sectionname = 'ons15454-net' and propertyname = 'net-threshold';

             END IF;

              select count(*) into  i_ActualNEsInNPID from ne_info_table ne, 
subnetwork_table sub

              where ne.NESubNetworkID=sub.subnetID and sub.NPID=NWPartitionId and 
ne.nemodeltype = NeModelType;

              -- append_file('/oracle','bulkneadd.log',' NEs Actuals = '|| 
i_ActualNEsInNPID || ' NEsLimit = ' || i_NtwPartitionLimit );

             IF ((i_ActualNEsInNPID +1) >  i_NtwPartitionLimit ) THEN

                 -- append_file('/oracle','bulkneadd.log',' RAISE EXCEPTION' );

                raise NtwPartitionLimit_EXCEEDED;

             END IF;

           END IF;

           SELECT NEDBACCESSID_SEQ.nextval INTO i_neid FROM dual;

           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,i_neid,'',GroupingOption,GroupPrefix, inUserId);

           ELSE

               i_gneid:=GneId;

           END IF;

           i_addnewsubnet:= SubNWToBeInserted;

           IF (i_addnewsubnet = 0) THEN

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

             IF (counter = 0) THEN

               i_addnewsubnet:=1;

			 ELSE

			   i_addnewsubnet:=0;

             END IF;

           END IF;

           IF (i_addnewsubnet = 1) THEN

              SELECT SUBNETID_SEQ.nextval INTO i_subnetid FROM dual;

              IF (subNwName is null) THEN

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

                   INTO i_subNwName from dual;

                 SELECT count(*) INTO counter from subnetwork_table

                  WHERE subnetname=i_subNwName;

                 IF (counter>0) THEN

                    i_subNwName:=i_subNwName||'-1';

                 END IF;

              ELSE

                  i_subNWName:=subNwName;

              END IF;

              INSERT INTO subnetwork_table

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

           ELSE

               i_subnetid :=SubNwId;

           END IF;

           INSERT INTO ne_info_table

(NEDBAccessID, NESysID, NEIPAddr, NEIPV6Addr, IS_IPV6, PREPROVISIONED_IP, NENSAPAddr, 
NEState, isNEConnected, GNEID, NEDescription, NEConfigMode, NESnmpCommString, NECLLICode, 
NEStateChangeTime, NEModelType, NEModelIndex, NEModelStatus, NESubNetworkID, NETimeOffset, 
NELEDStatus, NESystemTitle,NEUserLabel, NEVendorName, NEVersion, 
NENumActiveCriticalErrors, NENumActiveMajorErrors, NENumActiveMinorErrors, NEPmEnabled, 
NENodeID, NEInventoryLastChanged, NESubnetMask, NEDefaultGateway, 
NENumUnActiveCriticalErrors, NENumUnActiveMajorErrors, NENumUnActiveMinorErrors, 
NENumCleared, DisplayModelName, GWTL1USERNAME, GWTL1PASSWD, ServiceID, DeleteState, 
ConfigSyncStatus, NEUSERNAME, NEUSERPASSWORD, NEDiscoveryState, NERobustPmEnabled, 
SecurityTicket, RobustPMEnableTime, DebugLevel, Robust1DayPMEnableTime, SecondsBehindUTC, 
SecondsDstBehindUTC, NESNGRPID, NEAliasID, IsMultiShelf, RoleType, NEVirtualIpAddr, 
IsTunnelRequired, NE_SNMP_UNAME, NE_SNMP_VERSION, NE_SNMP_AUTH_PROT, NE_SNMP_AUTH_PWD, 
NE_SNMP_PRIV_PWD, NE_SNMP_EID, IsDSS)

           VALUES (i_neid,

                   InNeSysID,

                   IpAddr,

	IpaddrV6

	isIpV6,

	preprovisioned_ip,

'',

                   OperationalState,

                   isConnected,

                   i_gneid,

                   NeDescription,

                   neConfigMode,

                   SnmpCommunityString,

                   ClliCode,

'',

                   NeModelType,

                   NeModelIndex,

0,

                   i_subnetid,

0,

'',

                   systemTitle,

                   userLabel,

                   vendorName,

                   versionName,

0,

0,

0,

                   nepmenabled,

0,

0,

0,

0,

0,

0,

0,

0,

                   DisplayModelName,

                   GwTL1UserName,

                   GwTL1Password,

0,

0,

0,

                   null,

                   null,

		   NeDiscState,

		   pmCollectionFlag,

		   PmTicket,

'',

-1,

'',

0,

0,

0,

                    i_neid,

                   decode(inNEAliasID, null, InNESysid, inNEAliasID),

0,

                   inRoleType,

		   NeVirtualIp,

0,

		inNeSnmpUname,

-1,

		inNeSnmpAuthProt,

		inNeSnmpAuthPwd,

		inNeSnmpPrivPwd,

		inNeSnmpEid,

		isDSS

);

			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 NtwPartitionLimit_EXCEEDED THEN

              error_code := SQLCODE;

              error_msg:= SQLERRM;

              raise BulkNeAdd_Failed;

              rollback;

         WHEN OTHERS THEN

              error_code := SQLCODE;

              error_msg:= SQLERRM;

              raise BulkNeAdd_Failed;

              rollback;

END;

3.2.7 Clear_AllUnack_Alarms

CREATE or REPLACE procedure clear_allunack_alarms

IS

    counter number :=0;

BEGIN

    UPDATE ctm_config_table

       SET activevalue=1

     WHERE propertyname='clear-unack-alarms'

       AND sectionname='ui';

    commit;

    FOR rec IN (select rowid from active_alarm_table

                   where alarmstatus=1

                     and ackindex=0 ) LOOP

         UPDATE active_alarm_table

            SET ackindex=-1

          WHERE rowid=rec.rowid;

         counter:=counter+1;

         IF mod(counter,1000)=0 THEN

            commit;

         END IF;

    END LOOP;

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

    commit;

    UPDATE ctm_config_table

       SET activevalue=0

     WHERE propertyname='clear-unack-alarms'

       AND sectionname='ui';

END;

3.2.8 Create_File

create or replace PROCEDURE create_file

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

IS

       file_handle utl_file.file_type;

BEGIN

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

       IF line_in IS NOT NULL THEN

          utl_file.put_line(file_handle, line_in);

       ELSE

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

       END IF;

       utl_file.fclose(file_handle);

EXCEPTION

       WHEN OTHERS THEN

            dbms_output.put_line(SQLERRM);

            dbms_output.put_line(SQLCODE);

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

END;

3.2.9 Execute_Dml_Statement

    p_statement_txt in varchar2

IS

    pragma AUTONOMOUS_TRANSACTION;

BEGIN

    BEGIN

        execute immediate p_statement_txt;

    END;

END;

show error;

CREATE or REPLACE TRIGGER act_trigger AFTER INSERT on network_partition_table

FOR EACH ROW

DECLARE

    partition_to_be_added varchar2(15);

    statement_txt varchar2(500);

BEGIN

    partition_to_be_added := 'P_NP_'||:new.NPId;

    statement_txt := 'alter table circuit_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_bs_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_ctp_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_span_bs_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_dest_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_span_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_vlan_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_vcg_tbl add partition ' ||

                     partition_to_be_added || ' values(' || :new.NPId ||

                     ') tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

END;

show error;

CREATE OR REPLACE

TRIGGER NEINFO_TRIGGER AFTER INSERT  ON NE_INFO_TABLE

FOR EACH ROW

DECLARE

      flag number;

       partition_to_be_added varchar2(15);

       statement_txt varchar2(500);

       config        varchar2(20);

BEGIN

select permanentvalue into config from ctm_config_table where sectionname='database' and 
propertyname='db-server-configuration';

if (config !='small') then

if ( :new.nemodeltype not in (24,33,34,28 )) then

select count(*) into flag from user_tab_partitions where table_name='CIRCUIT_TBL' and 
partition_name = 'P_NP_1' ;

if ( flag = 0 ) THEN

partition_to_be_added := 'P_NP_1';

 statement_txt := 'alter table circuit_tbl add partition ' || partition_to_be_added || ' 
values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_bs_tbl add partition ' || partition_to_be_added 
|| ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_ctp_tbl add partition ' || partition_to_be_added 
|| ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_span_bs_tbl add partition ' || 
partition_to_be_added || ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_dest_tbl add partition ' || 
partition_to_be_added || ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_span_tbl add partition ' || 
partition_to_be_added || ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_vlan_tbl add partition ' || 
partition_to_be_added || ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

    statement_txt := 'alter table circuit_vcg_tbl add partition ' || partition_to_be_added 
|| ' values(1) tablespace BASE_DATA_TABLESPACE'||' UPDATE INDEXES ';

    execute_dml_statement(statement_txt);

END IF;

END IF;

END IF;

END;

show error;

3.2.10 Execute_Statement

CREATE or REPLACE PROCEDURE execute_statement

(p_statement_txt1  varchar2,

 param1          varchar2,

 p_statement_txt2  varchar2,

 param2         varchar2,

 p_statement_txt3  varchar2)

IS

   cursor_handle  number:=DBMS_SQL.OPEN_CURSOR;

   rows_processed number:=0;

   statement_txt  varchar2(500);

BEGIN

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

   dbms_output.put_line(statement_txt);

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

   rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

   DBMS_SQL.CLOSE_CURSOR(cursor_handle);

EXCEPTION

   WHEN OTHERS THEN

        IF DBMS_SQL.IS_OPEN(cursor_handle) THEN

           DBMS_SQL.CLOSE_CURSOR(cursor_handle);

        END IF;

END execute_statement;

3.2.11 GET_NESYSID

CREATE OR REPLACE FUNCTION GET_NESYSID(neId IN NUMBER)

RETURN VARCHAR2

AS

    retVal  VARCHAR2(128);

BEGIN

  select nesysid into retVal from ne_info_table where nedbaccessid = neId;

  RETURN retVal;

END GET_NESYSID;

show errors;

3.2.12 Login_Disable

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

CREATE OR REPLACE PROCEDURE Login_Disable

IS

   Baseline       date;

   difference     number;

   CURSOR usertable IS

   SELECT * FROM USER_TABLE;

BEGIN

  for rec in usertable loop

   if rec.AUTODISABLEINTERVAL <> 0 then

     if rec.LASTLOGINTIME is NULL then

      Baseline := rec.PASSWORDSETTIME;

     else

      Baseline := rec.LASTLOGINTIME;

     end if;

         difference := sysdate - Baseline;

     if (difference >= rec.AUTODISABLEINTERVAL) then

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

	 commit;

     END IF;

   END IF;

  END LOOP;

END Login_Disable;

3.2.13 PortName

CREATE or REPLACE PROCEDURE PortName

          (p_nedbaccessid IN number,

           p_physicalloc IN number,

           p_ifindex IN number,

           p_moduletype IN number,

           p_objectindex IN number,

           p_portname OUT varchar2)

IS

   o_portname   varchar2(256);

BEGIN

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND ifindex=p_ifindex

           AND moduleType=p_moduletype

           AND objectindex=p_objectindex;

        p_portname := o_portname;

  EXCEPTION

       WHEN TOO_MANY_ROWS

       THEN null;

END PortName;

3.2.14 Prune_Audittrail

3.2.14.1 Prune_Audittrail Procedure for Small Configurations

CREATE or REPLACE PROCEDURE prune_audittrail( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

             rows_to_be_deleted  NUMBER := 100000 ;

             ORACLE_SID      varchar2(40);

	     ORACLE_BASE   varchar2(20);

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

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

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

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

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

           counter:=0;

           rows_processed :=0;

       LOOP

           counter:=0;

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

                     where  TIMESTAMP < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM NE_Audit_Trail_Table

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM NE_Audit_Trail_Table

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

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

     LOOP

           counter:=0;

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

                     where  DateTime < prunedate and rownum <= rows_to_be_deleted) LOOP

                     counter := counter + 1;

                     rowid_tab_v(counter) := i.rowid;

                     IF counter = rows_to_be_deleted THEN

                        commit;

                        FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_LAC_LOG_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                        COMMIT;

                        rows_processed:=rows_processed+counter;

                     END IF;

           END LOOP ;

           IF counter < rows_to_be_deleted THEN

              IF counter > 0 THEN

                  FORALL rid IN 1 .. counter

                               DELETE FROM ONS158XX_LAC_LOG_TABLE

                               WHERE rowid = rowid_tab_v(rid);

                  COMMIT;

                  rows_processed:=rows_processed+counter;

                  counter := 0;

              END IF;

              exit;

           END IF;

     END LOOP;

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

   ELSE

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

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

   END IF;

   EXCEPTION

         WHEN OTHERS THEN

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

   END prune_audittrail;

show error;

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

CREATE OR REPLACE PROCEDURE prune_audittrail( intervaldays IN number ) AS

             prunedate  date;

             counter    number:=0;

             TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;

             rowid_tab_v rowid_tab;

             rows_processed  number :=0;

             ORACLE_SID  varchar2(40);

   BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      IF ( intervaldays != -1 ) THEN

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

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

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

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

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

     ELSE

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

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

     END IF;

     EXCEPTION

         WHEN OTHERS THEN

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

   END;

show error;

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

      ORACLE_BASE           varchar2(20);

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

      SELECT gneid

        FROM gne_table

       WHERE gnesysid=p_nesysid;

      r_gneid ne_info_table.gneid%TYPE;

      INVALID_NESYSID       EXCEPTION;

      PRAGMA EXCEPTION_INIT (INVALID_NESYSID, -20003);

      PRUNE_FAILED          EXCEPTION;

      PRAGMA EXCEPTION_INIT (PRUNE_FAILED, -20004);

BEGIN

      SELECT activevalue INTO ORACLE_SID

        FROM ctm_config_table

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

      SELECT activevalue INTO ORACLE_BASE

        FROM ctm_config_table

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

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

      OPEN IsGne(p_nesysid);

      FETCH IsGne INTO r_gneid;

      IF IsGne%FOUND THEN

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

         SELECT count(*) INTO counter

           FROM ne_info_table

          WHERE gneid=r_gneid ;

         IF (counter > 1 ) THEN

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

            FOR rec IN (SELECT nesysid FROM ne_info_table

                         WHERE gneid=r_gneid and nesysid <> p_nesysid ) LOOP

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

            END LOOP;

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

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

            prune_process_state :=1;

         END IF;

      END IF;

      CLOSE IsGne;

      IF (prune_process_state=0) THEN

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

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

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

          /* get the nedbaccessid from nesysid  */

	SELECT nedbaccessid, nenodeid 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 like '%NODE_ID%' and a.table_name like 
'%_DATA') OR

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

                                                                IN (

                                                                  'SCMNODE',

                                                                  'SCMENABLELIST',

                                                                  'COLL_ERR_LOG',

                                                                  'COLL_INFO',

                                                                  'COLL_INFO',

                                                                  'COLL_INFO',

                                                                  'COLL_SUMM_LIST',

                                                                  'COMP_FILE_LIST',

                                                                  'DESTROYNODE',

                                                                  'FILE_ERR_LOG',

                                                                  'FILE_INFO',

                                                                  'FILE_QUEUE_LOG',

                                                                  'FILE_TRANSFER_LOG',

                                                                  'PEND_FILE_LIST',

                                                                  'SCMCARDCOLL',

                                                                  'SCMCARDCOLLHOST',

                                                                  'SCMCARDCOLLSTATUS',

                                                                  'SCMCARDENABLE',

                                                                  'SCMDB_NODE_INFO',

                                                                  'SCMENABLELIST',

                                                                  'SCMENABLESUBTYPE',

                                                                  'SCMNODE',

                                                                  'SCMNODECOLL',

                                                                  'SCMNODECOLLHOST',

                                                                  'SCMNODECOLLSTATUS',

                                                                  'SCMNODEENABLE',

                                                                  'SYNC_INFO',

                                                                  'SYNC_LIST'

                            ) OR

                            a.column_name='CKTNODEID'    OR

                            a.column_name='CHILDID'      OR

                            a.column_name='GROUPORNEID') AND

							a.table_name NOT LIKE 'BIN$%')

                         ORDER BY table_name)

           LOOP

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

                                                 'PURGED_NE_TABLE' ,1,

                                                 'USER_NE_TABLE'   ,1,

                                                                    0) INTO prune_state 
FROM dual;

                     IF (prune_state = 0 ) THEN

                        rows_deleted_total :=0;

                        cursor_handle := DBMS_SQL.OPEN_CURSOR;

                        IF (i.table_name='ACTIVE_ALARM_TABLE') THEN

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

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

                                             AND rownum < = '||rows_deleted;

                        END IF;

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

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

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

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

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

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

                        -- purge all circuit tables based on cktnodeid

                        IF (i.column_name='CKTNODEID' ) THEN

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

                                             ' AND rownum < = '||rows_deleted;

                        END IF;

                        IF (i.column_name='CHILDID') THEN

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

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

                                            ' AND rownum < = '||rows_deleted;

                        END IF;

                        -- this is user_map_table

                        IF (i.column_name='GROUPORNEID') THEN

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

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

                                             ' AND rownum <= '||rows_deleted;

                        END IF;

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

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

                        LOOP

                             rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);

                             rows_deleted_total:=rows_deleted_total+rows_processed;

                             commit;

                             IF rows_processed=0 THEN

                                exit;

                             END IF;

                        END LOOP;

Table Of Contents

Packages, Procedures, Functions, and Triggers

3.1 CTM Packages

3.1.1 Body DB_Info_Utility

3.1.2 Body Security_Pkg

3.1.3 Body Conversion_Utility

3.1.4 Conversion_Utility

3.1.5 CTMAlarm

3.1.6 DB_Info_Utility

3.1.7 getIPSLA_IPADDR

3.1.8 getIPSLA_SUBNETMASK

3.1.9 MSMCircuit

3.1.10 Prune_Package

3.1.10.1 Prune_Package for Small Configurations

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

3.1.10.3 Prune_TCA_Event

3.1.11 Security_Pkg

3.1.12 Service_Availability

3.2 CTM Stored Procedures

3.2.1 Add_Circuit_Ctps_To_Met

3.2.2 Add_Log_Partition

3.2.3 Add_PM_Partition

3.2.4 Alter_User_Permission

3.2.5 Append_File

3.2.6 BulkNEAddition

3.2.7 Clear_AllUnack_Alarms

3.2.8 Create_File

3.2.9 Execute_Dml_Statement

3.2.10 Execute_Statement

3.2.11 GET_NESYSID

3.2.12 Login_Disable

3.2.13 PortName

3.2.14 Prune_Audittrail

3.2.14.1 Prune_Audittrail Procedure for Small Configurations

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

3.2.15 Prune_NE