Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A trigger has given me the following:

 ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation

and this is when working on altering schemas/triggers/etc from an import of a client database.

I ran

show errors trigger adminuser.db_error_change_trig

and the results were:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PL/SQL: Statement ignored
2/5      PLS-00201: identifier 'DBMS_ALERT' must be declared

Now, googling, I have found that I need to grant explicit execute privileges to the public user which I did with

grant execute on dms_public to public

However, this has not worked.
How can I determine what is going on, as well as how to fix this. Thirdly, is dbms_alert schema specific? Meaning, my understanding is the the full name of the object is SYS.dbms_alert, is there another for another schema or is one global one?

share|improve this question
    
Privileges received through roles are not in effect in PL/SQL code. You need to grant the execute privilege directly to the owner of the trigger –  a_horse_with_no_name Apr 14 at 18:50

2 Answers 2

I think you may be missing synonym for dbms_alert for adminuser schema. A object grant and synonym both are normally required (unless you own the object).

Try following as sys account, perhaps it will fix the problem.

create or replace synonym adminuser.dbms_alert for sys.dbms_alert;

If you don't want to create a synonym, you should use sys.dbms_alert, if your create a synonym as shown above, using just dbms_alert is sufficient in your code.

share|improve this answer

Are you sure public is a user and not a role? Privileges acquired via roles have no effect in PL/SQL context. You need to grant sufficient permissions for sys.dbms_alert to the user.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.