DACOT [NSN-AC]

来源：互联网发布：电子狗软件排名榜编辑：程序博客网时间：2024/05/01 01:48

#!/bin/bash

sqlplus -S / as sysdba <<EOF

set head off

@01_grant.sql;

exit

EOF

echo "Grant system privellige .... done!"

sqlplus -S system/manager <<EOF

set head off

@02_create_dblink.sql;

@03_create_aux_table.sql;

@04_create_dacot.sql;

exit

EOF

echo "Dacot installation .... done!"

CREATE OR REPLACE PROCEDURE Dacot (

piNEType varchar2,

piNEInsValue varchar2,

piDataCat varchar2,

piAdRel varchar2,

piCandiSelType varchar2,

piCandiSelExp varchar2,

piCompareMode varchar2,

piTraceLevel integer,

piDurationCp varchar2,

piDebugNull varchar2,

piRoundFix integer,

piIgnoreTopo varchar2,

piKnownIssue varchar2,

piCustomLogName varchar2

)

/* What's new

V9.1:

2010-08-12:

Fix data different (no. of rows different) bug. (Duplicate counter detail of previous table)

Improve different counter shown message.

v9.2:

2010-08-24

Extra table Validation. If a table which should be removed in OSS6 but still exists, then [NOK] Code=102

2010-08-26

1.Add parameter piIgnoreTopo. If piIgnoreTopo='Y', then skip compare topology ID. Otherwise (NULL or other value) compare as normal.

2.Enhanced logic of duration check. Handle this tough scenario:ADMSS_DIAMSC_DESTNAME_DAY: DIAMSC_DURATION, PRI_UNA_DURATION, SEC_UNA_DURATION

2010-08-27

1.Fix bug. When seeking Sample GID, just filter like this: mco.co_dn like 'PLMN%/MSC-4850%'.

Because some measurement can create managed object adhoc. e.g. HOBSCM : PLMN-PLMN/MSC-4850/BSCM-100

v9.3:

2010-08-31

Add known issue handling. Add new parameter: piHandleKnownIssue. If piHandleKnownIssue = y|Y then enable this feature.

add onw new asTraceCode: 175: know exceptions. Exception Category is treated as table_cat.

2010-09-01

Enhanced XXX_DURATION mapping logic.:

a. If oss6 table is XXXXBH , then OSS6 string is XXXX

b. Special exception column : 'AVANHLR_DURATION', 'SLPNHLR_DURATION' map to PERIOD_DURATION (Hardcode)

2010-09-03

1. Fix bug: ADMSS_CSSB, the top level is PLMN-PLMN/MSC-4850/BSCM-100, can not using mco.co_object_instance = '4850' as the filter.

Should be lik this: and mco.co_dn like ''PLMN%/'||asKeyIDCat||'-'||asKeyIDValue||'%''';

2. Add 3 exceptions to "true duration column white list" :

'AVANMSC_DURATION', 'Y',

'SLPNMSC_DURATION', 'Y',

'ROUTMSC_DURATION', 'Y',

v9.4:

2010-09-04

Support multi-range of tables:

piCandiSelType piCandiSelExp

ALL disabled

RANGE X-Y (from X to Y)

ADHOC in ( xxx ) . xxx must match SQL syntax . e.g: 'xxxx' , or 'xxx','yyy','zzz'

TMS xxx without ''

2010-09-07

BIG ENHANCEMENT!

1.Modify duration mapping logic. Get exact measurement name. Disuse the duration white list in v9.3

2.Modify TOPO id mapping logic. Using exact mapping SQL.

2010-09-09

Bug fix. Add Adaptation_Release filter for topo seek SQL to avoid multi-rows return.

v9.5

2010-09-16

Refine and normalize parameters exit name.

2010-09-19

Extend piCompareMode, if 'DEBUG' then do nothing but print table loop sql.

2010-09-20

Display Measurement name in detail log

2010-09-27

Enhance Start_time (Busy hour) compare sql.

Improve SQL for seeking different counters.

v9.6

Refine code. Delete parameter piAggTabCvt.

2010-10-15

Extend Candidate table defination. Only Compare tables in TEMP_DACOT_CAND_TABLE

piCandiSelType piCandiSelExp

CAN disabled

2010-10-18

Add a parameter piCustomLogName to customize DACOT log file name. If null, use DACOT default log name.

type curtype is ref cursor;

sumCur curtype;

detCur curtype;

tableCur curtype;

oss6IDCur curtype;

logf UTL_FILE.file_type;

log_dir VARCHAR2(100) := 'DATA_PUMP_DIR';

summary_log VARCHAR2(100);

detail_log VARCHAR2(100);

other_log VARCHAR2(100);

asDacotVersion varchar2(10) := 'v9.6';

asKeyIDCat varchar2(20) := upper(piNEType);

asIDSpecify varchar2(1) := 'Y';

asKeyIDValue varchar2(20) := piNEInsValue;

asDataCat varchar2(30) := upper(piDataCat);

asCompareMode varchar2(10) := upper(piCompareMode);

asAdapID varchar2(50);

asAdapRel varchar2(30) := upper(piAdRel);

asAggTabCvt varchar2(10);

asFromTable varchar2(20) := 'A'; -- A:65

asToTable varchar2(20) := 'Z';

compareDuration varchar2(1) := upper(piDurationCp);

asCandiSelType varchar2(10) := upper(piCandiSelType);

asCandiSelExp varchar2(2000):= upper(piCandiSelExp);

asCrSumSQL VARCHAR2(500);

asCrDetSQL VARCHAR2(500);

asTempTname VARCHAR2(40);

asTableLoopSQL VARCHAR2(4000);

asTableLoopSQL1 VARCHAR2(3000);

asTraceCode integer := 0;

as5schema varchar2(40);

as5Tname varchar2(40);

asMOSchema varchar2(30) := 'MOF';

as6schema varchar2(40);

as6Tname varchar2(40);

asTableStr varchar2(40);

asBHind varchar2(1);

asTableCat varchar2(10);

asLWOSchema varchar2(30);

as6tableInd integer;

as6IDCol varchar2(40);

asLogicalCtr varchar2(40);

asMeasName varchar2(40);

asTempSQL VARCHAR2(10000);

asTempSQL2 VARCHAR2(20000);

asTempSQL3 VARCHAR2(10000);

asIDMappingSQL VARCHAR2(2000);

tempStrSum VARCHAR2(100);

tempStr VARCHAR2(1000);

as1st6Dim varchar2(1);

asSeekIDSQL varchar2(1000);

asSampleDataID integer;

asMeasDimType varchar2(20) := 'TRANSIENT';

asSelectAliasNo integer;

asIdDeepInd integer;

asSeek5IdPos integer;

as5IdCol varchar2(30);

asTname5str varchar2(40);

as6LWTable varchar2(30);

as5TopoStr varchar2(15000);

as5TopoID varchar2(1000);

as5CounterStr varchar2(14000);

as5Counter varchar2(40);

as5FromStr varchar2(1000);

as5WhereStr varchar2(1000);

as6TopoStr varchar2(15000);

as6TopoID varchar2(1000);

as6CounterStr varchar2(14000);

as6FromStr varchar2(1000);

as6WhereStr varchar2(1000);

asSQL5 varchar2(16300);

asSQL6 varchar2(16300);

asSQL5Cnt varchar2(16300);

asSQL6Cnt varchar2(16300);

asCompareSQL varchar2(32760);

asCompareSQLSum varchar2(32760);

as5RowsCnt integer;

as6RowsCnt integer;

asDiffNum integer;

as5CntSQL varchar2(1000);

as6CntSQL varchar2(1000);

as5TimeStr varchar2(500);

as6TimeStr varchar2(500);

as5LoopCt varchar2(14000);

as6LoopCt varchar2(14000);

as5CpCounter varchar2(50);

as6CpCounter varchar2(50);

asDiffCounter varchar2(50);

asCntDatatype varchar2(50);

as5DiffCnt varchar2(5000);

as6DiffCnt varchar2(5000);

asDiffInfo varchar2(5000);

asDiffDetSQL varchar2(10000);

diffInd varchar2(1);

asIgnoreTopo varchar2(1) := upper(piIgnoreTopo);

asHandleKnownIssue varchar2(1) := upper(piKnownIssue);

asKnownIssueSum varchar2(40);

asExpCat varchar2(20);

asResultCode integer;

asResultDesc varchar2(50);

asSumCnt integer;

asPct varchar2(10);

asTargetCode integer;

INVALID_PARAMETER EXCEPTION;

RUNTIME_ERR EXCEPTION;

CR_TEMP_T_ERR EXCEPTION;

asRuntimeTrace varchar2(21000);

--v9.5

asDiffSqlOrder varchar2(200);

--e_TooLong Exception;

--pragma Exception_Init(e_TooLong, -01489);

s1 integer;

s2 integer;

asTcat varchar2(20);

asTnum integer;

asTpct varchar2(10);

BEGIN

if piCustomLogName is null then

summary_log := 'dacot_'||asKeyIDCat||'-'||asKeyIDValue||'_'||asDataCat||'_sum_';

detail_log := 'dacot_'||asKeyIDCat||'-'||asKeyIDValue||'_'||asDataCat||'_det_';

other_log := 'dacot_'||asKeyIDCat||'-'||asKeyIDValue||'_'||asDataCat||'_oth_';

else

summary_log := piCustomLogName||'_sum_';

detail_log := piCustomLogName||'_det_';

other_log := piCustomLogName||'_oth_';

end if;

-- Parameter validation --

if asKeyIDCat = 'MSC' then

asLWOSchema := 'ADMSS';

else

asLWOSchema := 'AD'||asKeyIDCat;

end if;

if asKeyIDCat = 'MSC' OR asKeyIDCat = 'HLR' OR asKeyIDCat = 'CDS' OR asKeyIDCat = 'SRR' then

asAggTabCvt := '2';

else

asAggTabCvt := '1';

end if;

if asDataCat = 'RAW' then

as6schema := asLWOSchema||'_PMB';

if asLWOSchema = 'ADMSS' then

as5schema := 'XMLNSSRAW';

asAdapID := 'NOKMSS';

elsif asLWOSchema = 'ADHLR' then

as5schema := 'XMLNSSRAW';

asAdapID := 'NOKHLR';

elsif asLWOSchema = 'ADCDS' then

as5schema := 'XMLNSSRAW';

asAdapID := 'com.nsn.cds';

elsif asLWOSchema = 'ADSRR' then

as5schema := 'XMLNSSRAW';

asAdapID := 'com.nsn.srr';

elsif asLWOSchema = 'ADMGW' then

as5schema := 'NOKMWWRAW';

asAdapID := 'NOKMGW';

else

as5schema := 'NOT_FOUND';

asAdapID := 'NOT_FOUND';

end if;

elsif asDataCat = 'AGG' then

as6schema := asLWOSchema||'_PMWH';

if asLWOSchema = 'ADMSS' then

as5schema := 'XMLNSS';

asAdapID := 'NOKMSS';

elsif asLWOSchema = 'ADHLR' then

as5schema := 'XMLNSS';

asAdapID := 'NOKHLR';

elsif asLWOSchema = 'ADCDS' then

as5schema := 'XMLNSS';

asAdapID := 'com.nsn.cds';

elsif asLWOSchema = 'ADSRR' then

as5schema := 'XMLNSS';

asAdapID := 'com.nsn.srr';

elsif asLWOSchema = 'ADMGW' then

as5schema := 'NOKMWW';

asAdapID := 'NOKMGW';

else

as5schema := 'NOT_FOUND';

asAdapID := 'NOT_FOUND';

end if;

else

raise INVALID_PARAMETER;

end if;

if not (asCompareMode = '5-6' or asCompareMode = '6-5' or asCompareMode = 'DEBUG') then

raise INVALID_PARAMETER;

end if;

if as5schema = 'NOT_FOUND' then

raise INVALID_PARAMETER;

end if;

if asCandiSelType = 'RANGE' then

if length(asCandiSelExp) = 3 then

asFromTable := substr(asCandiSelExp,1,1);

asToTable := substr(asCandiSelExp,3,1);

end if;

--------------------------

summary_log := summary_log||TO_CHAR(SYSDATE,'YYYYMMDD_HH24_MI_SS')||'.log';

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '########## OSS5 vs OSS6 Data Compare Summary Log ##########');

UTL_FILE.put_line (logf, 'Dacot '||asDacotVersion||' Begin at : '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '---- Parameter list ----');

UTL_FILE.put_line (logf, 'NECat : '||asKeyIDCat);

UTL_FILE.put_line (logf, 'NEInsValue : '||asKeyIDValue);

UTL_FILE.put_line (logf, 'DataCat : '||asDataCat);

UTL_FILE.put_line (logf, 'AdapRelease : '||asAdapRel);

UTL_FILE.put_line (logf, 'CandidateType: '||asCandiSelType);

UTL_FILE.put_line (logf, 'CandidateValue: '||asCandiSelExp);

UTL_FILE.put_line (logf, 'CompareMode : '||piCompareMode);

UTL_FILE.put_line (logf, 'TraceLevel : '||piTraceLevel);

UTL_FILE.put_line (logf, 'CompareDuration : '||piDurationCp);

UTL_FILE.put_line (logf, 'NullColumnDebug : '||piDebugNull);

UTL_FILE.put_line (logf, 'NumbericRound : '||piRoundFix);

UTL_FILE.put_line (logf, 'IgnoreTopology : '||piIgnoreTopo);

UTL_FILE.put_line (logf, 'KnownIssueFilter: '||piKnownIssue);

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '---- Runtime Log ----');

UTL_FILE.fclose (logf);

detail_log := detail_log||TO_CHAR(SYSDATE,'YYYYMMDD_HH24_MI_SS')||'.log';

logf := UTL_FILE.fopen (log_dir, detail_log, 'A');

UTL_FILE.put_line (logf, '########## OSS5 vs OSS6 Data Compare Detail Log ##########');

UTL_FILE.put_line (logf, 'Begin at : '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));

UTL_FILE.fclose (logf);

if upper(piDebugNull) = 'Y' then

other_log := other_log||TO_CHAR(SYSDATE,'YYYYMMDD_HH24_MI_SS')||'.log';

logf := UTL_FILE.fopen (log_dir, other_log, 'A');

UTL_FILE.put_line (logf, '--- OSS5 null counter statistics ---');

UTL_FILE.fclose (logf);

end if;

asCrSumSQL := 'create table temp_dacot_sum (

resultCode integer,

resultDesc varchar2(50),

sumCnt integer,

cat varchar2(10),

remarks varchar2(300)

)';

asCrDetSQL := 'create table temp_dacot_det

( resultCode integer,

schema5 varchar2(30),

tableName5 varchar2(40),

schema6 varchar2(30),

tableName6 varchar2(40),

tableCat varchar2(20),

remarks varchar2(300)

)';

-- Create Temp table for statistics

begin

select table_name

into asTempTname

from dba_tables

where owner = 'SYSTEM'

and table_name = 'TEMP_DACOT_SUM';

asTempSQL := 'truncate table TEMP_DACOT_SUM';

execute immediate asTempSQL;

exception

when no_data_found then

execute immediate asCrSumSQL;

end;

begin

select table_name

into asTempTname

from dba_tables

where owner = 'SYSTEM'

and table_name = 'TEMP_DACOT_DET';

asTempSQL := 'truncate table TEMP_DACOT_DET';

execute immediate asTempSQL;

exception

when no_data_found then

execute immediate asCrDetSQL;

end;

exception

when others then

asRuntimeTrace := sqlerrm;

raise CR_TEMP_T_ERR;

end;

asTableLoopSQL1 := '

SELECT OSS5_TNAME, TABLE_STR,

decode ('''||asAggTabCvt||'''

,''1'', '''||asLWOSchema||'_''||'||'decode('''||asDataCat||''',''AGG'',OSS6_MAIN||decode(OSS6_LEVEL,''_BH'',''_DAYBH'',OSS6_LEVEL), TABLE_STR)

,''2'', '''||asLWOSchema||'_''||'||'decode('''||asDataCat||''',''AGG'',OSS6_MAIN||decode(OSS6_LEVEL,''_WEEKBH'',''_WBH'',OSS6_LEVEL), TABLE_STR)

) OSS6_TNAME

,decode('''||asDataCat||''',''RAW'',''N'',decode(substr(OSS5_TNAME,length(OSS5_TNAME)-1),''BH'',''Y'',''N'')) BH_ind

,decode('''||asDataCat||''',''RAW'',''RAW'',replace(OSS6_LEVEL,''_'','''')) table_cat

FROM (

select table_name OSS5_TNAME

,substr(table_name, headT6, instr(table_name,''_'',1,3)-headT6) TABLE_STR

,REGEXP_REPLACE(substr(table_name,headT6, instr(table_name,''_'',-1)-headT6),''[0-9]{1,}$'','''') OSS6_MAIN

,OSS6_LEVEL

from (

SELECT table_name

, instr(table_name,''_P_'',1)+3 headT6

,substr(table_name, instr(table_name,''_'',-1)) OSS6_LEVEL

from dba_tab_columns@OSS5

where owner= '''||as5schema||'''

and table_name like ''%/_P/_%'' escape ''/''

and column_name = '''||asKeyIDCat||'_ID'||'''';

if asCandiSelType = 'ALL' then

-- ALL

asTableLoopSQL := asTableLoopSQL1 ||'

)

order by OSS5_TNAME';

elsif asCandiSelType = 'RANGE' then

-- RANGE

asTableLoopSQL := asTableLoopSQL1 ||'

)

where ascii(substr(table_name,headT6,1)) >= ascii('''||asFromTable||''')

and ascii(substr(table_name,headT6,1)) <= ascii('''||asToTable||''')

)

order by OSS5_TNAME';

elsif asCandiSelType = 'ADHOC' then

-- ADHOC

asTableLoopSQL := asTableLoopSQL1 ||'

and table_name IN ('''||replace(asCandiSelExp,',',''',''')||''')

)

order by OSS5_TNAME';

elsif asCandiSelType = 'TMS' then

-- TMS

asTableLoopSQL := asTableLoopSQL1 ||'

)

where OSS6_TSTR = '''||asCandiSelExp||'''

order by OSS5_TNAME';

elsif asCandiSelType = 'CAN' then

-- CAN

asTableLoopSQL := 'select OSS5_TNAME, TABLE_STR, OSS6_TNAME, BH_ind, table_cat

from ('||asTableLoopSQL1 ||'

)

) ot56, TEMP_DACOT_CAND_TABLE ct

where ct.schema_name = '''||as6schema||'''

and ot56.OSS6_TNAME = ct.table_name';

else

raise INVALID_PARAMETER;

end if;

if asCompareMode = 'DEBUG' then

goto tail;

end if;

open tableCur for asTableLoopSQL;

loop

fetch tableCur into as5Tname, asTableStr, as6Tname, asBHind, asTableCat;

exit when tableCur%notfound;

as5TopoStr := '';

as6TopoStr := '';

asTraceCode := 1;

asDiffSqlOrder := 'order by ';

select count(1) into s1

from TEMP_DACOT_EXCP_TABLE

where cat = asKeyIDCat

and exp_sum = 'REMOVED'

and table_str = asTableStr;

if s1 > 0 then

select count(1) into s2

from dba_tables

where owner = as6schema

and table_name = as6Tname;

if s2 > 0 then -- should be removed but not

asTraceCode := 102;

goto compare_result;

else

asTraceCode := 101;

goto compare_result;

end if;

asTempSQL := 'select count(1) from dba_tables where owner = '''||as6schema||''' and table_name = '''||as6Tname||'''';

execute immediate asTempSQL into as6tableInd;

if as6tableInd = 1 then

--################################################

-- 2. Get measurement name

--################################################

begin

if asDataCat = 'RAW' then

select upper(mty.id)

into asMeasName

from pm_cmp_model.db_table dt

, pm_cmp_model.measurement_table mt

, pm_cmp_model.measurement_type mty

where dt.oid = mt.dbtable_pk

and dt.db_schema = as6schema

and dt.id = as6Tname

and mty.oid = mt.measurement_type_pk

and mty.adaptation_release = asAdapRel;

else

select upper(mty.id)

into asMeasName

from pm_cmp_model.db_table dt

, pm_cmp_model.aggregate_table agt

, pm_cmp_model.measurement_type_ref mtr

, pm_cmp_model.measurement_type mty

where dt.db_schema = as6schema

and dt.id = as6Tname

and agt.dbtable_pk = dt.oid

and mtr.aggregate_pk = agt.aggregate_pk

and mty.oid = mtr.agg_meas_type_pk

and mty.adaptation_release = asAdapRel;

end if;

exception

when no_data_found then

asTraceCode := 5;

goto compare_result;

end;

asRuntimeTrace := '';

asTraceCode := 10;

asSeek5IdPos := 0;

as1st6Dim := 'Y';

if asBHind = 'Y' then

as5TimeStr := asTableStr||'.period_start_time + '||asTableStr||'.BUSY_HOUR/24 stime';

else

as5TimeStr := asTableStr||'.period_start_time stime';

end if;

as5FromStr := as5schema||'.'||as5Tname||'@oss5 '||asTableStr||', uma.utp_common_objects@oss5 uco';

as5WhereStr := 'uco.co_gid = '||asTableStr||'.'||asKeyIDCat||'_id';

if asIDSpecify = 'Y' then

as5WhereStr := as5WhereStr||'

and uco.co_object_instance = '''||asKeyIDValue||'''';

end if;

as6TimeStr := asTableStr||'.start_time stime';

as6FromStr := as6schema||'.'||as6Tname||' '||asTableStr;

as6WhereStr :='';

asSelectAliasNo := 0;

as5CounterStr := '';

as6CounterStr := '';

-------------------------------------------------------

-- 1. Topo ID mapping

-------------------------------------------------------

if asDataCat = 'RAW' then

asIDMappingSQL :='select upper(d.id)||''_CO_GID'' topo_column_name, dto.id

from pm_cmp_model.db_table dt

, pm_cmp_model.measurement_table mt

, pm_cmp_model.MEASURED_TARGET mtg

, commodel.COM_ANNOTATION ca

, commodel.COM_ANNOTATIONELEMENT cae

, pm_cmp_model.dimension d

, pm_cmp_model.dim_join_descriptor dd

, pm_cmp_model.db_table dto

, pm_cmp_model.measurement_type mty

where dt.id = '''||as6Tname||'''

and mt.dbtable_pk = dt.oid

and mtg.measurement_type_pk = mt.measurement_type_pk

and ca.ann_fk_modelelement = mtg.oid

and cae.ael_fk_annotation = ca.ann_oid

and d.oid = mtg.dimension_pk

and dd.db_table_pk = dt.oid

and dd.dimension_pk = d.oid

and dto.oid = dd.object_class_table_pk

and mty.oid = mtg.measurement_type_pk

and mty.adaptation_release = '''||asAdapRel||'''

order by cae.AEL_VALUE';

elsif asDataCat = 'AGG' then

asIDMappingSQL :='select upper(d.id)||''_CO_GID'' topo_column_name, dto.id

from pm_cmp_model.db_table dt

, pm_cmp_model.aggregate_table agt

, pm_cmp_model.measurement_type_ref mtr

, pm_cmp_model.measured_target mtg

, commodel.com_annotation ca

, commodel.com_annotationelement cae

, pm_cmp_model.dimension d

, pm_cmp_model.dim_join_descriptor dd

, pm_cmp_model.db_table dto

, pm_cmp_model.measurement_type mty

where dt.id = '''||as6Tname||'''

and agt.dbtable_pk = dt.oid

and mtr.aggregate_pk = agt.aggregate_pk

and mtg.measurement_type_pk = mtr.agg_meas_type_pk

and ca.ann_fk_modelelement = mtg.oid

and cae.ael_fk_annotation = ca.ann_oid

and d.oid = mtg.dimension_pk

and dd.db_table_pk = dt.oid

and dd.dimension_pk = d.oid

and dto.oid = dd.object_class_table_pk

and mty.oid = mtg.measurement_type_pk

and mty.adaptation_release = '''||asAdapRel||'''

order by cae.AEL_VALUE';

end if;

open oss6IDCur for asIDMappingSQL;

loop

fetch oss6IDCur into as6IDCol, as6LWTable;

exit when oss6IDCur%notfound;

asTraceCode := 20;

as5TopoID := '';

asIdDeepInd := 1;

as6TopoID := '';

asSelectAliasNo := asSelectAliasNo + 1;

if as6LWTable = 'MOF_COMMON_OBJECTS' then

as5TopoID := 'REGEXP_REPLACE(substr(uco.co_dn,instr(uco.co_dn,''/'')+1),''[A-Za-z_]{1,}'',''*'')';

-- ....... [A-Z]{1,}...... UNIT_TYPE-941 ......ADMSS.UNIT_TYPE...........

as6TopoID := 'REGEXP_REPLACE(substr(mco.co_dn,instr(mco.co_dn,''/'')+1),''[A-Za-z_]{1,}'',''*'')';

as6FromStr := as6FromStr||', '||asMOSchema||'.'||as6LWTable||' mco';

as6WhereStr := asTableStr||'.'||as6IdCol||' = '||'mco.co_gid';

if asIDSpecify = 'Y' then

as6WhereStr := as6WhereStr||'

and mco.co_dn like ''PLMN%/'||asKeyIDCat||'-'||asKeyIDValue||'%'''; --and mco.co_object_instance = '''||asKeyIDValue||'''';

end if;

as1st6Dim := 'N';

asMeasDimType := 'MANAGED';

--. MOF_COMMON_OBJECTS ... NETWORK_ELEMENT_CO_GID

asSeekIDSQL := 'select '||asTableStr||'.network_element_co_gid

from '||as6schema||'.'||as6Tname||' '||asTableStr||', mof.mof_common_objects mco

where '||asTableStr||'.network_element_co_gid = mco.co_gid

--and mco.co_ocv_sys_version = '''||asAdapRel||'''

and mco.co_dn like ''PLMN%/'||asKeyIDCat||'-'||asKeyIDValue||'%''

and rownum =1';

begin

execute immediate asSeekIDSQL into asSampleDataID;

--asTempSQL.........topo.....MCO.....TO.........'PLMN-*/'

