i have table and packages like this,but iam getting error while executing it.so please help me:
CREATE TABLE LOGGING_DATA_HDR
(
LOG_ID NUMBER,
TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL,
PK_DATA VARCHAR2 (500 BYTE),
ROW_ID ROWID NOT NULL,
LOG_DATE DATE NOT NULL,
OPERATION_TYPE VARCHAR2 (1 BYTE) NOT NULL,
DB_USER VARCHAR2 (100 BYTE),
CLIENT_IP VARCHAR2 (40 BYTE),
CLIENT_HOST VARCHAR2 (100 BYTE),
CLIENT_OS_USER VARCHAR2 (100 BYTE),
APP_USER VARCHAR2 (50 BYTE)
);
ALTER TABLE LOGGING_DATA_HDR ADD (
CONSTRAINT LOGGING_DATA_HDR_PK
PRIMARY KEY
(LOG_ID)
);
CREATE TABLE LOGGING_DATA_DTL
(
LOG_ID NUMBER,
COLUMN_NAME VARCHAR2 (30 CHAR),
OLD_VALUE VARCHAR2 (4000 BYTE),
NEW_VALUE VARCHAR2 (4000 BYTE)
);
ALTER TABLE LOGGING_DATA_DTL ADD (
CONSTRAINT LOGGING_DATA_DTL_PK
PRIMARY KEY
(LOG_ID, COLUMN_NAME));
CREATE SEQUENCE LOG_ID_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
CREATE OR REPLACE PACKAGE MAHMOUD_LOGGING
AS
TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;
GC$APP_USER LOGGING_DATA_HDR.APP_USER%TYPE;
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2);
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE;
END MAHMOUD_LOGGING;
/
CREATE OR REPLACE PACKAGE BODY MAHMOUD_LOGGING
AS
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2)
IS
LN$LOG_ID LOGGING_DATA_HDR.LOG_ID%TYPE;
BEGIN
SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;
INSERT INTO LOGGING_DATA_HDR (LOG_ID,
TABLE_NAME,
PK_DATA,
ROW_ID,
LOG_DATE,
OPERATION_TYPE,
DB_USER,
CLIENT_IP,
CLIENT_HOST,
CLIENT_OS_USER,
APP_USER)
VALUES (LN$LOG_ID,
IN_TABLE_NAME,
GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),
IN_ROWID,
SYSDATE,
IN_OPERATION_TYPE,
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'ip_address'),
SYS_CONTEXT ('USERENV', 'host'),
SYS_CONTEXT ('USERENV', 'os_user'),
GET_APP_USER);
IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0
THEN
FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST
LOOP
IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL
AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL
OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)
THEN
INSERT INTO LOGGING_DATA_DTL (LOG_ID,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE)
VALUES (LN$LOG_ID,
IN_ARRAY (INDX).COLUMN_NAME,
IN_ARRAY (INDX).OLD_VALUE,
IN_ARRAY (INDX).NEW_VALUE);
END IF;
END LOOP;
END IF;
END ADD_LOG;
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2
IS
LC$TRIGGER_STMT VARCHAR2 (4000);
CURSOR LCUR$COLUMNS
IS
SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = IN_TABLE_NAME
ORDER BY COLUMN_ID;
BEGIN
LC$TRIGGER_STMT :=
'CREATE OR REPLACE TRIGGER '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG '
|| CHR (10)
|| 'AFTER INSERT OR UPDATE OR DELETE'
|| CHR (10)
|| 'ON '
|| IN_TABLE_NAME
|| ' FOR EACH ROW '
|| CHR (10)
|| 'DECLARE '
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'LC$OPERATION VARCHAR2 (1);'
|| CHR (10)
|| 'PROCEDURE ADD_ELEMENT ('
|| CHR (10)
|| 'IN_OPERATION VARCHAR2,'
|| CHR (10)
|| 'IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'
|| CHR (10)
|| 'IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,'
|| CHR (10)
|| 'IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)'
|| CHR (10)
|| 'IS'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'
|| CHR (10)
|| 'THEN'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL.EXTEND;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'END IF;'
|| CHR (10)
|| 'END ADD_ELEMENT;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL := MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();'
|| CHR (10)
|| 'LC$OPERATION :='
|| CHR (10)
|| 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'
|| CHR (10);
FOR LREC$COLUMNS IN LCUR$COLUMNS
LOOP
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' ADD_ELEMENT (LC$OPERATION,'''
|| LREC$COLUMNS.COLUMN_NAME
|| ''',:OLD.'
|| LREC$COLUMNS.COLUMN_NAME
|| ',:NEW.'
|| LREC$COLUMNS.COLUMN_NAME
|| ');'
|| CHR (10);
END LOOP;
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'''
|| IN_TABLE_NAME
|| ''',:NEW.ROWID,LC$OPERATION);'
|| CHR (10)
|| 'END '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG ;';
RETURN LC$TRIGGER_STMT;
END GENERATE_TRIGGER;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
LC$COLUMNS VARCHAR2 (512) := '';
LC$KEY VARCHAR2 (512);
CURSOR LCUR$COLUMNS (
IN_TABLE_NAME VARCHAR2)
IS
SELECT CON_C.COLUMN_NAME
FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON
WHERE CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME = IN_TABLE_NAME
ORDER BY POSITION;
BEGIN
FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)
LOOP
LC$COLUMNS :=
LC$COLUMNS
|| LREC$COLUMNS.COLUMN_NAME
|| '||'''
|| IN_DELIMETER
|| '''||';
END LOOP;
LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');
EXECUTE IMMEDIATE
'SELECT '
|| LC$COLUMNS
|| ' FROM '
|| IN_TABLE
|| ' WHERE ROWID='''
|| IN_ROWID
|| ''''
INTO LC$KEY;
RETURN LC$KEY;
END GET_COMPOSITE_KEY;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)
IS
BEGIN
GC$APP_USER := IN_APP_USER;
END SET_APP_USER;
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE
IS
BEGIN
RETURN GC$APP_USER;
END GET_APP_USER;
END MAHMOUD_LOGGING;
/
BEGIN
DBMS_OUTPUT.PUT_LINE (MAHMOUD_LOGGING.GENERATE_TRIGGER ('EMP'));
END;
CREATE OR REPLACE TRIGGER EMP_LOGTRG
AFTER INSERT OR UPDATE OR DELETE
ON EMP FOR EACH ROW
DECLARE
LT$LOGGING_DATA_DTL MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;
LC$OPERATION VARCHAR2 (1);
PROCEDURE ADD_ELEMENT (
IN_OPERATION VARCHAR2,
IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,
IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,
IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)
IS
LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;
BEGIN
IF NOT (IN_OPERATION = 'U' AND IN_NEW_VALUE = IN_OLD_VALUE)
THEN
LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;
LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;
LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;
LT$LOGGING_DATA_DTL.EXTEND;
LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;
END IF;
END ADD_ELEMENT;
BEGIN
LT$LOGGING_DATA_DTL := MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();
LC$OPERATION :=
CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;
ADD_ELEMENT (LC$OPERATION,'EMPNO',:OLD.EMPNO,:NEW.EMPNO);
ADD_ELEMENT (LC$OPERATION,'ENAME',:OLD.ENAME,:NEW.ENAME);
ADD_ELEMENT (LC$OPERATION,'JOB',:OLD.JOB,:NEW.JOB);
ADD_ELEMENT (LC$OPERATION,'MGR',:OLD.MGR,:NEW.MGR);
ADD_ELEMENT (LC$OPERATION,'HIREDATE',:OLD.HIREDATE,:NEW.HIREDATE);
ADD_ELEMENT (LC$OPERATION,'SAL',:OLD.SAL,:NEW.SAL);
ADD_ELEMENT (LC$OPERATION,'COMM',:OLD.COMM,:NEW.COMM);
ADD_ELEMENT (LC$OPERATION,'DEPTNO',:OLD.DEPTNO,:NEW.DEPTNO);
MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'EMP',:NEW.ROWID,LC$OPERATION);
END EMP_LOGTRG ;
EXEC MAHMOUD_LOGGING.SET_APP_USER('Mahmoud A. Mahmoud');
INSERT INTO emp (EMPNO,
ENAME,
JOB,
DEPTNO)
VALUES (150,
'Mahmoud',
'Dev',
10);
UPDATE emp
SET job = 'S. Dev', deptno = 20
WHERE empno = 150;
DELETE FROM emp
WHERE empno = 150;
COMMIT;
You'll need to *tell* us the error (ie, show us an example of your execution output) because it works fine for me here
SQL> drop TABLE LOGGING_DATA_DTL purge;
drop TABLE LOGGING_DATA_DTL purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop SEQUENCE LOG_ID_SEQ ;
drop SEQUENCE LOG_ID_SEQ
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> drop TRIGGER EMP_LOGTRG ;
drop TRIGGER EMP_LOGTRG
*
ERROR at line 1:
ORA-04080: trigger 'EMP_LOGTRG' does not exist
SQL>
SQL> CREATE TABLE LOGGING_DATA_HDR
2 (
3 LOG_ID NUMBER,
4 TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL,
5 PK_DATA VARCHAR2 (500 BYTE),
6 ROW_ID ROWID NOT NULL,
7 LOG_DATE DATE NOT NULL,
8 OPERATION_TYPE VARCHAR2 (1 BYTE) NOT NULL,
9 DB_USER VARCHAR2 (100 BYTE),
10 CLIENT_IP VARCHAR2 (40 BYTE),
11 CLIENT_HOST VARCHAR2 (100 BYTE),
12 CLIENT_OS_USER VARCHAR2 (100 BYTE),
13 APP_USER VARCHAR2 (50 BYTE)
14 );
Table created.
SQL> ALTER TABLE LOGGING_DATA_HDR ADD (
2 CONSTRAINT LOGGING_DATA_HDR_PK
3 PRIMARY KEY
4 (LOG_ID)
5 );
Table altered.
SQL> CREATE TABLE LOGGING_DATA_DTL
2 (
3 LOG_ID NUMBER,
4 COLUMN_NAME VARCHAR2 (30 CHAR),
5 OLD_VALUE VARCHAR2 (4000 BYTE),
6 NEW_VALUE VARCHAR2 (4000 BYTE)
7 );
Table created.
SQL> ALTER TABLE LOGGING_DATA_DTL ADD (
2 CONSTRAINT LOGGING_DATA_DTL_PK
3 PRIMARY KEY
4 (LOG_ID, COLUMN_NAME));
Table altered.
SQL> CREATE SEQUENCE LOG_ID_SEQ
2 START WITH 1
3 MAXVALUE 99999999999
4 MINVALUE 1
5 NOCYCLE
6 NOCACHE
7 NOORDER;
Sequence created.
SQL> CREATE OR REPLACE PACKAGE MAHMOUD_LOGGING
2 AS
3 TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;
4 GC$APP_USER LOGGING_DATA_HDR.APP_USER%TYPE;
5 PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
6 IN_TABLE_NAME VARCHAR2,
7 IN_ROWID ROWID,
8 IN_OPERATION_TYPE VARCHAR2);
9 FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
10 RETURN VARCHAR2;
11 FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
12 IN_ROWID ROWID,
13 IN_DELIMETER VARCHAR2 DEFAULT '-')
14 RETURN VARCHAR2;
15 PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);
16 FUNCTION GET_APP_USER
17 RETURN LOGGING_DATA_HDR.APP_USER%TYPE;
18 END MAHMOUD_LOGGING;
19 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY MAHMOUD_LOGGING
2 AS
3 PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
4 IN_TABLE_NAME VARCHAR2,
5 IN_ROWID ROWID,
6 IN_OPERATION_TYPE VARCHAR2)
7 IS
8 LN$LOG_ID LOGGING_DATA_HDR.LOG_ID%TYPE;
9 BEGIN
10 SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;
11 INSERT INTO LOGGING_DATA_HDR (LOG_ID,
12 TABLE_NAME,
13 PK_DATA,
14 ROW_ID,
15 LOG_DATE,
16 OPERATION_TYPE,
17 DB_USER,
18 CLIENT_IP,
19 CLIENT_HOST,
20 CLIENT_OS_USER,
21 APP_USER)
22 VALUES (LN$LOG_ID,
23 IN_TABLE_NAME,
24 GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),
25 IN_ROWID,
26 SYSDATE,
27 IN_OPERATION_TYPE,
28 SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
29 SYS_CONTEXT ('USERENV', 'ip_address'),
30 SYS_CONTEXT ('USERENV', 'host'),
31 SYS_CONTEXT ('USERENV', 'os_user'),
32 GET_APP_USER);
33 IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0
34 THEN
35 FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST
36 LOOP
37 IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL
38 AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL
39 OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)
40 THEN
41 INSERT INTO LOGGING_DATA_DTL (LOG_ID,
42 COLUMN_NAME,
43 OLD_VALUE,
44 NEW_VALUE)
45 VALUES (LN$LOG_ID,
46 IN_ARRAY (INDX).COLUMN_NAME,
47 IN_ARRAY (INDX).OLD_VALUE,
48 IN_ARRAY (INDX).NEW_VALUE);
49 END IF;
50 END LOOP;
51 END IF;
52 END ADD_LOG;
53 FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
54 RETURN VARCHAR2
55 IS
56 LC$TRIGGER_STMT VARCHAR2 (4000);
57 CURSOR LCUR$COLUMNS
58 IS
59 SELECT COLUMN_NAME
60 FROM USER_TAB_COLS
61 WHERE TABLE_NAME = IN_TABLE_NAME
62 ORDER BY COLUMN_ID;
63 BEGIN
64 LC$TRIGGER_STMT :=
65 'CREATE OR REPLACE TRIGGER '
66 || SUBSTR (IN_TABLE_NAME, 1, 23)
67 || '_LOGTRG '
68 || CHR (10)
69 || 'AFTER INSERT OR UPDATE OR DELETE'
70 || CHR (10)
71 || 'ON '
72 || IN_TABLE_NAME
73 || ' FOR EACH ROW '
74 || CHR (10)
75 || 'DECLARE '
76 || CHR (10)
77 || 'LT$LOGGING_DATA_DTL MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;'
78 || CHR (10)
79 || 'LC$OPERATION VARCHAR2 (1);'
80 || CHR (10)
81 || 'PROCEDURE ADD_ELEMENT ('
82 || CHR (10)
83 || 'IN_OPERATION VARCHAR2,'
84 || CHR (10)
85 || 'IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'
86 || CHR (10)
87 || 'IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,'
88 || CHR (10)
89 || 'IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)'
90 || CHR (10)
91 || 'IS'
92 || CHR (10)
93 || 'LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;'
94 || CHR (10)
95 || 'BEGIN'
96 || CHR (10)
97 || ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'
98 || CHR (10)
99 || 'THEN'
100 || CHR (10)
101 || 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'
102 || CHR (10)
103 || 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'
104 || CHR (10)
105 || 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'
106 || CHR (10)
107 || 'LT$LOGGING_DATA_DTL.EXTEND;'
108 || CHR (10)
109 || 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'
110 || CHR (10)
111 || 'END IF;'
112 || CHR (10)
113 || 'END ADD_ELEMENT;'
114 || CHR (10)
115 || 'BEGIN'
116 || CHR (10)
117 || 'LT$LOGGING_DATA_DTL := MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();'
118 || CHR (10)
119 || 'LC$OPERATION :='
120 || CHR (10)
121 || 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'
122 || CHR (10);
123 FOR LREC$COLUMNS IN LCUR$COLUMNS
124 LOOP
125 LC$TRIGGER_STMT :=
126 LC$TRIGGER_STMT
127 || ' ADD_ELEMENT (LC$OPERATION,'''
128 || LREC$COLUMNS.COLUMN_NAME
129 || ''',:OLD.'
130 || LREC$COLUMNS.COLUMN_NAME
131 || ',:NEW.'
132 || LREC$COLUMNS.COLUMN_NAME
133 || ');'
134 || CHR (10);
135 END LOOP;
136 LC$TRIGGER_STMT :=
137 LC$TRIGGER_STMT
138 || ' MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'''
139 || IN_TABLE_NAME
140 || ''',:NEW.ROWID,LC$OPERATION);'
141 || CHR (10)
142 || 'END '
143 || SUBSTR (IN_TABLE_NAME, 1, 23)
144 || '_LOGTRG ;';
145 RETURN LC$TRIGGER_STMT;
146 END GENERATE_TRIGGER;
147 FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
148 IN_ROWID ROWID,
149 IN_DELIMETER VARCHAR2 DEFAULT '-')
150 RETURN VARCHAR2
151 IS
152 PRAGMA AUTONOMOUS_TRANSACTION;
153 LC$COLUMNS VARCHAR2 (512) := '';
154 LC$KEY VARCHAR2 (512);
155 CURSOR LCUR$COLUMNS (
156 IN_TABLE_NAME VARCHAR2)
157 IS
158 SELECT CON_C.COLUMN_NAME
159 FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON
160 WHERE CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME
161 AND CON.CONSTRAINT_TYPE = 'P'
162 AND CON.TABLE_NAME = IN_TABLE_NAME
163 ORDER BY POSITION;
164 BEGIN
165 FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)
166 LOOP
167 LC$COLUMNS :=
168 LC$COLUMNS
169 || LREC$COLUMNS.COLUMN_NAME
170 || '||'''
171 || IN_DELIMETER
172 || '''||';
173 END LOOP;
174 LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');
175 EXECUTE IMMEDIATE
176 'SELECT '
177 || LC$COLUMNS
178 || ' FROM '
179 || IN_TABLE
180 || ' WHERE ROWID='''
181 || IN_ROWID
182 || ''''
183 INTO LC$KEY;
184 RETURN LC$KEY;
185 END GET_COMPOSITE_KEY;
186 PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)
187 IS
188 BEGIN
189 GC$APP_USER := IN_APP_USER;
190 END SET_APP_USER;
191 FUNCTION GET_APP_USER
192 RETURN LOGGING_DATA_HDR.APP_USER%TYPE
193 IS
194 BEGIN
195 RETURN GC$APP_USER;
196 END GET_APP_USER;
197 END MAHMOUD_LOGGING;
198 /
Package body created.
SQL> set serverout on
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE (MAHMOUD_LOGGING.GENERATE_TRIGGER ('EMP'));
3 END;
4 /
CREATE OR REPLACE TRIGGER EMP_LOGTRG
AFTER INSERT OR UPDATE OR DELETE
ON EMP FOR EACH ROW
DECLARE
LT$LOGGING_DATA_DTL
MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;
LC$OPERATION VARCHAR2 (1);
PROCEDURE ADD_ELEMENT (
IN_OPERATION
VARCHAR2,
IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,
IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,
IN_NEW_VALUE
LOGGING_DATA_DTL.NEW_VALUE%TYPE)
IS
LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;
BEGIN
IF NOT (IN_OPERATION = 'U' AND
IN_NEW_VALUE = IN_OLD_VALUE)
THEN
LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;
LR$LOGGING_DATA_DTL.OLD_VALUE
:=IN_OLD_VALUE;
LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;
LT$LOGGING_DATA_DTL.EXTEND;
LT$LOGGING_DATA_DTL
(LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;
END IF;
END ADD_ELEMENT;
BEGIN
LT$LOGGING_DATA_DTL :=
MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();
LC$OPERATION :=
CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;
MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'EMP',:NEW.ROWID,LC$OPERATION);
END EMP_LOGTRG ;
PL/SQL procedure successfully completed.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table emp as select * from scott.emp;
Table created.
SQL> alter table emp add primary key ( empno );
Table altered.
SQL> CREATE OR REPLACE TRIGGER EMP_LOGTRG
2 AFTER INSERT OR UPDATE OR DELETE
3 ON EMP FOR EACH ROW
4 DECLARE
5 LT$LOGGING_DATA_DTL
6 MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;
7 LC$OPERATION VARCHAR2 (1);
8 PROCEDURE ADD_ELEMENT (
9 IN_OPERATION
10 VARCHAR2,
11 IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,
12 IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,
13 IN_NEW_VALUE
14 LOGGING_DATA_DTL.NEW_VALUE%TYPE)
15 IS
16 LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;
17 BEGIN
18 IF NOT (IN_OPERATION = 'U' AND
19 IN_NEW_VALUE = IN_OLD_VALUE)
20 THEN
21 LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;
22 LR$LOGGING_DATA_DTL.OLD_VALUE
23 :=IN_OLD_VALUE;
24 LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;
25 LT$LOGGING_DATA_DTL.EXTEND;
26 LT$LOGGING_DATA_DTL
27 (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;
28 END IF;
29 END ADD_ELEMENT;
30 BEGIN
31 LT$LOGGING_DATA_DTL :=
32 MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();
33 LC$OPERATION :=
34 CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;
35
36 MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'EMP',:NEW.ROWID,LC$OPERATION);
37 END EMP_LOGTRG ;
38 /
Trigger created.
SQL> EXEC MAHMOUD_LOGGING.SET_APP_USER('Mahmoud A. Mahmoud');
PL/SQL procedure successfully completed.
SQL> INSERT INTO emp (EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO)
5 VALUES (150,
6 'Mahmoud',
7 'Dev',
8 10);
1 row created.
SQL> UPDATE emp
2 SET job = 'S. Dev', deptno = 20
3 WHERE empno = 150;
1 row updated.
SQL> DELETE FROM emp
2 WHERE empno = 150;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> select * from LOGGING_DATA_HDR;
LOG_ID TABLE_NAME
---------- ------------------------------
PK_DATA
----------------------------------------------------------------------------------------------------------------------------------
ROW_ID LOG_DATE O
------------------ --------- -
DB_USER
----------------------------------------------------------------------------------------------------
CLIENT_IP
----------------------------------------
CLIENT_HOST
----------------------------------------------------------------------------------------------------
CLIENT_OS_USER
----------------------------------------------------------------------------------------------------
APP_USER
--------------------------------------------------
1 EMP
AAAv+eAAGAAAsp3AAA 08-JAN-17 I
MCDONAC
ORADEV\COMCDONA-AU
COMCDONA-AU\comcdona
Mahmoud A. Mahmoud
2 EMP
AAAv+eAAGAAAsp3AAA 08-JAN-17 U
MCDONAC
ORADEV\COMCDONA-AU
COMCDONA-AU\comcdona
Mahmoud A. Mahmoud
3 EMP
AAAv+eAAGAAAsp3AAA 08-JAN-17 D
MCDONAC
ORADEV\COMCDONA-AU
COMCDONA-AU\comcdona
Mahmoud A. Mahmoud
3 rows selected.
SQL> select * from LOGGING_DATA_DTL;
no rows selected
SQL>