FAQ
Hi!

When I try to compile the trigger below, I get the error message:

PLS-00201: identifier 'SYS.V_$SESSION' must be declared

Why???? v$session is nothing but a synonym for v$_session.
The user trying to compile this trigger has DBA privileges.

CREATE OR REPLACE TRIGGER TBREVENUECAT_TRIGGER_DEL AFTER DELETE

ON VIVOUSER.TBREVENUECAT

FOR EACH ROW

BEGIN

DECLARE myApplName varchar2(50);
BEGIN

SELECT PROGRAM into myApplName
FROM v$session WHERE audsid = userenv('sessionid');
IF Trim(Upper(myApplName))<>Trim('VIVOCLIENT.EXE') THEN
INSERT INTO OM_TBREVENUECAT (OM_TRANSID, OM_TRANSTYPE,
OM_TRANSSTATUS, COMPANYID, STORELOCID, REVCATID, DEPTID, STATUS)
VALUES (OM_IDS.NextVal, 'D', 'N', :OLD.COMPANYID, :OLD.STORELOCID,
:OLD.REVCATID, :OLD.DEPTID, :OLD.STATUS);
END IF;

END;

END;

Any ideas?

Search Discussions

  • Jared Still at Aug 24, 2000 at 8:44 pm
    Helmut,

    This is one of those questions that is asked very
    frequently, and the answer can be found at sites
    such as www.orafaq.org. ( I checked, the answer
    is there )

    The problem is that you cannot use a database
    object in a stored procedure if the grants
    are made through a role. The grants much be
    made directly to the user ( You in this case ).

    Jared
    On Thu, 24 Aug 2000, Helmut Daiminger wrote:

    Hi!

    When I try to compile the trigger below, I get the error message:

    PLS-00201: identifier 'SYS.V_$SESSION' must be declared

    Why???? v$session is nothing but a synonym for v$_session.
    The user trying to compile this trigger has DBA privileges.


    CREATE OR REPLACE TRIGGER TBREVENUECAT_TRIGGER_DEL AFTER DELETE
    ON VIVOUSER.TBREVENUECAT
    FOR EACH ROW
    BEGIN
    DECLARE myApplName varchar2(50);
    BEGIN
    SELECT PROGRAM into myApplName
    FROM v$session WHERE audsid = userenv('sessionid');
    IF Trim(Upper(myApplName))<>Trim('VIVOCLIENT.EXE') THEN
    INSERT INTO OM_TBREVENUECAT (OM_TRANSID, OM_TRANSTYPE,
    OM_TRANSSTATUS, COMPANYID, STORELOCID, REVCATID, DEPTID, STATUS)
    VALUES (OM_IDS.NextVal, 'D', 'N', :OLD.COMPANYID, :OLD.STORELOCID,
    :OLD.REVCATID, :OLD.DEPTID, :OLD.STATUS);
    END IF;
    END;
    END;

    Any ideas?

    Thanks,
    Helmut


    --------
    If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
    to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
    to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
    Jared Still
    Certified Oracle DBA and Part Time Perl Evangelist;-)
    Regence BlueCross BlueShield of Oregon
    jkstill_at_bcbso.com - Work - preferred address

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 24, '00 at 6:37p
activeAug 24, '00 at 8:44p
posts2
users2
websiteoracle.com

2 users in discussion

Jared Still: 1 post Helmut Daiminger: 1 post

People

Translate

site design / logo © 2022 Grokbase