Re: Triggers fails to compile. Why?

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 Received on Thu Aug 24 2000 - 15:44:47 CDT