asTempSQL := 'select length(co_dn) - length(regexp_replace(co_dn,''[-]{1,}'',''''))

from (

select substr(mco.co_dn,instr(mco.co_dn,''/'')+1) co_dn

from mof.mof_common_objects mco

where mco.co_gid = '||asSampleDataID||'

and rownum = 1

)';

asTraceCode := 30;

exception

when no_data_found then

asTraceCode := 21;

asRuntimeTrace := asSeekIDSQL;

exit; -- quit loop

end;

else

--------------------------------------------------------------------------------

if as1st6Dim = 'Y' then

as6TopoID := 'REGEXP_REPLACE(substr('||as6LWTable||'.co_dn,instr('||as6LWTable||'.co_dn,''/'')+1),''[A-Za-z_]{1,}'',''*'')';

as1st6Dim := 'N';

--to_do: . XXX.... ID

--SQLD12

asSeekIDSQL := 'select '||asTableStr||'.network_element_co_gid

from '||as6schema||'.'||as6Tname||' '||asTableStr||', '||asLWOSchema||'.'||as6LWTable||' '||as6LWTable||'

where '||asTableStr||'.'||as6IdCol||' = '||as6LWTable||'.co_gid

and '||as6LWTable||'.co_dn like ''%'||asKeyIDCat||'-'||asKeyIDValue||'%''

and rownum = 1';

begin

execute immediate asSeekIDSQL into asSampleDataID;

--SQLD13

asTempSQL := 'select length(co_dn) - length(regexp_replace(co_dn,''[-]{1,}'',''''))

