Trigger needs dbms_alert declared

oracle-11g-r2permissionstrigger

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?

Best Answer

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.

Related Solutions

ADMINISTER DATABASE TRIGGER privilege

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe. If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

It appears that the trigger is behaving as expected in this case. You wouldn't in reality create a database link from one database to another using a DBA account.

Mysql – Auto increment value needs to be changed inside trigger

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly):

CREATE TRIGGER innodb_seqno_trigger 
   BEFORE INSERT ON my_table 
   FOR EACH ROW 
BEGIN

  SELECT MAX(seqno) INTO @newseqno 
  FROM my_table 
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1); 

END;

Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?

Best Answer

Related Solutions

ADMINISTER DATABASE TRIGGER privilege

Mysql – Auto increment value needs to be changed inside trigger

Related Question