Re: BEFORE LOGOFF Trigger question

On Thu, 11 Apr 2002 18:42:26 GMT, "Ian" <iwood_at_Xnovalistech.com> wrote:

>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;
>/
>
>
>
>
>
>

In 8i you have globally temporary tables which can be deleted automatically on commit. I would consider stop using conventional temporary tables. Also, because you have inline views now, you wouldn't usually need them.
Other than that, the solution for your problem is spelled out in this newsgroup almost weekly. I still take the trouble to answer them for the umpteenth time, but I'm more and more inclined to stop doing so. The fact the question is reposted clearly demonstrate the poster didn't do absolutely nothing to resolve the question himself, and as long as this behavior is being rewarded by people like me the problem of frequent reposts of FAQs will continue to exist. Jonathan Lewis has set up a faq site on www.jlcomp.demon.co.uk, there is www.orafaq.com, there is http://asktom.oracle.com, there is http://osi.oracle.com/~tkyte, the predecessor of asktom, where this question has been answered a few years ago, and of course there are this groups archives at http://groups.google.com As to the question the issue here is that roles are ignored during compilation of stored procedures as roles are volatile. The solution to this is either
- grant direct privilege on the affected object (pre 8i) - create the pl/sql with authid as invoker As you can call procedures from a trigger now, if you want to continue to use temporary tables, there are two solutions - either keep track of them in a pl/sql table when you create them, so you won't need the dictionary at all
- or convert your code to a procedure authid invoker and call the procedure from your trigger.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Apr 11 2002 - 14:01:47 CDT