Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, bharath.

Asked: January 07, 2017 - 5:53 am UTC

Last updated: January 08, 2017 - 11:57 am UTC

Version: 10g

Viewed 1000+ times

You Asked

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;  

and Connor said...

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>



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library