DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Trigger: ORA-00942: table or view does not exist

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Exclamation Trigger: ORA-00942: table or view does not exist

    Hi, I have a LOGON trigger with the code bellow which causes me error. I will appreciate it vey much if you could give some advices ..
    Oracle 11.2

    PHP Code:
    create or replace TRIGGER SYSTEM.LOGIN_OMEGA_RISK
    AFTER LOGON ON OMEGA_RISK
    .schema
    declare
    prog VARCHAR2(200);
    BEGIN
        SELECT program INTO prog FROM sys
    .v_$session WHERE audsid=USERENV('sessionid') and rownum<=1;
        if(
    prog in ('JDBC Thin Client')) then 
        RAISE_APPLICATION_ERROR
    (-20001'The program '||prog||' is not allowed to login to '||user||' Oracle Schema.');
        else
        
    execute immediate 'alter session set current_schema = OMEGA_RISK_OWN';
        
    end if;
    END;
    /
    show errors;

    Errors for TRIGGER SYSTEM.LOGIN_OMEGA_RISK:

    LINE/COL ERROR
    -------- -----------------------------------------------
    6/5      PL/SQLSQL Statement ignored
    6
    /35     PL/SQLORA-00942: table or view does not exist

    19
    :17:46 OMEGA_RISK @SPH as  > select table_name,PRIVILEGE from user_tab_privs where owner='SYS';

    TABLE_NAME                     PRIVILEGE
    ------------------------------ ----------------------------------------
    V_$SESSION                     SELECT

    1 row selected
    .
    19:18:02 OMEGA_RISK @SPH as  > show user
    USER is 
    "OMEGA_RISK" 
    Last edited by kgb; 01-28-2014 at 02:28 PM.
    Best wishes!
    Dmitri

  2. #2
    Join Date
    Jan 2014
    Location
    Apple Valley, CA
    Posts
    2
    Dmitri,

    Are you gaining the SELECT privilege on SYS.V_$SESSION from a role?
    If so, privileges from roles don't carry over to triggers.

    Mark

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Good point, Mark. Thank you !
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width