Hi Oracle expert,
I downloaded the job email notification and installed in our existing development DB oracle 10g.
The installation was successfully.
However when I tried to run the procedure it endup with errors as follows:
SQL> exec add_job_email_notification('BACKUP_IDTAPPS.XXX.COM_000009','s
teven.foo@xxx.com','JOB_FAILED');
BEGIN add_job_email_notification('BACKUP_IDTAPPS.XXX.COM_000009','steve
n.foo@xxx.com','JOB_FAILED'); END;
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_ISCHED", line 3559
ORA-06512: at "SYS.DBMS_ISCHED", line 3532
ORA-06512: at "SYS.ADD_JOB_EMAIL_NOTIFICATION", line 37
ORA-06512: at line 1
Any one have ideas?
Below is the procedure code from Oracle.
(
job_name IN VARCHAR2,
recipient_address IN VARCHAR2,
events IN VARCHAR2 DEFAULT
'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED',
sender_address IN VARCHAR2 DEFAULT 'do-not-reply@oracle.com',
subject_prefix IN VARCHAR2 DEFAULT 'Oracle Scheduler Job Notification',
email_server_host IN VARCHAR2 DEFAULT 'mail.XXX.com',
email_server_port IN PLS_INTEGER DEFAULT 25
) AS
events_on NUMBER := 0;
job_object VARCHAR2(35);
job_owner VARCHAR2(35);
canon_job_name VARCHAR2(30);
canon_job_owner VARCHAR2(30);
caller VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(80);
new_raise_events NUMBER := 0;
cur_raise_events VARCHAR2(200);
priv_count NUMBER;
event_condition VARCHAR2(4000);
comments_text VARCHAR2(100) :=
'Auto-generated job to send email alerts for job ';
type event_name_list is table of varchar2(30);
type event_number_list is table of number;
event_names event_name_list := event_name_list('JOB_STARTED',
'JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED',
'JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED',
'JOB_CHAIN_STALLED', 'JOB_OVER_MAX_DUR');
event_numbers event_number_list :=
event_number_list(1,2,4,8,16,32,64,128,256,512);
event_found pls_integer := 0;
event_10862 pls_integer := 0;
begin
-- get job name and owner
sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);
-- canonicalize job name and owner
dbms_utility.canonicalize(job_object, canon_job_name, 30);
dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;
-- check if the caller has privileges on the job
-- check if the caller is the job owner or 'SYS'
IF canon_job_owner = caller or caller = 'SYS' THEN
goto privilege_check_passed;
END IF;
-- check whether the caller has been granted ALTER on the job or
-- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
select count(*) into priv_count from dba_sys_privs where grantee=caller and
privilege='CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_role_privs where grantee=caller and
granted_role='SCHEDULER_ADMIN';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_tab_privs where grantee=caller and
owner=canon_job_owner and table_name=canon_job_name;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check for CREATE ANY JOB system priv
-- includes a recursive roles check so SCHEDULER_ADMIN will also work
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select grantee, granted from
(
/* roles granted */
select grantee, granted_role granted from dba_role_privs
/* system privileges granted */
union
select grantee, privilege granted from dba_sys_privs
)
start with grantee = caller connect by grantee = prior granted )
where granted = 'CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check whether the caller has object privileges on the job
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select * from
(
/* object privileges granted */
select table_name g1, owner g2, grantee obj, grantee own, privilege typ
from dba_tab_privs
/* role privileges granted */
union
select granted_role g1, granted_role g2, grantee, grantee, null
from dba_role_privs
)
start with g1 = canon_job_name and g2 = canon_Job_owner
connect by g1 = prior obj and g2 = prior own)
where obj=caller;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- no privileges, throw job_does_exist error
dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
TRUE);
<<privilege_check_passed>>
-- retrieve current events turned on. cast NO_DATA_FOUND to job not found
begin
select raise_events into cur_raise_events from dba_scheduler_jobs where
job_name=canon_job_name and owner=canon_job_owner ;
exception when no_data_found then
dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
TRUE);
when others then raise;
end;
-- generate event_condition
event_condition := 'tab.user_data.object_owner = '''||canon_job_owner||
''' AND tab.user_data.object_name = '''||canon_job_name|| '''';
if instr(UPPER(events),'JOB_ALL_EVENTS')>0 then
-- by default we have no events clause so all events will trigger an e-mail
event_found := 1;
else
event_condition := event_condition ||' AND tab.user_data.event_type in (';
for i in event_names.first..event_names.last loop
if instr(UPPER(events),event_names(i))>0 then
event_condition := event_condition || ''''||event_names(i)||''',';
event_found := 1;
end if;
end loop;
if instr(UPPER(events),'JOB_RUN_COMPLETED')>0 then
event_condition := event_condition ||
'''JOB_SUCCEEDED'',''JOB_FAILED'',''JOB_STOPPED'',';
event_found := 1;
end if;
-- strip last comma and add close brace
event_condition := regexp_replace(event_condition, ',$');
event_condition := event_condition || ')';
end if;
-- if no events have been specified, throw an error
if event_found = 0 then
dbms_sys_error.raise_system_error(-24098, events, 'EVENTS',TRUE);
end if;
-- collect all events to turn on
if cur_raise_events is null then
cur_raise_events := UPPER(events) ;
else
cur_raise_events := UPPER(events) ||','||UPPER(cur_raise_events) ;
end if;
for i in event_names.first..event_names.last loop
if instr(cur_raise_events,event_names(i))>0 then
new_raise_events := new_raise_events + event_numbers(i);
end if;
end loop;
if instr(cur_raise_events,'JOB_RUN_COMPLETED')>0 then
new_raise_events := new_raise_events -
bitand(new_raise_events,sys.dbms_scheduler.job_run_completed) +
sys.dbms_scheduler.job_run_completed;
end if;
if instr(cur_raise_events,'JOB_ALL_EVENTS')>0 then
new_raise_events := new_raise_events -
bitand(new_raise_events,sys.dbms_scheduler.job_all_events) +
sys.dbms_scheduler.job_all_events;
end if;
-- turn on events the user is interested in
dbms_scheduler.set_attribute
( '"'||canon_job_owner||'"."'||canon_job_name||'"' , 'raise_events' ,
new_raise_events);
-- set event 10862 if not set so that we can add a subscriber
-- this is necessary because if event 10862 is not set then AQ is in backward
-- compatibility mode which checks the login user instead of the current user
-- for privileges.
dbms_system.read_ev(10862, event_10862);
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE
'ALTER SESSION SET EVENTS ''10862 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
END IF;
-- add a new subscriber for this notification
BEGIN
dbms_aqadm.add_subscriber
(queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
subscriber => sys.aq$_agent(canon_job_owner, NULL, NULL),
rule => 'tab.user_data.object_owner = '''||canon_job_owner||'''');
EXCEPTION WHEN others then
-- unset event 10862, if we set it above
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
'''10862 TRACE NAME CONTEXT OFF''' ;
END IF;
if sqlcode = -24034 then NULL;
else raise;
end if;
end;
-- unset event 10862, if we set it above
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
'''10862 TRACE NAME CONTEXT OFF''' ;
END IF;
-- allow the job owner to access our events queue via this subscriber
dbms_aqadm.enable_db_access(canon_job_owner,'"'||canon_job_owner||'"');
-- if this procedure has been run already for this job, drop previously
-- created email notification jobs. One of the parameters might have changed
-- and existing email notification jobs might have been altered or broken.
FOR old_notify_job IN
( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
and comments = comments_text and program_owner = 'SYS'
and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
)
LOOP
dbms_scheduler.drop_job(old_notify_job.name);
END LOOP;
-- now create a notifier job which waits on job events
BEGIN
-- try using a simple name without an added number suffix
notifier_job_name := substr(canon_job_name,1,10) || '_EMAILER' ;
notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
dbms_scheduler.create_job(notifier_job_name,
program_name => 'sys.email_notification_program',
event_condition => event_condition,
queue_spec =>'sys.scheduler$_event_queue,"'
|| canon_job_owner||'"',
comments => comments_text);
EXCEPTION WHEN OTHERS THEN
IF sqlcode != -27477 THEN RAISE; END IF;
-- a job already exists using our simple name, add a numerical suffix
notifier_job_name :=
dbms_scheduler.generate_job_name( '"'||substr(canon_job_name,1,10)||'_EMAILER"');
notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
dbms_scheduler.create_job(notifier_job_name,
program_name => 'sys.email_notification_program',
event_condition => event_condition,
queue_spec =>'sys.scheduler$_event_queue,"'
|| canon_job_owner||'"',
comments => comments_text);
END;
dbms_scheduler.set_job_argument_value(notifier_job_name,2,
recipient_address);
dbms_scheduler.set_job_argument_value(notifier_job_name,3,
email_server_host);
dbms_scheduler.set_job_argument_value(notifier_job_name,4,
sender_address);
dbms_scheduler.set_job_argument_value(notifier_job_name,5,
subject_prefix);
dbms_scheduler.set_job_argument_value(notifier_job_name,6,
to_char(email_server_port));
dbms_scheduler.enable(notifier_job_name);
end;