rollback changes on ( TRIGGER,PACKAGE BODY,PACKAGE,PROCEDURE,FUNCTION )

when you run create or replace ( TRIGGER,PACKAGE BODY,PACKAGE,PROCEDURE,FUNCTION ) and you want to rollback

oraclesqlperformance-tuningv1.0.0.1
1 stars1 downloads60 views0 comments
By Mahmoud • Created

Code

(61 lines)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE TABLE DDL_AUDIT_SOURCE
(
    ID             NUMBER,
    OBJECT_NAME    VARCHAR2 (128),
    OBJECT_TYPE    VARCHAR2 (30),
    OWNER          VARCHAR2 (128),
    OLD_SOURCE     CLOB,
    CHANGE_DATE    DATE,
    OS_USER        VARCHAR2 (128),
    DB_USER        VARCHAR2 (128),
    MACHINE        VARCHAR2 (128)
);

CREATE SEQUENCE SYS.DDL_AUDIT_SOURC_ID START WITH 1
                                       MAXVALUE 9999999999999999999999999999
                                       MINVALUE 1
                                       NOCYCLE
                                       NOCACHE
                                       NOORDER
                                       NOKEEP
                                       GLOBAL;


GRANT SELECT ON SYS.DDL_AUDIT_SOURC_ID TO PUBLIC;

CREATE OR REPLACE TRIGGER CAPTURE_SOURCE_BEFORE_DDL
    BEFORE DDL
    ON DATABASE
DECLARE
    V_SQL   CLOB;
    V_ID    NUMBER;
BEGIN
    SELECT SYS.DDL_AUDIT_SOURC_ID.NEXTVAL INTO V_ID FROM DUAL;

    IF ORA_DICT_OBJ_TYPE IN ('TRIGGER',
                             'PACKAGE BODY',
                             'PACKAGE',
                             'PROCEDURE',
                             'FUNCTION')
    THEN
        SELECT XMLAGG (XMLELEMENT (E, TEXT) ORDER BY LINE).EXTRACT (
                   '//text()').GETCLOBVAL ()
          INTO V_SQL
          FROM ALL_SOURCE
         WHERE     NAME = ORA_DICT_OBJ_NAME
               AND TYPE = ORA_DICT_OBJ_TYPE
               AND OWNER = ORA_DICT_OBJ_OWNER;

        INSERT INTO DDL_AUDIT_SOURCE
             VALUES (V_ID,
                     ORA_DICT_OBJ_NAME,
                     ORA_DICT_OBJ_TYPE,
                     ORA_DICT_OBJ_OWNER,
                     V_SQL,
                     SYSDATE,
                     SYS_CONTEXT ('USERENV', 'OS_USER'),
                     SYS_CONTEXT ('USERENV', 'SESSION_USER'),
                     SYS_CONTEXT ('USERENV', 'HOST'));
    END IF;
END;
/

Usage Instructions

after any changes to ( TRIGGER,PACKAGE BODY,PACKAGE,PROCEDURE,FUNCTION )
select * from DDL_AUDIT_SOURCE;
you will find the old one under OLD_SOURCE

General Comments(0)

Tip: Click on a line number in the code to add a line-specific comment

No general comments yet. Be the first to comment!