Re: QUESTION: raise_application_error results in ORA-00604...?

On 25 May 2006 11:46:26 -0700, "BD" <bobby_dread_at_hotmail.com> wrote:

>>5 Currently you don't allow sys to connect. That is going to be a problem.
>Sys is able to connect (I just tried). The code is intended to only
>prevent the BROWSE_ID from logging on under the specific circumstances.
>The code grabs USER from DUAL. In the case of sys, USER from DUAL is
>'SYS', not BROWSE_ID.
>
>>2 Many of your selects from v$session can and should be replaced by calling the sys_context function
>An earlier iteration of this trigger did use sys_context, but a
>co-worker recommended v$session. Can you give me a sense as to why
>sys_context is better than v$session?
>

My concern would be that it would avoid explicit grants to the v_$session view, and it would avoid an explicit select.
>>3 audsid is always 0 for user SYS and any user with SYSDBA. Your code is going to fail, as all SYS users have the same audsid, 0
>I'll have to look closer at that - I'm not yet quite sure what you're
>telling me, but I'll look into it.
>

Your select into's should end into exception as they return more than one row. SYS is always connected (remember the background sessions) so there will be always multiple sessions with audsid=0 You would need to use v$mystat. (... and sid = (select sid from v$mystat) )
Yet another reason to avoid the select
>Thanks!!

--
Sybrand Bakker, Senior Oracle DBA