Re: Problem with v$mystat on trigger

On 3 Jan 2002 08:52:52 -0800, s.favard_at_nmf.fr (sylvain favard) wrote:

>I'm trying to build a trigger which gets the current session SID to
>put it into a specific table.
>This is my code :
>
>Create trigger TRIGGER_4 after logon on schema
>Declare
> pxsid NUMBER(10);
>Begin
> select distinct sid into pxsid from v$mystat;
> insert into G_SESSION (SID,USER_PX,JOB_PX) values
>(pxsid,'toto','titi');
>End;
>
>When I try to run this code with SQL*plus, the following message
>appears (translation of a french message):
>
>Warning : Trigger created with compilation errors
>
>This message is linked to the 'select distinct sid into pxsid from
>$mystat;' line.
>(The problem appears even if I'm connected as system/manager)
>
>Can you help me ?
>
>thank you.
>
>Sylvain Favard
>Network Mapping Facilities
>58 rue Georges Denizot
>34097 Montpellier cedex 5
>FRANCE
>
>tel : 04.99.236.223
>mail : s.favard_at_nmf.fr

Please always specify platform and version. I will assume 8i, as that is technology that hit the market already 3 years ago. 1 the session id won't be unique. The audsid however will. You can retrieve the audsid with the sys_context function, as you can retrieve other properties of your session. The doco I have on sys_context is a little confusing, you better look that up in the sql reference manual. 2 other than that you have privilege through a role. Roles are disabled during compilation of stored procedures and triggers. You can wrap the code in a procedure and create that procedure with invokers rights.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 03 2002 - 11:41:22 CST