What is wrong with this trigger code?

662763

Hi folks,

Just wondering whether I might be missing something here about Oracle 9i, that one of you might notice, either from the syntax perspective, or regarding a bug? The code below compiles in the database I have, but the trigger does not fire, and no data is being inserted into the tables. The trigger is owned by sys, and the insert statements are in dynamic sql because I don't want the trigger not to compile if ever the tables are deleted from the b1dev schema.

Incidentally, this same trigger works well on Oracle 10.2.1.0 and Oracle 11.1.0. Any feedback would be much appreciated.

CREATE OR REPLACE TRIGGER cc_LogOff_Trig
    BEFORE LogOff ON DATABASE
    DECLARE
       LogOff_sid   PLS_INTEGER;
       LogOff_Time  DATE := SYSDATE;
       Table_1      VARCHAR2(30) := 'CC_SESSION_EVENT_HISTORY';
       Table_2      VARCHAR2(30) := 'CC_SESSTAT_HISTORY';
       Table_Count  NUMBER;
       v_sql1       VARCHAR2(4000);
       v_sql2       VARCHAR2(4000);
    BEGIN
      SELECT sId
      INTO   LogOff_sId
      FROM   sys.v$MysTat
      WHERE  ROWNUM < 2;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_1
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql1 := 'INSERT INTO bdev.cc_session_event_history(sid,   serial#,   username,   osuser,   session_process_addr,   os_client_process_id,   logon_time,   type,   event,    total_waits,   total_timeouts,   time_waited_csecs,   
  average_wait_csecs,   max_wait_csecs,    logoff_timestamp) SELECT se.sid, s.serial#, s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, 
  se.max_wait, '''
                  ||LogOff_Time
                  ||''' FROM sys.v$session_event se, sys.v$session s WHERE se.sid = s.sid AND s.username = '''
                  ||LogIn_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql1;
      END IF;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_2
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql2 := 'INSERT INTO bdev.cc_sesstat_history(username,     osuser,   sid,   serial#,   session_process_addr,   os_client_process_id,   logon_time,   statistic#,   name,   VALUE,   logoff_timestamp)   SELECT s.username,    
  s.osuser,      ss.sid,     s.serial#,     s.paddr,     s.process,     s.logon_time,     ss.statistic#,     sn.name,     ss.VALUE,     '''
                  ||LogOff_Time
                  ||'''   FROM sys.v$sesstat ss,     sys.v$statname sn,     sys.v$session s  
  WHERE ss.statistic# = sn.statistic#    AND ss.sid = s.sid    AND sn.name IN(''CPU used when call started'',   ''CPU used by this session'',   ''recursive cpu usage'',   ''parse time cpu'')    
  AND s.username = '''
                  ||Login_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql2;
      END IF;
      
      COMMIT;
    END;

desc cc_session_event_history;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ------------------------- 
SID                                     NUMBER                                                                                                                                                                                        
SERIAL#                                 NUMBER                                                                                                                                                                                        
USERNAME                                VARCHAR2(30)                                                                                                                                                                                  
OSUSER                                  VARCHAR2(30)                                                                                                                                                                                  
SESSION_PROCESS_ADDR                    RAW(0)                                                                                                                                                                                        
OS_CLIENT_PROCESS_ID                    VARCHAR2(24)                                                                                                                                                                                  
LOGON_TIME                              DATE                                                                                                                                                                                          
TYPE                                    VARCHAR2(10)                                                                                                                                                                                  
EVENT                                   VARCHAR2(64)                                                                                                                                                                                  
EVENT#                                  NUMBER                                                                                                                                                                                        
TOTAL_WAITS                             NUMBER                                                                                                                                                                                        
TOTAL_TIMEOUTS                          NUMBER                                                                                                                                                                                        
TIME_WAITED_CSECS                       NUMBER                                                                                                                                                                                        
AVERAGE_WAIT_CSECS                      NUMBER                                                                                                                                                                                        
MAX_WAIT_CSECS                          NUMBER                                                                                                                                                                                        
TIME_WAITED_MICRO                       NUMBER                                                                                                                                                                                        
LOGOFF_TIMESTAMP                        DATE                                                                                                                                                                                          

17 rows selected

desc cc_sesstat_history;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ----------------
USERNAME                                VARCHAR2(30)                                                                                                                                                                                  
OSUSER                                  VARCHAR2(30)                                                                                                                                                                                  
SID                                     NUMBER                                                                                                                                                                                        
SERIAL#                                 NUMBER                                                                                                                                                                                        
SESSION_PROCESS_ADDR                    RAW(0)                                                                                                                                                                                        
OS_CLIENT_PROCESS_ID                    VARCHAR2(24)                                                                                                                                                                                  
LOGON_TIME                              DATE                                                                                                                                                                                          
STATISTIC#                              NUMBER                                                                                                                                                                                        
NAME                                    VARCHAR2(64)                                                                                                                                                                                  
VALUE                                   NUMBER                                                                                                                                                                                        
LOGOFF_TIMESTAMP                        DATE                                                                                                                                                                                          

11 rows selected

bscalzo

As a start, you might want to try adding some EXCEPTION code logic - e.g. maybe post some data to a table or file via UTL_FILE ....

You also could try running a procedure'ized version of this in SQL Developer's debugger to see if anything interesting turns up ......

662763