from (

select substr('||as6LWTable||'.co_dn,instr('||as6LWTable||'.co_dn,''/'')+1) co_dn

FROM '||as6schema||'.'||as6Tname||' '||asTableStr||', '||asLWOSchema||'.'||as6LWTable||' '||as6LWTable||'

where '||asTableStr||'.'||as6IdCol||' = '||as6LWTable||'.co_gid

and '||asTableStr||'.NETWORK_ELEMENT_CO_GID = '||asSampleDataID||'

and rownum = 1

)';

asTraceCode := 30;

exception

when no_data_found then

asTraceCode := 21;

asRuntimeTrace := asSeekIDSQL;

exit; -- quit loop

end;

else

as6TopoID := 'REGEXP_REPLACE('||as6LWTable||'.co_dn,''[A-Za-z_]{1,}'',''*'')';

--SQLD13

asTempSQL := 'select length(co_dn) - length(regexp_replace(co_dn,''[-]{1,}'',''''))

from (

select '||as6LWTable||'.co_dn

FROM '||as6schema||'.'||as6Tname||' '||asTableStr||', '||asLWOSchema||'.'||as6LWTable||' '||as6LWTable||'

where '||asTableStr||'.'||as6IdCol||' = '||as6LWTable||'.co_gid

and '||asTableStr||'.NETWORK_ELEMENT_CO_GID = '||asSampleDataID||'

and rownum = 1

)';

end if;

as6FromStr := as6FromStr||', '||asLWOSchema||'.'||as6LWTable||' '||as6LWTable;

if as6WhereStr is null then

as6WhereStr := asTableStr||'.'||as6IdCol||' = '||as6LWTable||'.co_gid';

if asIDSpecify = 'Y' then

as6WhereStr := as6WhereStr||'

and '||as6LWTable||'.co_dn like ''%'||asKeyIDCat||'-'||asKeyIDValue||'%''';

