| Alert email notifications [message #500379] |
Sun, 20 March 2011 08:05  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'm trying to set up alert notifications. I have to program it myself, because SE isn't allowed to do it with OEM.
As far as I can see from the documentation, this should be all that is necessary:
conn / as sysdba
exec dbms_aqelm.set_mailhost(mailhost=>'127.0.0.1')
exec dbms_aqelm.set_mailport(mailport=>25)
exec dbms_aqelm.set_sendfrom(sendfrom=>'alert@noreply')
DECLARE
reginfo sys.aq$_reg_info;
reg_list sys.aq$_reg_info_list;
BEGIN
reginfo := sys.aq$_reg_info(
'sys.alert_que',
DBMS_AQ.NAMESPACE_ANONYMOUS,
'mailto://me@myaddress',
HEXTORAW('FF'));
reg_list := sys.aq$_reg_info_list(reginfo);
DBMS_AQ.REGISTER(
reg_list => reg_list,
reg_count => 1);
COMMIT;
END;
/
but this gives me:
ORA-24940: invalid combination of ANONYMOUS namespace, default presentation and
e-mail receive protocol
ORA-06512: at "SYS.DBMS_AQ", line 737
ORA-06512: at line 11
I must be missing something! Probably something very basic. There is nothing in any trace files.
Has anyone done this already? Can you share the code? Or give me any advice?
Thanks.
[update: pasted in the wrong code/error]
[Updated on: Sun, 20 March 2011 08:16] Report message to a moderator |
|
|
|
| Re: Alert email notifications [message #500380 is a reply to message #500379] |
Sun, 20 March 2011 08:25  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If I try the AQ namespace, I get this:
orcl>DECLARE
2 reginfo sys.aq$_reg_info;
3 reg_list sys.aq$_reg_info_list;
4 BEGIN
5 reginfo := sys.aq$_reg_info(
6 'sys.alert_que',
7 DBMS_AQ.NAMESPACE_AQ,
8 'mailto://me@myaddress',
9 HEXTORAW('FF'));
10 reg_list := sys.aq$_reg_info_list(reginfo);
11 DBMS_AQ.REGISTER(
12 reg_list => reg_list,
13 reg_count => 1);
14 COMMIT;
15 END;
16 /
DECLARE
*
ERROR at line 1:
ORA-25257: consumer must be specified with a multi-consumer queue
ORA-06512: at "SYS.DBMS_AQ", line 737
ORA-06512: at line 11
which I don't understand at all: I can't see an attribute in the aq$_reg_info type where I can specify a consumer.
|
|
|
|
| Re: Alert email notifications [message #500384 is a reply to message #500380] |
Sun, 20 March 2011 10:38 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've got rid of the error - it just doesn't send any emails when an alert is raised. Help! I must be missing something.
This is what compiles, I have to create and nominate the subscriber and the agent:
exec dbms_aqadm.add_subscriber('sys.alert_que',-
aq$_agent('alert_scott','',0));
exec dbms_aqadm.enable_db_access(agent_name=>'alert_scott',-
db_username=>'scott');
exec dbms_aqelm.set_mailhost(mailhost=>'127.0.0.1')
exec dbms_aqelm.set_mailport(mailport=>25)
exec dbms_aqelm.set_sendfrom(sendfrom=>'scott_alert@noreply')
DECLARE
reginfo sys.aq$_reg_info;
reg_list sys.aq$_reg_info_list;
BEGIN
reginfo := sys.aq$_reg_info(
'sys.alert_que:alert_scott',
DBMS_AQ.NAMESPACE_AQ,
'mailto://me@myemail',
HEXTORAW('FF'));
reg_list := sys.aq$_reg_info_list(reginfo);
DBMS_AQ.REGISTER(
reg_list => reg_list,
reg_count => 1);
COMMIT;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