I have taken out the 'commit' and have added the 'when others then null' exception clause, but I would imagine there is a better way to do it than this? I don't want to do an insert into another table either. With Oracle 11g, this gives me the compile time warning....

CREATE OR REPLACE TRIGGER cc_LogOff_Trig
    BEFORE LogOff ON DATABASE
    DECLARE
       LogOff_sid   PLS_INTEGER;
       LogOff_Time  DATE := SYSDATE;
       Table_1      VARCHAR2(30) := 'CC_SESSION_EVENT_HISTORY';
       Table_2      VARCHAR2(30) := 'CC_SESSTAT_HISTORY';
       Table_Count  NUMBER;
       v_sql1       VARCHAR2(4000);
       v_sql2       VARCHAR2(4000);
    BEGIN
      SELECT sId
      INTO   LogOff_sId
      FROM   sys.v$MysTat
      WHERE  ROWNUM < 2;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_1
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql1 := 'INSERT INTO bdev.cc_session_event_history(sid,   serial#,   username,   osuser,   session_process_addr,   os_client_process_id,   logon_time,   type,   event,    total_waits,   total_timeouts,   time_waited_csecs,   
  average_wait_csecs,   max_wait_csecs,    logoff_timestamp) SELECT se.sid, s.serial#, s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, 
  se.max_wait, '''
                  ||LogOff_Time
                  ||''' FROM sys.v$session_event se, sys.v$session s WHERE se.sid = s.sid AND s.username = '''
                  ||LogIn_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql1;
      END IF;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_2
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql2 := 'INSERT INTO bdev.cc_sesstat_history(username,     osuser,   sid,   serial#,   session_process_addr,   os_client_process_id,   logon_time,   statistic#,   name,   VALUE,   logoff_timestamp)   SELECT s.username,    
  s.osuser,      ss.sid,     s.serial#,     s.paddr,     s.process,     s.logon_time,     ss.statistic#,     sn.name,     ss.VALUE,     '''
                  ||LogOff_Time
                  ||'''   FROM sys.v$sesstat ss,     sys.v$statname sn,     sys.v$session s  
  WHERE ss.statistic# = sn.statistic#    AND ss.sid = s.sid    AND sn.name IN(''CPU used when call started'',   ''CPU used by this session'',   ''recursive cpu usage'',   ''parse time cpu'')    
  AND s.username = '''
                  ||Login_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql2;
      END IF;
  EXCEPTION
      WHEN OTHERS THEN 
         NULL;
   
    END;

Edited by: efachim on Jan 6, 2009 11:30 AM

David Krch-Oracle

You will probably not be able to find why is your trigger not running as long as you use "WHEN OTHERS THEN NULL". Using this means "I don't care if anything goes wrong".
Try inserting the error message (SQLERRM) into some table or a file (as Bscalco suggested) in your exception handler.
Do it at least during the debugging, but it is better not to mask OTHERS in production also. I bet that as soon as you will see the error message, you will know what's wrong. If not, paste the whole error message here.

I would also comment on using this construction (but changing this will probably not solve your problem):
SELECT COUNT(* )
INTO Table_Count
FROM dba_Objects
WHERE Object_Name = Table_1
AND Object_Type = 'TABLE';

IF Table_Count = 1 THEN
.....


I have three comments
a)by using COUNT(*) you are saying "Go through the whole table and find all objects like ...." - but you are only interested in finding the first row that satisfies the condition (to know, that object exists), trying to find some other rows is just wasted server capacity. Look here for probably a better solution http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1525380674899

b)DBA_OBJECTS is a list of all objects in database - in all schemas. Let's imagine, that you create this trigger in SYS schema. There is no CC_SESSION_EVENT_HISTORY in SYS schema. but there is a table with the same name in some other schema (for example HR.CC_SESSION_EVENT_HISTORY) - your SELECT will return 1 table found, but the INSERT will fail with
ORA-00942: table or view does not exist
If you really want to SELECT from data dictionary first, you should use USER_OBJECTS, or use DBA_OBJECTS but add WHERE OWNER='....'

c)I would probably don't test for existence of table at all - just try running the INSERT and catch ORA-00942 exception. To mimic your behaviour, you will need a separate BEGIN/EXCEPTION/END block around each INSERT so that the second INSERT will run even when the first fails.

662763

Hi Dkrch,

Thanks for the feedback. Even as it is, the trigger now works (for Oracle 9i, anyway). But I need to have a similar or identical trigger working for Oracle 10g/11g. The reason I am using the 'When others then null' clause is because the trigger is owned by sys, and I would like to avoid a situation where no one can log in to the database if the trigger fails. However, right now, for Oracle 11g, it compiles with a warning since this is a new 11g feature (Maybe this also occurs with 10g but I have not tested it there yet. I had to put the insert statements in dynamic sql to avoid the trigger failing if those tables donot exist or have been deleted. This is why I donot want to include another insert statement in the exception section in the event of an exception. Does that make sense?

Your other points are also appreciated.

thks, Efachim

Solomon Yakobson

efachim wrote:
Hi Dkrch,

The reason I am using the 'When others then null' clause is because the trigger is owned by sys, and I would like to avoid a situation where no one can log in to the database if the trigger fails.

It is not how database level logon triggers work. Users with ADMINISTER DATABASE TRIGGER (which obviously includes SYSDBA) can still connect. For users with ADMINISTER DATABASE TRIGGER error is not raised, instead error message is written to alert.log and a trace file in user_dump_dest.

SY.