end if;

else

as6WhereStr := as6WhereStr||'

and '||asTableStr||'.'||as6IdCol||' = '||as6LWTable||'.co_gid';

end if;

begin

execute immediate asTempSQL into asIdDeepInd;

exception

when no_data_found then

asTraceCode := 22;

asRuntimeTrace := asTempSQL;

exit; -- quit loop

end;

--++++++++++++++++++++++++++++++++++++++++++++

if asIdDeepInd >= 1 then -- asIdDeepInd >= 1 ...column..mapping....

for j in 1..asIdDeepInd loop

-- dbms_output.put_line('asIdDeepInd = '||asIdDeepInd);

--SQLS21

begin

select column_name

into as5IdCol

from dba_tab_columns@oss5

where owner = as5schema

and table_name = as5Tname

and nullable = 'N'

and column_name != 'PERIOD_START_TIME'

and column_id = asSeek5IdPos + j;

if as5IdCol = asKeyIDCat||'_ID' then

as5TopoID:= 'REGEXP_REPLACE(substr(uco.co_dn,instr(uco.co_dn,''/'')+1),''[A-Za-z_]{1,}'',''*'')';

elsif as5TopoID is not null then

as5TopoID := as5TopoID||'||''/*-''||'||asTableStr||'.'||as5IdCol;

