Home » SQL & PL/SQL » SQL & PL/SQL » trigge on server erro
trigge on server erro [message #285322] Tue, 04 December 2007 04:28 Go to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
hi all
i create trigger on server error .
when i create it get error say (PL/SQL: ORA-00942: table or view does not exist) the view is v$session ?

CREATE OR REPLACE TRIGGER log_errors_trig
AFTER SERVERERROR ON DATABASE
DECLARE
var_user VARCHAR2 (30);
var_osuser VARCHAR2 (30);
var_machine VARCHAR2 (64);
var_process VARCHAR2 (8);
var_program VARCHAR2 (48);
BEGIN
SELECT s.username, s.osuser, s.machine, s.process, s.program
INTO var_user, var_osuser, var_machine, var_process, var_program
FROM v$session s wHERE s.audsid = USERENV ('sessionid');

END;
Re: trigge on server erro [message #285324 is a reply to message #285322] Tue, 04 December 2007 04:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Does your user have a direct grant on the view?

MHE
Re: trigge on server erro [message #285353 is a reply to message #285324] Tue, 04 December 2007 05:13 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
when i run block

Quote:

SQL> DECLARE
var_user VARCHAR2 (300);
var_osuser VARCHAR2 (300);
var_machine VARCHAR2 (640);
var_process VARCHAR2 (80);
var_program VARCHAR2 (480);
BEGIN
SELECT s.username, s.osuser, s.machine, s.process, s.program
INTO var_user, var_osuser, var_machine, var_process, var_program
FROM v$session s 11 wHERE s.audsid = USERENV ('sessionid');

dbms_output.put_line( var_user||' '|| var_osuser||' '||var_machine||' '||var_process||' '|| var
_program);

null;
END;
/
RAFEEK rafeek INFOCUS\RAFEEK

PL/SQL procedure successfully completed.




procedure successfully and return values

[Updated on: Tue, 04 December 2007 05:16]

Report message to a moderator

Re: trigge on server erro [message #285357 is a reply to message #285322] Tue, 04 December 2007 05:21 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

I can see one difference in the line

Quote:

FROM v$session s 11 wHERE s.audsid = USERENV ('sessionid');


to the original post.

Kiran.

[Updated on: Tue, 04 December 2007 05:21]

Report message to a moderator

Re: trigge on server erro [message #285359 is a reply to message #285353] Tue, 04 December 2007 05:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, my bad. I should have known that wasn't the problem here.

[edit]what Oracle version are we talking about? 9.2.0.4 by any chance?

MHE

[Updated on: Tue, 04 December 2007 05:23]

Report message to a moderator

Re: trigge on server erro [message #285467 is a reply to message #285322] Tue, 04 December 2007 14:56 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why use v$session which can have priviledge problems, when all the information you are asking for and more is avaiable using sys_context which is a system functon and will never have any rights issues. see the following link

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions122a.htm#1038178
Previous Topic: dbms_job.interval
Next Topic: Date Minus Time Query
Goto Forum:
  


Current Time: Sat Dec 10 03:26:05 CST 2016

Total time taken to generate the page: 0.05472 seconds