w9673GeE

1create or replace PACKAGE BODY milestone_logs  AS
2    PROCEDURE table_creation IS 
3        BEGIN
4            X_Mig_Common.Drop_If_Exists('MILESTONES_LOGS');
5            drop_sequence;
6            EXECUTE IMMEDIATE 
7            'CREATE TABLE MILESTONES_LOGS(
8                NR NUMBER,
9                MILESTONE_NAME VARCHAR2(100),
10                EXECUTE_DATE TIMESTAMP default CURRENT_TIMESTAMP,
11                EXECUTE_STATUS VARCHAR2(3),
12                PATH_OF_SCRIPT VARCHAR2(200),
13                DESCRIPTION VARCHAR2(4000),
14                STATS VARCHAR2(1000))
15            ';
16            DBMS_OUTPUT.PUT_LINE('Table MILESTONES_LOGS was created');
17            EXECUTE IMMEDIATE
18            'CREATE SEQUENCE milestones_log_id
19                START WITH 100
20                INCREMENT BY 1
21                MAXVALUE 9999999
22                NOMINVALUE
23                CYCLE
24                ORDER';
25        END;
26        
27    PROCEDURE drop_sequence
28    IS
29        v_seq_check number;
30    BEGIN
31       SELECT COUNT (*) 
32       INTO v_seq_check
33       FROM DUAL
34       WHERE EXISTS (
35            SELECT * 
36            FROM user_objects 
37            WHERE Object_Name='MILESTONES_LOG_ID');
38            dbms_output.put_line(v_seq_check);
39        IF v_seq_check>0
40        THEN
41            EXECUTE IMMEDIATE 
42                'DROP SEQUENCE MILESTONES_LOG_ID';
43        END IF;
44    END;
45
46    FUNCTION cnt_check (a_table_name VARCHAR2)
47        RETURN number
48        IS
49            r_cnt Number;
50            v_sql VARCHAR2(4000);
51        BEGIN
52            v_sql := 'SELECT COUNT(*) FROM ' || a_table_name;
53            EXECUTE IMMEDIATE v_sql INTO r_cnt;
54        RETURN r_cnt;
55    END cnt_check;
56   
57    FUNCTION get_milestone_description(a_concat_pattern VARCHAR2) -- tables that needs to be checked will be concatenated in pattern table1_name|table2_name....etc.
58        return VARCHAR2
59        IS
60        r_description VARCHAR2(1000);
61        v_cnt NUMBER;
62        CURSOR c_tab_names IS
63            SELECT 
64                REGEXP_SUBSTR (a_concat_pattern,'[^|]+',1,LEVEL) as tname
65            FROM DUAL
66            CONNECT BY
67            REGEXP_SUBSTR(a_concat_pattern,'[^|]+',1,LEVEL)IS NOT NULL;
68        
69        BEGIN
70         FOR i_table in c_tab_names
71            LOOP
72                v_cnt:=cnt_check(i_table.tname);
73                IF v_cnt = 0
74                THEN
75                    r_description:=r_description||i_table.tname||'-#NO_DATA#';
76                ELSE
77                    r_description:=r_description||i_table.tname||'-'||v_cnt||'|';
78                END IF;
79            END LOOP;
80            
81            RETURN r_description;
82        END get_milestone_description;
83      
84    PROCEDURE insert_log(a_ms_name VARCHAR2,a_path VARCHAR2, a_description VARCHAR2)
85    IS
86        v_status VARCHAR2(3);
87    BEGIN
88       IF REGEXP_LIKE(a_description,'-#NO_DATA#')
89        THEN
90            v_status:='N';
91       ELSE
92            v_status:='Y';
93       END IF;
94       
95       INSERT INTO Milestones_Logs(
96            NR,
97            milestone_name,
98            execute_status,
99            path_of_script,
100            description)
101        VALUES (milestones_log_id.NEXTVAL,a_ms_name,a_path, v_status,a_description);
102    END;
103END milestone_logs;