else

as5TopoID := '''*-''||'||asTableStr||'.'||as5IdCol;

end if;

exception

when no_data_found then

asTraceCode := 24;

asRuntimeTrace := 'select column_name

from dba_tab_columns@oss5

where owner = '''||as5schema||'''

and table_name = '''||as5Tname||'''

and nullable = ''N''

and column_name != ''PERIOD_START_TIME''

and column_id = '||asSeek5IdPos||' + '||j||';';

exit;

end;

end loop;

else

begin

select column_name

into as5IdCol

from dba_tab_columns@oss5

where owner = as5schema

and table_name = as5Tname

and nullable = 'N'

and column_name like '%'||asTname5str||'%';

if as5IdCol = asKeyIDCat||'_ID' then

as5TopoID:= 'REGEXP_REPLACE(substr(uco.co_dn,instr(uco.co_dn,''/'')+1),''[A-Za-z_]{1,}'',''*'')';

elsif as5TopoID is not null then

as5TopoID := as5TopoID||'||''/*-''||'||asTableStr||'.'||as5IdCol;

else

as5TopoID := '''*-''||'||asTableStr||'.'||as5IdCol;

end if;

exception

when no_data_found then

asTraceCode := 25;

asRuntimeTrace := 'select column_name

into as5IdCol

from dba_tab_columns@oss5

where owner = '''||as5schema||'''

and table_name = '''||as5Tname||'''

and nullable = ''N''

and column_name like ''%'||asTname5str||'%'';';

exit;

when others then

asTraceCode := 26;

asRuntimeTrace := sqlerrm;

exit;

end;

end if;

if asTraceCode = 20 then -- no exception

asTraceCode := 30;

end if;

asSeek5IdPos := asSeek5IdPos + asIdDeepInd;

as5TopoID := as5TopoID||' C'||asSelectAliasNo;

as6TopoID := as6TopoID||' F'||asSelectAliasNo;

asDiffSqlOrder := asDiffSqlOrder||' C'||asSelectAliasNo||',';

if as5TopoStr is null then

as5TopoStr := as5TopoID;

else

as5TopoStr := as5TopoStr||'

, '||as5TopoID;

end if;

if as6TopoStr is null then

as6TopoStr := as6TopoID;

else

as6TopoStr := as6TopoStr||'

, '||as6TopoID;

end if;

--<< end match topo_id of OSS5

end loop;

--++++++++++++++++++++++++++++++++++++++++++++

end if;

-------------------------------------------------------

-- 2. Counter mapping

-------------------------------------------------------

if asTraceCode >= 30 then

-- loop counter columns assembly in OSS6

for tb6 in (

select column_name --, column_id, substr(column_name,1,27) col_str

, sign(length(column_name)-27) isoverlong

, instr(column_name,'_PERIOD_DURATION_') pd_ind

, substr(column_name,1,instr(column_name,'_DURATION')-1) dur_prestr

, decode ( asDataCat

,'RAW', decode(column_name,'DURATION','Y','N')

--,'AGG', decode(substr(column_name,length(column_name)-8),'_DURATION','Y','N')

-- 2010-08-26 ADMSS_DIAMSC_DESTNAME_DAY: DIAMSC_DURATION, PRI_UNA_DURATION, SEC_UNA_DURATION

,'AGG', decode(column_name, asMeasName||'_DURATION','Y','N')

) dur_ind

from dba_tab_columns

where owner = as6schema

and table_name = as6Tname

and nullable = 'Y'

and column_name not like 'BHCRITERIA/_%' escape '/'

--- 2010-07-05

and column_name not like '%WEIGHT'

order by column_id

) loop

if tb6.dur_ind = 'Y' then

if compareDuration = 'Y' then

as6CounterStr := as6CounterStr||','||tb6.column_name;

as5CounterStr := as5CounterStr||',PERIOD_DURATION';

end if;

asTraceCode := 50;

else

begin

if tb6.isoverlong = 1 then

-- length > 27

select upper(mip.id)

into as5Counter

from pm_cmp_model.db_table dt

,pm_cmp_model.indicator_column ic

,pmmodel.meas_indicator_pr01 mip

where ic.db_table_pk = dt.oid

and ic.indicator_pk = mip.oid

and dt.id = as6Tname

and ic.column_name = tb6.column_name;

elsif tb6.pd_ind = 0 then -- not XXX_PERIOD_DURATION_XXX column

as5Counter := tb6.column_name;

else

-- Scenario: ADHLR_EIRH_HLR_DAY.EIRH_PERIOD_DURATION_EIRH

-- it is not overlong but it's a logical counter, still except to no_data_found exception

select 'null'

into as5Counter

from dual

where 1>1;

end if;

-- verify if this column exists in OSS5

select count(1) into s1

from dba_tab_columns@oss5

where owner = as5schema

and table_name = as5Tname

and column_name = as5Counter;

if s1 = 1 then

----- Further counter column testing here

if upper(piDebugNull) = 'Y' then

-- asTempSQL := 'select count(1) from '||as6schema||'.'||as6Tname||' where '||tb6.column_name||' is null';

asTempSQL := 'select count(1) from '||as5schema||'.'||as5Tname||'@oss5 '||asTableStr||', uma.utp_common_objects@oss5 uco

where uco.co_gid = '||asTableStr||'.'||asKeyIDCat||'_id

and uco.co_object_instance = '''||asKeyIDValue||'''

and '||asTableStr||'.'||as5Counter||' is null';

execute immediate asTempSQL into s2;

if s2 > 0 then

logf := UTL_FILE.fopen (log_dir, other_log, 'A');

UTL_FILE.put_line (logf, as5schema||'.'||as5Tname||'.'||as5Counter||' > '||as6schema||'.'||as6Tname||'.'||tb6.column_name||' : '||s2);

UTL_FILE.fclose (logf);

goto next_column;

end if;

as5CounterStr := as5CounterStr||','||as5Counter;

