?
Solved

Cannot select V$SESSION in before LOGOFF trigger

Posted on 2007-12-06
4
Medium Priority
?
1,059 Views
Last Modified: 2008-02-01
Do you know why I cannot compile this trigger?

CREATE OR REPLACE TRIGGER LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
DECLARE
BEGIN
  insert into TEST
  (USERNAME)
  select distinct USERNAME
  from V$SESSION;
END;
/
0
Comment
Question by:Zopilote
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 20422654
Not sure "why" it does that... but try this instead:

create trigger logoff_trigger
before logoff on database
begin
  insert into test values(sys_context('userenv','session_user'));
end;
/
0
 
LVL 5

Author Comment

by:Zopilote
ID: 20423682
Problem solved. No direct grant.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 20423683
whomever owns the trigger doesn't have select access on v$session.

I was able to compile your trigger fine and it ran correctly as well.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20423687
sorry, didn't see your post.  yes, you are correct, that's "why"

sys_context is a good workaround too
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question