BEFORE LOGOFF Trigger question

From: Ian <iwood_at_Xnovalistech.com>
Date: Thu, 11 Apr 2002 18:42:26 GMT
Message-ID: <mOkt8.56$51.38936@sapphire.mtt.net>

Hi,

I wish for a trigger to fire on logoff to remove some temporary tables that may have been created during a users session. It works great so long as it is compiled by SYS but I would prefer that it be owned by a lower level user but then I run into permission problems. I tried referencing USER_TABLES instead of ALL_TABLES but then the trigger reads only the USER_TABLES for the owner of the trigger. I also tried including an 'alter session set schema' command in there but I must be missing something.

Anybody lend a hand?

Ian.

/*create as the SYS user for now*/

create or replace trigger clean_grs_tables before logoff on database
declare

   type CURTYP is ref cursor;
   MYCURSOR curtyp;
   MYCURSOR_REC ALL_TABLES%rowtype;
   SESSION_COUNT integer;

   SQL_STMT       varchar2(200);
   CMD_STMT       varchar2(200);

begin

   select count(*) into SESSION_COUNT from V$SESSION where USERNAME = user;

   if SESSION_COUNT = 1 then

      SQL_STMT := 'select * from all_tables where table_name like ''U_TEMP%'' and owner = user';

         open MYCURSOR for SQL_STMT;
            loop
               fetch MYCURSOR into MYCURSOR_REC;
               exit when mycursor%notfound;
               begin
                  CMD_STMT := 'drop table ' || user || '.' ||
mycursor_rec.table_name;
                  execute immediate  CMD_STMT;
               end;
            end loop;
         close mycursor;

   end if;
end;
/
Received on Thu Apr 11 2002 - 13:42:26 CDT