as6CounterStr := as6CounterStr||','||tb6.column_name;

----- End of Further counter column testing

else

asTraceCode := 41;

asRuntimeTrace := as6Tname||'.'||tb6.column_name||' <--> '||as5Tname||'.'||as5Counter||'

Counter Column '||as5Counter||' not found in OSS5:

select *

from dba_tab_columns@oss5

where owner = '''||as5schema||'''

and table_name = '''||as5Tname||'''

and column_name = '''||as5Counter||'''

goto assembly;

end if;

exception

WHEN NO_DATA_FOUND THEN

-- todo: Logical counter !!!!!

begin

-- exact match

select column_name

into asLogicalCtr

from dba_tab_columns@oss5

where owner = as5schema

and table_name = as5Tname

and column_name = tb6.column_name;

as5CounterStr := as5CounterStr||','||asLogicalCtr;

as6CounterStr := as6CounterStr||','||tb6.column_name;

asTraceCode := 50;

exception

WHEN NO_DATA_FOUND THEN

begin

-- partial match

-- SQLD43

-- tough scenario:

-- ACHHLR_PERIOD_DURATION > ACHHLR_PERIOD_DURATION_ACHH_00 ...5...6..

-- RNS_P_EIRI_HLR_DAY has two columns: REQUESTS_THIRD_PARTY_LISTING and REQUESTS

-- ADMGW_PMWH.ADMGW_MTPMST_DSPC_DAYBH: MTPSLU_PERIOD_DURATION_MTPSLU, MTPSLU_PERIOD_DURATION

select column_name

into asLogicalCtr

from (

select column_name, length(column_name) len

from dba_tab_columns@oss5

where owner = as5schema

and table_name = as5Tname

and instr(tb6.column_name,column_name) != 0

and column_name != 'PERIOD_DURATION'

order by length(column_name) desc

)

where rownum = 1;

as5CounterStr := as5CounterStr||','||asLogicalCtr;

as6CounterStr := as6CounterStr||','||tb6.column_name;

asTraceCode := 50;

exception

WHEN NO_DATA_FOUND THEN

logf := UTL_FILE.fopen (log_dir, other_log, 'A');

UTL_FILE.put_line (logf, 'Logical counter mapping error: '||as6schema||'.'||as6Tname||'.'||tb6.column_name);

UTL_FILE.fclose (logf);

asTraceCode := 40;

asRuntimeTrace := as6schema||'.'||as6Tname||'.'||tb6.column_name||': no metadata found for counter mapping.

select upper(mip.id) oss5_col

into as5Counter

from pm_cmp_model.db_table dt

,pm_cmp_model.indicator_column ic

,pmmodel.meas_indicator_pr01 mip

where ic.db_table_pk = dt.oid

and ic.indicator_pk = mip.oid

and dt.id = '''||as6Tname||'''

and ic.column_name = '''||tb6.column_name||'''';

end;

when others then

asTraceCode := 35;

s1:=length(as5CounterStr);

s2:=length(as6CounterStr);

asRuntimeTrace := 'Error occurred when find OSS5 table column mapped to '||as6Tname||'.'||tb6.column_name||'

'||sqlerrm||'

as5CounterStr length: '||s1||'

as6CounterStr length: '||s2||'

exit;

end;

end if;

<<next_column>>

asTraceCode := 50;

end loop;

-- end loop counter columns assembly in OSS6

<<assembly>>

if asTraceCode = 50 then

if asIgnoreTopo = 'Y' then

asSQL5 := 'SELECT '||as5TimeStr||'

'||as5CounterStr||'

FROM '||as5FromStr||'

WHERE '||as5WhereStr;

else

asSQL5 := 'SELECT '||as5TimeStr||'

,'||as5TopoStr||'

'||as5CounterStr||'

FROM '||as5FromStr||'

WHERE '||as5WhereStr;

end if;

asSQL5Cnt := 'select count(1)

from '||as5FromStr||'

WHERE '||as5WhereStr;

if asIgnoreTopo = 'Y' then

asSQL6 := 'SELECT '||as6TimeStr||'

'||as6CounterStr||'

FROM '||as6FromStr||'

WHERE '||as6WhereStr;

else

asSQL6 := 'SELECT '||as6TimeStr||'

,'||as6TopoStr||'

'||as6CounterStr||'

FROM '||as6FromStr||'

WHERE '||as6WhereStr;

end if;

asSQL6Cnt := 'select count(1)

from '||as6FromStr||'

WHERE '||as6WhereStr;

if asCompareMode = '5-6' then

asCompareSQL := asSQL5||'

MINUS'||'

'||asSQL6;

elsif asCompareMode = '6-5' then

asCompareSQL := asSQL6||'

MINUS'||'

'||asSQL5;

end if;

asCompareSQLSum := 'select count(1) from ('||asCompareSQL||')';

s1:= length(asCompareSQLSum);

begin

execute immediate asSQL5Cnt into as5RowsCnt;

execute immediate asSQL6Cnt into as6RowsCnt;

if as5RowsCnt = 0 and as6RowsCnt = 0 then

asTraceCode := 61; -- No data in BOTH tables

asRuntimeTrace:= asSQL5Cnt||'

'||asSQL6Cnt;

elsif (as5RowsCnt = 0 and as6RowsCnt > 0) then

asTraceCode := 63; -- No data in OSS5 table

asRuntimeTrace:= asSQL5Cnt;

elsif (as6RowsCnt = 0 and as5RowsCnt > 0) then

asTraceCode := 64; -- No data in OSS6 table

asRuntimeTrace:= asSQL6Cnt;

else

-- both tables have data

execute immediate asCompareSQLSum into asDiffNum;

if asDiffNum = 0 then

if as5RowsCnt = as6RowsCnt then

asTraceCode := 100; -- Pass!

else

asTraceCode := 99; -- Pass with caution

end if;

if piTraceLevel = 2 then

asRuntimeTrace:= asCompareSQL;

end if;

else

asTraceCode := 72; -- Data different

asRuntimeTrace:= asCompareSQL;

if as5RowsCnt = as6RowsCnt then

---------------------------------------------

-- Is it a known issue?

---------------------------------------------

if asHandleKnownIssue = 'Y' then

begin

select exp_sum

into asKnownIssueSum

from TEMP_DACOT_EXCP_TABLE

where table_name = as6Tname

and cat = asKeyIDCat;

-- if data found, then it is a known issue, skip further analyzing

asTraceCode := 175;

goto compare_result;

exception

when no_data_found then

goto go_on_compare;

end;

end if;

<<go_on_compare>>

---------------------------------------------------------------

-- To do: findout which counter is different

---------------------------------------------------------------

diffInd := 'N';

asDiffInfo := '';

as5DiffCnt := '';

as6DiffCnt := '';

-- Time Compare

asTempSQL := '

select rownum, stime

from (

select '||as5TimeStr||'

from '||as5FromStr||'

where '||as5WhereStr||'

order by stime

)

minus

select rownum, stime

from (

select '||as6TimeStr||'

from '||as6FromStr||'

where '||as6WhereStr||'

order by stime

)

asTempSQL2 := 'select count(1) from ('||asTempSQL||')';

execute immediate asTempSQL2 into s1;

if s1 > 0 then

asDiffInfo := 'START_TIME is different!';

asDiffDetSQL := asTempSQL;

goto compare_result;

end if;

-- Topo ID Compare

if asIgnoreTopo != 'Y' or asIgnoreTopo is null then

