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 StillHi!
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
Certified Oracle DBA and Part Time Perl Evangelist;-)
Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address
