--Project:SCM项目二期-----------------------------------------------------------------------------------
--Author:is-null@sohu.com-------------------------------------------------------------------------------
--Version:5.0-------------------------------------------------------------------------------------------
--Created Date:2002-12-10-------------------------------------------------------------------------------
--Last Modified:2004-04-26------------------------------------------------------------------------------
--Desc:ORACLE数据库开发工具库-----------------------------------------------------------------------------
--Document:ORACLE数据库开发工具库.DOC---------------------------------------------------------------------
*******************************************************************************************************/
/*******************************************************************************************************
----ID001,V1.0 [2002-12-10]:/Complete The Package Head And Package Body/----
----ID002,V2.0 [2003-01-02]:/增加表之间数据比较函数table_comp/----
----ID003,V2.1 [2003-01-02]:/完善修改,部分过程的日志处理等。/----
----ID004,V3.0 [2003-01-09]:/增加IDB LOADER的配置初始化过程:Init_Ldr_Cons/----
----ID005,V3.1 [2003-02-27]:/增加Del_Log和Cls_Log过程/----
----ID006,V4.0 [2003-04-04]:/改进Table_Comp地执行输出/----
----ID007,V4.1 [2003-04-06]:/增加清除IDB目标表的操作,CL_OBJ_TAB:TRUNCATE所有的目标表/----
/CL_OBJ_TAB2:取得用户订制脚本,执行清除目标表的数据/----
----ID008,V4.2 [2003-04-21]:/增加Table_Comp2,用于根据IDB字典表,比较表之间的数据差异/----
----ID009,V4.4 [2003-04-21PM]:/增加Init_Ldr_Cons得重载函数,用于更新相应任务的MD_Cfg_FK_Cons/----
----ID010,V4.5 [2003-04-23PM]:/增加Exp_Reset函数,用于IDB运行异常时的配置状态表的状态恢复/----
----ID011,V4.6 [2003-05-16]:/增加CL_OBJ_TAB的重载函数，用于处理任务ID/----
----ID012,V4.7 [2003-05-16PM]:/增加CL_OBJ_TAB的重载函数(不带返回参数)，用于处理任务ID/----
----ID013,V4.8 [2003-05-18PM]:/增加IDB运行出错后,重新运行的时候,系统初始化函数/----
----ID014,V4.9 [2003-05-21]:/优化一下Init_Ldr_Cons的性能/----
----ID015,V5.0 [2004-04-26]:/全面升级,进行通用化处理/----
*******************************************************************************************************/
CREATE OR REPLACE PACKAGE Orcl_Tool AS
FUNCTION Comp_Tab_Lvl(iv_CTable VARCHAR2) RETURN NUMBER;
FUNCTION Get_PTable(iv_CTable VARCHAR2,iv_Cons_Name VARCHAR2) RETURN VARCHAR2;
PROCEDURE Get_Parent_Tab_FK(iv_CTable VARCHAR2);
PROCEDURE Get_Child_Tab_FK(iv_PTable VARCHAR2);
PROCEDURE Create_UI_Trigger(on_ExitCode OUT NUMBER);
PROCEDURE Upt_Logic_Related_TBs(iv_IDB_User VARCHAR2,iv_ADW_User VARCHAR2);
PROCEDURE Table_Comp(iv_Src_Owner VARCHAR2,iv_Dest_Owner VARCHAR2,iv_Dest_DBLink VARCHAR2,iv_Range VARCHAR2,iv_PKAble BOOLEAN,iv_Comp_Mode VARCHAR2);
PROCEDURE Table_Comp2(iv_Src_Owner VARCHAR2,iv_Dest_Owner VARCHAR2,iv_Dest_DBLink VARCHAR2,iv_Tab_Type VARCHAR2,iv_PKAble BOOLEAN,iv_Comp_Mode VARCHAR2);
PROCEDURE Del_Log(Module_Name VARCHAR2,End_Dttm DATE,Start_Dttm DATE DEFAULT TO_DATE('2000-01-01','YYYY-MM-DD'));
PROCEDURE Cls_Log(Module_Name VARCHAR2);
FUNCTION Get_Task_CL_Text(iv_Task_Name VARCHAR2,iv_CL_Text VARCHAR2) RETURN VARCHAR2;
PROCEDURE CL_Obj_Tab;
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER);
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER,iv_Task_Name VARCHAR2,iv_CL_Mode VARCHAR2 DEFAULT NULL);
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER,in_Task_ID NUMBER,iv_CL_Mode VARCHAR2 DEFAULT NULL);
PROCEDURE CL_Obj_Tab(in_Task_ID NUMBER,iv_CL_Mode VARCHAR2 DEFAULT NULL);
PROCEDURE Exp_Reset;
PROCEDURE IDB_Rerun_Init;
--PROCEDURE DB_To_Text();
-----------------------------------------------------------------------------------------------------------------------------------
gv_User VARCHAR2(30);
gv_Module_Name VARCHAR2(30) :='ORCL_TOOL';
C_VERSION CONSTANT VARCHAR2(18) :='5.0.015.20040426';
C_AUTHOR CONSTANT VARCHAR2(80) :='jiangbing@lenovo.com';
C_DESCR CONSTANT VARCHAR2(200) :='ORACLE数据库开发工具库';
END Orcl_Tool;
/
CREATE OR REPLACE PACKAGE BODY Orcl_Tool
AS
FUNCTION Comp_Tab_Lvl(iv_CTable VARCHAR2) RETURN NUMBER
AS
n_PTables NUMBER;
n_YLevel NUMBER;
TYPE t_YLevels_Array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
t_YLevels t_YLevels_Array;
n_PTables_ID NUMBER :=0;
n_Max_YLevel NUMBER;
BEGIN
SELECT COUNT(*) INTO n_PTables FROM MD_CFG_FK_Cons WHERE CTable=iv_CTable AND PTable IN (SELECT CTable FROM MD_CFG_FK_CONS);
IF n_PTables =0 THEN
RETURN 0;
ELSE
FOR C IN (SELECT * FROM MD_CFG_FK_CONS WHERE CTable=iv_CTable AND PTable IN (SELECT CTable FROM MD_CFG_FK_CONS) ) LOOP
n_PTables_ID :=n_PTables_ID+1;
n_YLevel :=Comp_Tab_Lvl(C.PTable)+1;
--t_YLevels_Array.Extend;
-- dbms_output.put_line(n_PTables_id);
-- dbms_output.put_line('111');
t_YLevels(n_PTables_ID) :=n_YLevel;
END LOOP;
DBMS_OUTPUT.PUT_LINE(IV_CTable);
FOR CC IN 1..T_YLEVELS.COUNT LOOP
IF CC=1 THEN
n_Max_YLevel :=T_YLEVELS(CC);
ELSE
IF T_YLEVELS(CC)>n_Max_YLevel THEN
n_Max_YLevel :=T_YLEVELS(CC);
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(T_YLEVELS(CC));
END LOOP;
RETURN n_Max_YLevel;
END IF;
END Comp_Tab_Lvl;
/****************************************************************************************
--TOOL2
--
****************************************************************************************/
FUNCTION Get_PTable(iv_CTable VARCHAR2,iv_Cons_Name VARCHAR2) RETURN VARCHAR2
IS
v_PTable VARCHAR2(30);
BEGIN
SELECT Table_Name INTO v_PTable
FROM DBA_Constraints
WHERE Constraint_Type='P'
AND OWNER=gv_ADW_User
AND Constraint_Name=iv_Cons_Name;
RETURN v_PTable;
END;
PROCEDURE Get_Parent_Tab_FK(iv_CTable VARCHAR2)
IS
BEGIN
INSERT INTO MD_CFG_FK_CONS
SELECT 'IDBTOADW',Table_Name,
Table_Name||'_E',
Get_PTable(iv_CTable,R_Constraint_Name) R_Table,
Constraint_Name,
9999
FROM DBA_Constraints C1 WHERE Constraint_Type='R'
AND OWNER=gv_ADW_User AND Table_Name=iv_CTable
AND Constraint_Name NOT IN (SELECT FK_Name FROM MD_CFG_FK_CONS);
COMMIT;
END Get_Parent_Tab_FK;
/****************************************************************************************
--TOOL2
--
****************************************************************************************/
PROCEDURE Get_Child_Tab_FK(iv_PTable VARCHAR2)
IS
BEGIN
INSERT INTO MD_CFG_FK_CONS
SELECT 'IDBTOADW',Table_Name,NULL,iv_PTable,Constraint_NAME,9999
FROM DBA_Constraints
WHERE Constraint_Type='R'
AND OWNER=gv_ADW_User
AND R_Constraint_Name IN
(SELECT Constraint_NAME FROM DBA_Constraints WHERE Table_Name=iv_PTable
AND OWNER=gv_ADW_User
AND Constraint_Type='P')
AND Constraint_Name NOT IN (SELECT FK_Name FROM MD_CFG_FK_CONS);
COMMIT;
END Get_Child_Tab_FK;
/************************************************************************************************************
用于建立MD_CFG_CALENDAR表的触发器，用户可以自己定义，这个触发器功能就是要在用户增加新的与时间有关的RESOURCE
的时候给UI表增加原始数据，用于给与用户维护RESOURCE与日历的关系。
************************************************************************************************************/
PROCEDURE Create_UI_Trigger(on_ExitCode OUT NUMBER)
IS
err_num NUMBER;
err_msg VARCHAR2(512);
v_stmt VARCHAR2(10000) :='NULL';
v_columnstr VARCHAR2(2000) ;
v_columnstr_old VARCHAR2(2000) ;
v_columnstr_new VARCHAR2(2000) ;
v_table_name VARCHAR2(30):='NULL';
v_ltable_name VARCHAR2(30) :='NULL';
v_ltrigger_name VARCHAR2(30) :='NULL';
v_Logic_Proc_Name VARCHAR2(30) :='Create_UI_Trigger';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='Create_UI_Trigger Exception: ';
BEGIN
on_ExitCode :=-20099;
FOR table_rec IN (SELECT * FROM MD_Cfg_UILogTrigger) LOOP
BEGIN
--auto generate the table column data
v_columnstr := NULL;
v_columnstr_old := NULL;
v_columnstr_new := NULL;
FOR v_rec IN( SELECT table_name, column_name FROM user_tab_columns
WHERE table_name = table_rec.table_name)
LOOP
v_columnstr := v_columnstr || ',' || v_rec.column_name;
v_columnstr_old := v_columnstr_old || ',:OLD.' || v_rec.column_name;
v_columnstr_new := v_columnstr_new || ',:NEW.' || v_rec.column_name;
END LOOP;
v_ErrDesc :='Create_UI_Trigger Exception: ';
v_columnstr := LTRIM (v_columnstr, ',');
v_columnstr_old := LTRIM (v_columnstr_old, ',');
v_columnstr_new := LTRIM (v_columnstr_new, ',');
v_table_name := table_rec.table_name;
v_ltable_name := table_rec.ltable_name;
v_ltrigger_name := table_rec.ltrigger_name;
v_stmt :=' CREATE OR REPLACE TRIGGER '||v_ltrigger_name;
v_stmt := v_stmt||' AFTER INSERT OR UPDATE OR DELETE ON '||v_table_name ||' FOR EACH ROW';
v_stmt := v_stmt||' /** AUTHOR: yangbt@legend.com DESCR:auto generated by IDB_Base_Tools.Create_UI_Trigger(on_ExitCode OUT NUMBER) procedure **/ ';
v_stmt := v_stmt||' BEGIN ';
v_stmt := v_stmt||' IF INSERTING THEN ';
v_stmt := v_stmt||' INSERT INTO '||v_ltable_name||'('||v_columnstr||',logtime,logtype,loguser,loguiuser,logterminal,logdelflag) VALUES('||v_columnstr_new||',SYSDATE,''I'',USER,SUBSTRB(USERENV(''CLIENT_INFO''),1,64),SUBSTRB(USERENV(''TERMINAL''),1,64),''0''); ';
v_stmt := v_stmt||' ELSIF UPDATING THEN ';
v_stmt := v_stmt||' INSERT INTO '||v_ltable_name||'('||v_columnstr||',logtime,logtype,loguser,loguiuser,logterminal,logdelflag) VALUES('||v_columnstr_old||',SYSDATE,''B'',USER,SUBSTRB(USERENV(''CLIENT_INFO''),1,64),SUBSTRB(USERENV(''TERMINAL''),1,64),''0''); ';
v_stmt := v_stmt||' INSERT INTO '||v_ltable_name||'('||v_columnstr||',logtime,logtype,loguser,loguiuser,logterminal,logdelflag) VALUES('||v_columnstr_new||',SYSDATE,''A'',USER,SUBSTRB(USERENV(''CLIENT_INFO''),1,64),SUBSTRB(USERENV(''TERMINAL''),1,64),''0''); ';
v_stmt := v_stmt||' ELSIF DELETING THEN ';
v_stmt := v_stmt||' INSERT INTO '||v_ltable_name||'('||v_columnstr||',logtime,logtype,loguser,loguiuser,logterminal,logdelflag) VALUES('||v_columnstr_old||',SYSDATE,''D'',USER,SUBSTRB(USERENV(''CLIENT_INFO''),1,64),SUBSTRB(USERENV(''TERMINAL''),1,64),''0''); ';
v_stmt := v_stmt||' END IF; ';
v_stmt := v_stmt||' END '||v_ltrigger_name||';';
EXECUTE IMMEDIATE v_stmt;
EXCEPTION
WHEN OTHERS THEN
err_msg :=SUBSTR(SQLERRM,1,512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{'||v_stmt||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,err_msg,'E',v_Data_Source,v_Data_Dest,v_ErrDesc,'MD_LOG_BASEFUNCS');
COMMIT;
END;
END LOOP;
on_ExitCode :=0;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_ErrDesc :=SUBSTR(v_ErrDesc||'{'||v_stmt||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,SUBSTR(SQLERRM,1,512),'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END Create_UI_Trigger;
/************************************************************************************************************
用于建立MD_CFG_CALENDAR表的触发器，用户可以自己定义，这个触发器功能就是要在用户增加新的与时间有关的RESOURCE
的时候给UI表增加原始数据，用于给与用户维护RESOURCE与日历的关系。
************************************************************************************************************/
/************************************************************************************
CREATE OR REPLACE TRIGGER ADW2_MD_USER.MT_CFG_CALENDAR
BEFORE INSERT OR DELETE
ON ADW2_MD_USER.MD_CFG_CALENDAR
FOR EACH ROW
Declare
n_Cnt Number :=0;
n_Days NUMBER :=0;
v_BU MD_CFG_CALENDAR.BU%TYPE;
v_Facility_Name MD_CFG_CALENDAR.Facility_Name%TYPE;
v_Capacity_Pat MD_CFG_CALENDAR.Capacity_Pat%TYPE;
Begin
IF INSERTING THEN
SELECT COUNT(*) INTO n_Cnt FROM MD_CFG_CALENDAR
WHERE BU = :new.BU
AND Facility_Name = :new.Facility_Name
AND Capacity_Pat = :new.Capacity_Pat;
IF n_Cnt IS NULL OR n_Cnt=0 THEN
n_Days :=TO_DATE('2031-12-31','YYYY-MM-DD') - TRUNC(SYSDATE,'DD');
FOR n_Loop IN 0..n_Days LOOP
INSERT INTO MD_UI_Calendar_Capacity
VALUES(:new.BU,
:new.Facility_Name,
TRUNC(SYSDATE,'DD')+n_Loop,
:new.Capacity_Pat,
1);
END LOOP;
END IF;
END IF;
END;
************************************************************************************/
/************************************************************************************************************
用于更新逻辑使用的相关的表，这里运行一次全部更新，对于引用一些系统表，如DUAL,DBA_TABLES等不会做作为相关表更新的
主要用于针对更新IDB系统的逻辑相关表，所以需要指定IDB系统的IDB_USER和ADW_USER。
************************************************************************************************************/
PROCEDURE Upt_Logic_Related_TBs(iv_IDB_User VARCHAR2,iv_ADW_User VARCHAR2)
IS
RELATE_TBS VARCHAR2(2024);
BEGIN
FOR CC IN(SELECT * FROM MD_IDB_CTRL) LOOP
RELATE_TBS :='';
FOR C IN (SELECT * FROM DBA_DEPENDENCIES WHERE NAME=CC.IDB_LOGIC_NAME AND (OWNER=iv_IDB_User OR OWNER=iv_ADW_User)
AND REFERENCED_TYPE='TABLE') LOOP
IF C.REFERENCED_NAME<>'DUAL' THEN
RELATE_TBS :=RELATE_TBS||','||C.REFERENCED_NAME;
END IF;
END LOOP;
IF CC.IDB_LOGIC_NAME='IDB_FACILITY_ITEM' THEN
DBMS_OUTPUT.PUT_LINE(CC.IDB_LOGIC_NAME);
DBMS_OUTPUT.PUT_LINE(substr(RELATE_TBS,1,255));
END IF;
UPDATE MD_IDB_CTRL SET RELATED_TABLES=SUBSTR(RELATE_TBS,2) WHERE IDB_LOGIC_NAME=CC.IDB_LOGIC_NAME;
END LOOP;
COMMIT;
RELATE_TBS :=SUBSTR(RELATE_TBS,2);
END Upt_Logic_Related_TBs;
/************************************************************************************************************
用于比较两个表的内容的差异，这两个表的表名称必须一致，结构必须一致，必须有主键，数据比较是基于主键数据的比较或者
基于所有列的数据，若iv_PKAble=TRUE那么是基于主键的比较，否则是基于所有列的数据比较需要的参数有对比的源数据的OWNER
和对比的目标OWNER和目标DBLINK(可以为空)，
-------------------------------------------
运行之前要在{IDB_USER}下面建立如下的表：(用后注意删除)
drop table idb$table_comp;
CREATE TABLE IDB$TABLE_COMP (
TABLE_NAME VARCHAR2(30),
COMP_SQL VARCHAR2(4000),
COMP_SQL2 VARCHAR2(4000));
************************************************************************************************************/
PROCEDURE Table_Comp(iv_Src_Owner VARCHAR2,iv_Dest_Owner VARCHAR2,iv_Dest_DBLink VARCHAR2,iv_Range VARCHAR2,iv_PKAble BOOLEAN,iv_Comp_Mode VARCHAR2)
IS
v_Comp_Cols VARCHAR2(320);
v_Comp_SQL VARCHAR2(4000);
v_Comp_SQL2 VARCHAR2(4000);
n_Loop NUMBER;
n_Cnt INT;
v_Range VARCHAR2(60);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE IDB$TABLE_COMP';
v_Range :=iv_Range||'%';
FOR CR IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=iv_Src_Owner AND Table_Name LIKE v_Range) LOOP
BEGIN
v_Comp_Cols :='';
--DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME);
n_Loop :=0;
IF iv_PKAble=TRUE THEN
FOR CR2 IN (SELECT COLUMN_NAME FROM
DBA_CONS_COLUMNS CC,DBA_CONSTRAINTS C
WHERE CC.CONSTRAINT_NAME=C.CONSTRAINT_NAME AND CC.OWNER=C.OWNER AND C.CONSTRAINT_TYPE='P' AND C.OWNER= iv_Src_Owner AND C.TABLE_NAME=CR.TABLE_NAME ) LOOP
n_Loop :=n_Loop+1;
IF n_Loop=1 THEN
v_Comp_Cols :=CR2.COLUMN_NAME;
ELSE
v_Comp_Cols :=v_Comp_Cols||','||CR2.COLUMN_NAME;
END IF;
END LOOP;
ELSE
FOR CR3 IN (SELECT COLUMN_NAME FROM
DBA_TAB_COLUMNS TC,DBA_TABLES T
WHERE TC.TABLE_NAME=T.TABLE_NAME AND TC.OWNER=T.OWNER AND T.OWNER= iv_Src_Owner AND T.TABLE_NAME=CR.TABLE_NAME ) LOOP
n_Loop :=n_Loop+1;
IF n_Loop=1 THEN
v_Comp_Cols :=CR3.COLUMN_NAME;
ELSE
v_Comp_Cols :=v_Comp_Cols||','||CR3.COLUMN_NAME;
END IF;
END LOOP;
END IF;
IF v_Comp_Cols IS NULL THEN
DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME||'IS NO PK');
ELSE
v_Comp_SQL :='SELECT COUNT(*) FROM '||iv_Src_Owner||'.'||CR.TABLE_NAME ||' WHERE ('||v_Comp_Cols||') NOT IN (SELECT '||v_Comp_Cols||' FROM '||iv_Dest_Owner ||'.'|| CR.TABLE_NAME || '@' ||iv_Dest_DBLink||');';
v_Comp_SQL2 :='SELECT COUNT(*) FROM '||iv_Dest_Owner||'.'||CR.TABLE_NAME ||'@'||iv_Dest_DBLink||' WHERE ('||v_Comp_Cols||') NOT IN (SELECT '||v_Comp_Cols||' FROM '||iv_Src_Owner ||'.'|| CR.TABLE_NAME ||');';
IF UPPER(iv_Comp_Mode) ='SYSTEM' THEN
EXECUTE IMMEDIATE 'INSERT INTO IDB$TABLE_COMP VALUES('''||CR.TABLE_NAME||''','''||V_COMP_SQL||''','''||v_Comp_SQL2||''')';
ELSE
EXECUTE IMMEDIATE RTRIM(v_Comp_SQL,';') INTO n_Cnt;
IF n_Cnt>0 THEN
DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME||'IS NOT SAME');
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE SQL IS ERROR');
END;
END LOOP;
COMMIT;
END Table_Comp;
/************************************************************************************************************
根据IDB字典表MD_IDB_Objects来比较两个表的内容的差异，这两个表的表名称必须一致，结构必须一致，必须有主键，数据比较是基于主键数据的比较或者
基于所有列的数据，若iv_PKAble=TRUE那么是基于主键的比较，否则是基于所有列的数据比较需要的参数有对比的源数据的OWNER
和对比的目标OWNER和目标DBLINK(可以为空)，
-------------------------------------------
运行之前要在{IDB_USER}下面建立如下的表：(用后注意删除)
drop table idb$table_comp;
CREATE TABLE IDB$TABLE_COMP (
TABLE_NAME VARCHAR2(30),
COMP_SQL VARCHAR2(4000),
COMP_SQL2 VARCHAR2(4000));
************************************************************************************************************/
PROCEDURE Table_Comp2(iv_Src_Owner VARCHAR2,iv_Dest_Owner VARCHAR2,iv_Dest_DBLink VARCHAR2,iv_Tab_Type VARCHAR2,iv_PKAble BOOLEAN,iv_Comp_Mode VARCHAR2)
IS
v_Comp_Cols VARCHAR2(320);
v_Comp_SQL VARCHAR2(4000);
v_Comp_SQL2 VARCHAR2(4000);
n_Loop NUMBER;
n_Cnt INT;
v_Range VARCHAR2(60);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE IDB$TABLE_COMP';
v_Range :=iv_Tab_Type||'%';
FOR CR IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=iv_Src_Owner AND Table_Name IN (SELECT Object_Name FROM MD_IDB_Objects WHERE Object_Type LIKE v_Range)) LOOP
BEGIN
v_Comp_Cols :='';
--DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME);
n_Loop :=0;
IF iv_PKAble=TRUE THEN
FOR CR2 IN (SELECT COLUMN_NAME FROM
DBA_CONS_COLUMNS CC,DBA_CONSTRAINTS C
WHERE CC.CONSTRAINT_NAME=C.CONSTRAINT_NAME AND CC.OWNER=C.OWNER AND C.CONSTRAINT_TYPE='P' AND C.OWNER= iv_Src_Owner AND C.TABLE_NAME=CR.TABLE_NAME ) LOOP
n_Loop :=n_Loop+1;
IF n_Loop=1 THEN
v_Comp_Cols :=CR2.COLUMN_NAME;
ELSE
v_Comp_Cols :=v_Comp_Cols||','||CR2.COLUMN_NAME;
END IF;
END LOOP;
ELSE
FOR CR3 IN (SELECT COLUMN_NAME FROM
DBA_TAB_COLUMNS TC,DBA_TABLES T
WHERE TC.TABLE_NAME=T.TABLE_NAME AND TC.OWNER=T.OWNER AND T.OWNER= iv_Src_Owner AND T.TABLE_NAME=CR.TABLE_NAME ) LOOP
n_Loop :=n_Loop+1;
IF n_Loop=1 THEN
v_Comp_Cols :=CR3.COLUMN_NAME;
ELSE
v_Comp_Cols :=v_Comp_Cols||','||CR3.COLUMN_NAME;
END IF;
END LOOP;
END IF;
IF v_Comp_Cols IS NULL THEN
DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME||'IS NO PK');
ELSE
v_Comp_SQL :='SELECT COUNT(*) FROM '||iv_Src_Owner||'.'||CR.TABLE_NAME ||' WHERE ('||v_Comp_Cols||') NOT IN (SELECT '||v_Comp_Cols||' FROM '||iv_Dest_Owner ||'.'|| CR.TABLE_NAME || '@' ||iv_Dest_DBLink||');';
v_Comp_SQL2 :='SELECT COUNT(*) FROM '||iv_Dest_Owner||'.'||CR.TABLE_NAME ||'@'||iv_Dest_DBLink||' WHERE ('||v_Comp_Cols||') NOT IN (SELECT '||v_Comp_Cols||' FROM '||iv_Src_Owner ||'.'|| CR.TABLE_NAME ||');';
IF UPPER(iv_Comp_Mode) ='SYSTEM' THEN
EXECUTE IMMEDIATE 'INSERT INTO IDB$TABLE_COMP VALUES('''||CR.TABLE_NAME||''','''||V_COMP_SQL||''','''||v_Comp_SQL2||''')';
ELSE
EXECUTE IMMEDIATE RTRIM(v_Comp_SQL,';') INTO n_Cnt;
IF n_Cnt>0 THEN
DBMS_OUTPUT.PUT_LINE(CR.TABLE_NAME||'IS NOT SAME');
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE SQL IS ERROR');
END;
END LOOP;
COMMIT;
END Table_Comp2;
PROCEDURE Del_Log(Module_Name VARCHAR2,End_Dttm DATE,Start_Dttm DATE DEFAULT TO_DATE('2000-01-01','YYYY-MM-DD'))
IS
BEGIN
IF UPPER(Module_Name)='PONINV' THEN
DELETE FROM MD_Log_POnInv WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='BOM' THEN
DELETE FROM MD_Log_Bom WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='DF' THEN
DELETE FROM MD_Log_DF WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='DEMAND' THEN
DELETE FROM MD_Log_Demand WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='DP' THEN
NULL;--DELETE FROM MD_Log_DP WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='TOADW' THEN
DELETE FROM MD_Log_ToADW WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='BASEFUNCS' THEN
DELETE FROM MD_Log_BaseFuncs WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='ADP' THEN
DELETE FROM MD_Log_ADP WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
ELSIF UPPER(Module_Name)='IDB_ALL_RUN' THEN
DELETE FROM MD_Log_Run;
ELSE
DELETE FROM MD_Log WHERE EventTime BETWEEN Start_Dttm AND End_Dttm;
END IF;
COMMIT;
END Del_Log;
PROCEDURE Cls_Log(Module_Name VARCHAR2)
IS
v_Cls_SQL IDB_Base_Type.tSQL;
nExitCode NUMBER;
BEGIN
IF UPPER(Module_Name)='PONINV' THEN
v_Cls_SQL :='MD_Log_POnInv';
ELSIF UPPER(Module_Name)='BOM' THEN
v_Cls_SQL :='MD_Log_Bom';
ELSIF UPPER(Module_Name)='DF' THEN
v_Cls_SQL :='MD_Log_DF';
ELSIF UPPER(Module_Name)='DEMAND' THEN
v_Cls_SQL :='MD_Log_Demand';
ELSIF UPPER(Module_Name)='DP' THEN
v_Cls_SQL :='MD_Log_DP';
ELSIF UPPER(Module_Name)='TOADW' THEN
v_Cls_SQL :='MD_Log_ToADW';
ELSIF UPPER(Module_Name)='BASEFUNCS' THEN
v_Cls_SQL :='MD_Log_BaseFuncs';
ELSIF UPPER(Module_Name)='ADP' THEN
v_Cls_SQL :='MD_Log_ADP';
ELSIF UPPER(Module_Name)='IDB_ALL_RUN' THEN
v_Cls_SQL :='MD_Log_Run';
ELSE
v_Cls_SQL :='MD_Log';
END IF;
v_Cls_SQL :='TRUNCATE TABLE '||v_Cls_SQL;
IDB_Base_Funcs.Truncate_Table(nExitCode,v_Cls_SQL);
END Cls_Log;
FUNCTION Get_Task_CL_Text(iv_Task_Name VARCHAR2,iv_CL_Text VARCHAR2) RETURN VARCHAR2
IS
v_Task_CL_Text VARCHAR2(2000);
v_CL_Text_Part VARCHAR2(2000);
v_Find_Task_Name VARCHAR2(40);
v_Left_Sep VARCHAR2(12) :='{';
v_Right_Sep VARCHAR2(12) :='}';
v_Indicator VARCHAR2(12) :=':';
n_Find_Pos NUMBER;
n_Left_Sep_Pos NUMBER;
n_Right_Sep_Pos NUMBER;
n_Ind_Pos NUMBER;
BEGIN
v_Find_Task_Name :='['||iv_Task_Name||']';
n_Find_Pos :=INSTR(iv_CL_Text,v_Find_Task_Name);
IF n_Find_Pos=0 THEN
RETURN v_Task_CL_Text;
END IF;
IF n_Find_Pos>0 THEN
n_Left_Sep_Pos :=INSTR(iv_CL_Text,v_Left_Sep,n_Find_Pos);
n_Right_Sep_Pos :=INSTR(iv_CL_Text,v_Right_Sep,n_Find_Pos);
IF (n_Left_Sep_Pos>n_Right_Sep_Pos AND n_Right_Sep_Pos>0) OR (n_Left_Sep_Pos=0 AND n_Right_Sep_Pos>0) THEN
v_CL_Text_Part :=SUBSTR(iv_CL_Text,n_Find_Pos,n_Right_Sep_Pos - n_Find_Pos);
ELSE
IF n_Left_Sep_Pos>0 THEN
v_CL_Text_Part :=SUBSTR(iv_CL_Text,n_Find_Pos,n_Left_Sep_Pos - n_Find_Pos);
ELSE
v_CL_Text_Part :=SUBSTR(iv_CL_Text,n_Find_Pos);
END IF;
END IF;
n_Ind_Pos :=INSTR(v_CL_Text_Part,':');
-- dbms_output.put_line('mh:'||to_char(n_Ind_Pos));
IF n_Ind_Pos>0 THEN
v_Task_CL_Text :=SUBSTR(v_CL_Text_Part,n_Ind_Pos+1);
END IF;
END IF;
RETURN v_Task_CL_Text;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER)
IS
-------------------------------------------------------------------------------------------------
n_ExitCode NUMBER;
v_SQLErrM VARCHAR2(512);
v_Logic_Proc_Name VARCHAR2(30) :='CL_OBJ_TAB';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='CL_Obj_Tab(All) Exception:';
BEGIN
on_ExitCode :=-20099;
/*************************************************************************************************
--循环运行请出当前任务的公用表--
*************************************************************************************************/
FOR Cr_CL_Tab IN (SELECT * FROM MD_CFG_ToADW2) LOOP
BEGIN
v_Data_Source :=Cr_CL_Tab.FromIDBTable;
v_ErrDesc :='CL_Obj_Tab(ALL) Exception:';
/*************************************************************************************************
--清除(TRUNCATE TABLE)用户指定的IDB目标表--
*************************************************************************************************/
IDB_Base_Funcs.Truncate_Table(n_ExitCode,Cr_CL_Tab.FromIDBTable);
IF n_ExitCode<0 THEN
v_SQLErrm :=SUBSTR (SQLERRM(n_ExitCode), 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{Truncate IDB Object Tables Error'||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'W',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_SQLErrm :=SUBSTR (SQLERRM, 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'Clear IDB Object Tables:Loop Inside:{OTHERS}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END;
END LOOP;
on_ExitCode :=0;
END CL_Obj_Tab;
PROCEDURE CL_Obj_Tab
IS
-------------------------------------------------------------------------------------------------
n_ExitCode NUMBER;
v_SQLErrM VARCHAR2(512);
v_Logic_Proc_Name VARCHAR2(30) :='CL_OBJ_TAB';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='CL_Obj_Tab(ALL,No Return Parameter) Exception:';
BEGIN
/*************************************************************************************************
--循环运行请出当前任务的公用表--
*************************************************************************************************/
FOR Cr_CL_Tab IN (SELECT * FROM MD_CFG_ToADW2) LOOP
BEGIN
v_Data_Source :=Cr_CL_Tab.FromIDBTable;
v_ErrDesc :='CL_Obj_Tab(ALL,No Return Parameter) Exception:';
/*************************************************************************************************
--清除(TRUNCATE TABLE)用户指定的IDB目标表--
*************************************************************************************************/
IDB_Base_Funcs.Truncate_Table(n_ExitCode,Cr_CL_Tab.FromIDBTable);
IF n_ExitCode<0 THEN
v_SQLErrm :=SUBSTR (SQLERRM(n_ExitCode), 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{Truncate IDB Object Tables Error'||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'W',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_SQLErrm :=SUBSTR (SQLERRM, 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'Clear IDB Object Tables:Loop Inside:{OTHERS}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END;
END LOOP;
END CL_Obj_Tab;
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER,iv_Task_Name VARCHAR2,iv_CL_Mode VARCHAR2 DEFAULT NULL)
IS
-------------------------------------------------------------------------------------------------
n_ExitCode NUMBER;
v_SQLErrM VARCHAR2(512);
v_Logic_Proc_Name VARCHAR2(30) :='CL_OBJ_TAB';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='CL_Obj_Tab(Task) Exception:';
v_Cust_CL_Text VARCHAR2(4000);
n_Task_ID NUMBER;
BEGIN
on_ExitCode :=-20099;
SELECT Task_Deal_ID INTO n_Task_ID FROM MD_Cfg_IDB_Task WHERE Task_Name=iv_Task_Name;
/*************************************************************************************************
--循环运行请出当前任务的公用表--
*************************************************************************************************/
FOR Cr_CL_Tab IN (SELECT * FROM MD_CFG_ToADW2 WHERE OBJ_TAB_CL IS NOT NULL AND BITAND(OBJ_TAB_CL,n_Task_ID)=n_Task_ID) LOOP
BEGIN
v_Data_Source :=Cr_CL_Tab.FromIDBTable;
v_ErrDesc :='CL_Obj_Tab(Task) Exception:';
IF iv_CL_Mode='X' THEN
v_Cust_CL_Text :=IDB_Base_Funcs.Get_Task_CL_Text(iv_Task_Name,Cr_CL_Tab.Obj_Tab_CL_Text);
IF v_Cust_CL_Text IS NOT NULL THEN
IDB_Base_Funcs.Dynamic_Exec(n_ExitCode,v_Cust_CL_Text);
END IF;
ELSE
/*************************************************************************************************
--清除(TRUNCATE TABLE)用户指定的IDB目标表--
*************************************************************************************************/
IDB_Base_Funcs.Truncate_Table(n_ExitCode,Cr_CL_Tab.FromIDBTable);
END IF;
IF n_ExitCode<0 THEN
v_SQLErrm :=SUBSTR (SQLERRM(n_ExitCode), 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{Truncate IDB Object Tables Error'||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'W',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_SQLErrm :=SUBSTR (SQLERRM, 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'Clear IDB Object Tables:Loop Inside:{OTHERS}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END;
END LOOP;
on_ExitCode :=0;
END CL_Obj_Tab;
PROCEDURE CL_Obj_Tab(on_ExitCode OUT NUMBER,in_Task_ID NUMBER,iv_CL_Mode VARCHAR2 DEFAULT NULL)
IS
-------------------------------------------------------------------------------------------------
n_ExitCode NUMBER;
v_SQLErrM VARCHAR2(512);
v_Logic_Proc_Name VARCHAR2(30) :='CL_OBJ_TAB';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='CL_Obj_Tab(Task) Exception:';
v_Cust_CL_Text VARCHAR2(4000);
n_Task_ID NUMBER;
BEGIN
on_ExitCode :=-20099;
/*************************************************************************************************
--循环运行请出当前任务的公用表--
*************************************************************************************************/
FOR Cr_CL_Tab IN (SELECT * FROM MD_CFG_ToADW2 WHERE OBJ_TAB_CL IS NOT NULL AND BITAND(OBJ_TAB_CL,in_Task_ID)>0) LOOP
BEGIN
v_Data_Source :=Cr_CL_Tab.FromIDBTable;
v_ErrDesc :='CL_Obj_Tab(Task) Exception:';
IF iv_CL_Mode='X' THEN
FOR TK IN (SELECT * FROM MD_Cfg_IDB_Task WHERE BITAND(Task_Deal_ID,in_Task_ID)=in_Task_ID) LOOP
v_Cust_CL_Text :=IDB_Base_Funcs.Get_Task_CL_Text(TK.Task_Name,Cr_CL_Tab.Obj_Tab_CL_Text);
IF v_Cust_CL_Text IS NOT NULL THEN
IDB_Base_Funcs.Dynamic_Exec(n_ExitCode,v_Cust_CL_Text);
END IF;
END LOOP;
ELSE
/*************************************************************************************************
--清除(TRUNCATE TABLE)用户指定的IDB目标表--
*************************************************************************************************/
IDB_Base_Funcs.Truncate_Table(n_ExitCode,Cr_CL_Tab.FromIDBTable);
END IF;
IF n_ExitCode<0 THEN
v_SQLErrm :=SUBSTR (SQLERRM(n_ExitCode), 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{Truncate IDB Object Tables Error'||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'W',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_SQLErrm :=SUBSTR (SQLERRM, 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'Clear IDB Object Tables:Loop Inside:{OTHERS}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END;
END LOOP;
on_ExitCode :=0;
END CL_Obj_Tab;
PROCEDURE CL_Obj_Tab(in_Task_ID NUMBER,iv_CL_Mode VARCHAR2 DEFAULT NULL)
IS
-------------------------------------------------------------------------------------------------
n_ExitCode NUMBER;
v_SQLErrM VARCHAR2(512);
v_Logic_Proc_Name VARCHAR2(30) :='CL_OBJ_TAB';
v_Data_Source VARCHAR2(512);
v_Data_Dest VARCHAR2(512);
v_ErrDesc VARCHAR2(4000) :='CL_Obj_Tab(Tasks,No Return Parameter) Exception:';
v_Cust_CL_Text VARCHAR2(4000);
n_Task_ID NUMBER;
BEGIN
/*************************************************************************************************
--循环运行请出当前任务的公用表--
*************************************************************************************************/
DBMS_OUTPUT.PUT_LINE('XP');
FOR Cr_CL_Tab IN (SELECT * FROM MD_CFG_ToADW2 WHERE OBJ_TAB_CL IS NOT NULL AND BITAND(OBJ_TAB_CL,in_Task_ID)>0) LOOP
BEGIN
v_Data_Source :=Cr_CL_Tab.FromIDBTable;
v_ErrDesc :='CL_Obj_Tab(Tasks,No Return Parameter) Exception:';
IF iv_CL_Mode='X' THEN
FOR TK IN (SELECT * FROM MD_Cfg_IDB_Task WHERE BITAND(Task_Deal_ID,in_Task_ID)=in_Task_ID) LOOP
v_Cust_CL_Text :=IDB_Base_Funcs.Get_Task_CL_Text(TK.Task_Name,Cr_CL_Tab.Obj_Tab_CL_Text);
IF v_Cust_CL_Text IS NOT NULL THEN
IDB_Base_Funcs.Dynamic_Exec(n_ExitCode,v_Cust_CL_Text);
END IF;
END LOOP;
ELSE
/*************************************************************************************************
--清除(TRUNCATE TABLE)用户指定的IDB目标表--
*************************************************************************************************/
IDB_Base_Funcs.Truncate_Table(n_ExitCode,Cr_CL_Tab.FromIDBTable);
END IF;
IF n_ExitCode<0 THEN
v_SQLErrm :=SUBSTR (SQLERRM(n_ExitCode), 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'{Truncate IDB Object Tables Error'||'}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'W',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_SQLErrm :=SUBSTR (SQLERRM, 1, 512);
v_ErrDesc :=SUBSTR(v_ErrDesc||'Clear IDB Object Tables:Loop Inside:{OTHERS}',1,4000);
IDB_Base_Funcs.Insert_Log(SYSDATE,gv_IDB_User||'.'||gv_Logic_Name,v_Logic_Proc_Name,SQLCODE,v_SQLErrM,'E',v_Data_Source,v_Data_Dest,v_ErrDesc);
COMMIT;
END;
END LOOP;
END CL_Obj_Tab;
PROCEDURE Exp_Reset
IS
BEGIN
/*
恢复EXCEPTION_LOG表的状态为0
*/
UPDATE MD_Cfg_Exp_Log_Tab SET Engage_Status=0;
COMMIT;
END Exp_Reset;
PROCEDURE IDB_Rerun_Init
IS
BEGIN
UPDATE MD_Cfg_Exp_Log_Tab SET Engage_Status=0;
UPDATE MD_Cfg_IDB_Task SET Start_Dttm=NULL,End_Dttm=NULL;
COMMIT;
END IDB_Rerun_Init;
END Orcl_Tool;
浙公网安备 33010602011771号