asTempSQL := '

select '||as5TopoStr||'

from '||as5FromStr||'

where '||as5WhereStr||'

minus

select '||as6TopoStr||'

from '||as6FromStr||'

where '||as6WhereStr||'

asTempSQL2 := 'select count(1) from ('||asTempSQL||')';

execute immediate asTempSQL2 into s1;

if s1 > 0 then

asDiffInfo := 'Topology data is different!';

asDiffDetSQL := asTempSQL;

goto compare_result;

end if;

-- Counter compare

as5LoopCt := as5CounterStr;

as6LoopCt := as6CounterStr;

asDiffInfo := 'Different Counters :';

s1 := 0;

loop

if instr(as5LoopCt,',',2) > 0 then

as5CpCounter := substr(as5LoopCt,2,instr(as5LoopCt,',',2)-2);

as5LoopCt := substr(as5LoopCt,instr(as5LoopCt,',',2));

as6CpCounter := substr(as6LoopCt,2,instr(as6LoopCt,',',2)-2);

as6LoopCt := substr(as6LoopCt,instr(as6LoopCt,',',2));

else

as5CpCounter := substr(as5LoopCt,2);

as5LoopCt := '';

as6CpCounter := substr(as6LoopCt,2);

as6LoopCt := '';

end if;

asDiffCounter := as5CpCounter;

--detect data type

select data_type

into asCntDatatype

from dba_tab_columns

where owner = as6schema

and table_name = as6Tname

and column_name = as6CpCounter;

if piRoundFix > 0 then

if asCntDatatype = 'NUMBER' then

as5CpCounter := 'round('||as5CpCounter||','||piRoundFix||')';

as6CpCounter := 'round('||as6CpCounter||','||piRoundFix||')';

end if;

as5CntSQL := 'select '||as5CpCounter||'

from '||as5FromStr||'

where '||as5WhereStr;

as6CntSQL := 'select '||as6CpCounter||'

from '||as6FromStr||'

where '||as6WhereStr;

if asCompareMode = '5-6' then

asTempSQL := as5CntSQL||'

minus

'||as6CntSQL;

elsif asCompareMode = '6-5' then

asTempSQL := as6CntSQL||'

minus

'||as5CntSQL;

end if;

asTempSQL2 := 'select count(1) from ('||asTempSQL||')';

begin

execute immediate asTempSQL2 into s2;

if s2 > 0 then

diffInd := 'Y';

as5DiffCnt := as5DiffCnt||','||as5CpCounter;

as6DiffCnt := as6DiffCnt||','||as6CpCounter;

if s1 = 0 then

asDiffInfo := asDiffInfo||asDiffCounter;

else

asDiffInfo := asDiffInfo||'

'||asDiffCounter;

end if;

s1 := s1 + 1;

end if;

exception

WHEN OTHERS THEN

asTraceCode := 97; --Counter compare error!!

asRuntimeTrace:= sqlerrm||'

'||asTempSQL;

end;

if as5LoopCt is null then

exit;

end if;

end loop;

if diffInd = 'Y' then

asDiffDetSQL := '

select *

from (

select 5 cat, '||as5TimeStr||'

,'||as5TopoStr||'

'||as5DiffCnt||'

from '||as5FromStr||'

where '||as5WhereStr||'

union all

select 6 cat, '||as6TimeStr||'

,'||as6TopoStr||'

'||as6DiffCnt||'

from '||as6FromStr||'

where '||as6WhereStr||'

)

'||asDiffSqlOrder||' stime,cat

else

asTraceCode := 100;

if piTraceLevel = 2 then

asRuntimeTrace:= asCompareSQL;

else

asRuntimeTrace:= '';

end if;

else

-- Number of rows different

asTraceCode := 75;

end if;

-- End: findout which counter is different

end if;

exception

WHEN OTHERS THEN

asTraceCode := 55; --Compare SQL error

asRuntimeTrace:= sqlerrm||'

'||asCompareSQL;

end;

end if;

<<compare_result>>

---------------------------------------------------------------

-- Message processing in Table loop

---------------------------------------------------------------

if asTraceCode = 1 then

tempStrSum := '[ERR] Table not found in OSS6';

elsif asTraceCode = 5 then

tempStrSum := '[ERR] Measurement name not found';

elsif asTraceCode = 10 then

tempStrSum := '[NOK] Topology Metadata error';

elsif asTraceCode = 21 then

tempStrSum := '[NOK] Sample GID not found';

elsif asTraceCode = 22 then

tempStrSum := '[NOK] Topology data not found';

elsif asTraceCode = 24 then

tempStrSum := '[ERR] Different topo relationship';

elsif asTraceCode = 26 then

tempStrSum := '[ERR] Exception when validate OSS5 column';

elsif asTraceCode = 35 then

tempStrSum := '[ERR] Unknown error when seek column';

elsif asTraceCode = 40 then

tempStrSum := '[ERR] Counter mapping not found';

elsif asTraceCode = 41 then

tempStrSum := '[ERR] Expect counter column not found in oss5';

elsif asTraceCode = 55 then

tempStrSum := '[ERR] Compare SQL error';

elsif asTraceCode = 61 then

tempStrSum := '[NOK] Both tables are empty';

elsif asTraceCode = 63 then

tempStrSum := '[NOK] OSS5 table is empty';

elsif asTraceCode = 64 then

tempStrSum := '[NOK] OSS6 table is empty';

elsif asTraceCode = 72 then

tempStrSum := '[NOK] Data different';

elsif asTraceCode = 75 then

tempStrSum := '[NOK] Number of rows different';

elsif asTraceCode = 99 then

tempStrSum := '[OK] Passed but num of rows different';

elsif asTraceCode = 100 then

tempStrSum := '[OK] Passed';

elsif asTraceCode = 101 then

tempStrSum := '[OK] Table removed in OSS6';

elsif asTraceCode = 102 then

tempStrSum := '[NOK] Table should be removed but still exists';

elsif asTraceCode = 175 then

tempStrSum := '[OK] Known issues';

end if;

-------------------------------------------------------------------------------

-- Generate Statistics

-------------------------------------------------------------------------------

begin

asTempSQL := 'select resultCode from temp_dacot_sum where resultCode = '||asTraceCode;

execute immediate asTempSQL into asTargetCode;

asTempSQL := 'update temp_dacot_sum set sumCnt = sumCnt+1 where resultCode = '||asTargetCode;

execute immediate asTempSQL;

commit;

exception

when no_data_found then

