OraFAQ Forum: SQL & PL/SQL » trigge on server erro

Home » SQL & PL/SQL » SQL & PL/SQL » trigge on server erro

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trigge on server erro [message #285322]

Tue, 04 December 2007 04:28

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;

Report message to a moderator

Re: trigge on server erro [message #285324 is a reply to message #285322]

Tue, 04 December 2007 04:30

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Does your user have a direct grant on the view?

MHE

Report message to a moderator

Re: trigge on server erro [message #285353 is a reply to message #285324]

Tue, 04 December 2007 05:13

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

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

Maaher
Messages: 7065
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

Bill B
Messages: 1971
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

Report message to a moderator

Previous Topic:	dbms_job.interval
Next Topic:	Date Minus Time Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 11:02:27 CST 2024