asTempSQL := 'insert into temp_dacot_sum(resultCode,resultDesc,sumCnt) values ('||asTraceCode||','''||tempStrSum||''',1)';

execute immediate asTempSQL;

commit;

end;

if asHandleKnownIssue = 'Y' and asTraceCode = 175 then

asTempSQL := 'insert into temp_dacot_det(resultCode,schema5,tableName5,schema6,tableName6,tableCat)

values ('||asTraceCode||','''||as5schema||''','''||as5Tname||''','''||as6schema||''','''||as6Tname||''','''||asKnownIssueSum||''')';

else

asTempSQL := 'insert into temp_dacot_det(resultCode,schema5,tableName5,schema6,tableName6,tableCat)

values ('||asTraceCode||','''||as5schema||''','''||as5Tname||''','''||as6schema||''','''||as6Tname||''','''||asTableCat||''')';

end if;

execute immediate asTempSQL;

commit;

if asTraceCode < 60 then

tempStr := as5schema||RPAD('.'||as5Tname, 33,' ')||'> '||as6schema||RPAD('.'||as6Tname, 33,' ')||'> '||tempStrSum;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, tempStr);

UTL_FILE.fclose (logf);

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf,'');

UTL_FILE.put_line (logf, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||' : '||as5schema||'.'||as5Tname||' > '||as6schema||'.'||as6Tname||' ('||asMeasName||')

(Code:'||asTraceCode||') '||tempStrSum);

UTL_FILE.put_line (logf, asRuntimeTrace);

UTL_FILE.put_line (logf,'');

UTL_FILE.fclose (logf);

elsif asTraceCode <= 100 then

-- tempStr := as5schema||RPAD('.'||as5Tname, 33,' ')||'> '||as6schema||RPAD('.'||as6Tname, 33,' ')||tempStrSum;

tempStr := as5schema||RPAD('.'||as5Tname||'('||as5RowsCnt||')', 33,' ')||'> '||as6schema||RPAD('.'||as6Tname||'('||as6RowsCnt||')', 33,' ')||'> '||tempStrSum;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, tempStr);

UTL_FILE.fclose (logf);

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf,'');

UTL_FILE.put_line (logf, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||' : '||as5schema||'.'||as5Tname||' > '||as6schema||'.'||as6Tname||' ('||asMeasName||')

(Code:'||asTraceCode||') '||tempStrSum);

if asTraceCode = 72 then

UTL_FILE.put_line (logf,asDiffInfo);

UTL_FILE.put_line (logf,asDiffDetSQL);

end if;

UTL_FILE.put_line (logf, asRuntimeTrace);

UTL_FILE.fclose (logf);

elsif asTraceCode = 101 then

-- 101, table removed

tempStr := as5schema||RPAD('.'||as5Tname, 33,' ')||'> '||RPAD('', 43,' ')||'> '||tempStrSum;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, tempStr);

UTL_FILE.fclose (logf);

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf,'');

UTL_FILE.put_line (logf, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||' : '||as5schema||'.'||as5Tname||'

(Code:'||asTraceCode||') '||tempStrSum);

UTL_FILE.put_line (logf,'');

UTL_FILE.fclose (logf);

elsif asTraceCode = 175 then

-- 175, known issues

tempStr := as5schema||RPAD('.'||as5Tname, 33,' ')||'> '||RPAD('', 43,' ')||'> '||tempStrSum||': '||asKnownIssueSum;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, tempStr);

UTL_FILE.fclose (logf);

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf,'');

UTL_FILE.put_line (logf, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||' : '||as5schema||'.'||as5Tname||'

(Code:'||asTraceCode||') '||tempStrSum||': '||asKnownIssueSum);

UTL_FILE.put_line (logf,'');

UTL_FILE.fclose (logf);

end if;

end loop; -- Table loop end

---------------------------------------------------------------------------------------------------

-- Message processing out of Table loop

if asTraceCode = 0 then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, 'No table found in OSS5 of Schema '||as5schema);

UTL_FILE.fclose (logf);

end if;

-- >>> Print Result Statistics

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '---- Result Statistics ----

');

UTL_FILE.fclose (logf);

asTempSQL := 'select resultCode,resultDesc, sumCnt, to_char(round(sumCnt/total,3)*100,''FM990.0'')||''%'' pct

from (

select resultCode,resultDesc,sumCnt, sum(sumcnt) over (partition by 1) total

from temp_dacot_sum

)

order by resultCode';

open sumCur for asTempSQL;

loop

fetch sumCur into asResultCode, asResultDesc, asSumCnt, asPct;

exit when sumCur%notfound;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '(Code:'||asResultCode||') '||asResultDesc||' ['||asSumCnt||'] '||asPct);

UTL_FILE.fclose (logf);

asTempSQL2 := 'select schema5,tableName5,schema6,tableName6,tablecat

from temp_dacot_det

where resultCode = '||asResultCode||'

order by tableName5';

open detCur for asTempSQL2;

loop

fetch detCur into as5schema, as5Tname, as6schema, as6Tname, asExpCat ;

exit when detCur%notfound;

if asResultCode = 175 then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, as5schema||'.'||as5Tname||' > '||as6schema||'.'||as6Tname||' > '||asExpCat);

UTL_FILE.fclose (logf);

else

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, as5schema||'.'||as5Tname||' > '||as6schema||'.'||as6Tname);

UTL_FILE.fclose (logf);

end if;

end loop;

close sumCur;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '---- Statistics Summary ----

');

UTL_FILE.fclose (logf);

asTempSQL := 'select resultCode,resultDesc, sumCnt, to_char(round(sumCnt/total,3)*100,''FM990.0'')||''%'' pct

from (

select resultCode,resultDesc,sumCnt, sum(sumcnt) over (partition by 1) total

from temp_dacot_sum

)

order by resultCode';

open sumCur for asTempSQL;

loop

fetch sumCur into asResultCode, asResultDesc, asSumCnt, asPct;

exit when sumCur%notfound;

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '(Code:'||asResultCode||') '||asResultDesc||' ['||asSumCnt||'] '||asPct);

UTL_FILE.fclose (logf);

asTempSQL3 := 'select tablecat, num, to_char(round(num/'||asSumCnt||',3)*100,''FM990.0'')||''%'' pct

from (

select tablecat, count(1) num

from temp_dacot_det

where resultCode='||asResultCode||'

group by tablecat

order by tablecat

)';

open detCur for asTempSQL3;

loop

fetch detCur into asTcat, asTnum, asTpct;

exit when detCur%notfound;

if asResultCode = 175 then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, ' '||asTcat||':'||LPAD(asTnum,4,' ')||' tables '||LPAD(asTpct,6,' '));

UTL_FILE.fclose (logf);

else

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, ' '||RPAD(asTcat,7,' ')||':'||LPAD(asTnum,4,' ')||' tables '||LPAD(asTpct,6,' '));

UTL_FILE.fclose (logf);

end if;

end loop;

close sumCur;

-- <<<<<

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '########## Dacot report end ##########');

UTL_FILE.fclose (logf);

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '########## Dacot report end ##########');

UTL_FILE.fclose (logf);

<<tail>>

if asCompareMode = 'DEBUG' then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, asTableLoopSQL);

UTL_FILE.fclose (logf);

end if;

exception

when INVALID_PARAMETER then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, 'Invalid parameter! Program abort!');

UTL_FILE.fclose (logf);

when CR_TEMP_T_ERR then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, 'Can not create temp table! Program abort!

');

UTL_FILE.put_line (logf, asRuntimeTrace);

UTL_FILE.fclose (logf);

when RUNTIME_ERR then

logf := UTL_FILE.fopen (log_dir, detail_log, 'A',32767);

UTL_FILE.put_line (logf,'');

UTL_FILE.put_line (logf, TO_CHAR(SYSDATE,'YYYYMMDD_HH24_MI_SS')||': [NOK] Runtime Error');

UTL_FILE.put_line (logf, asRuntimeTrace);

UTL_FILE.put_line (logf,'');

-- UTL_FILE.put_line (logf, sqlerrm);

UTL_FILE.fclose (logf);

when others then

logf := UTL_FILE.fopen (log_dir, summary_log, 'A');

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, sqlerrm);

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, as5schema||'.'||as5Tname||' > '||as6schema||'.'||as6Tname);

UTL_FILE.put_line (logf, '');

UTL_FILE.put_line (logf, '########## Dacot report end with exception ##########');

UTL_FILE.fclose (logf);

End;