How to present job_name to DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION
I am having trouble adding email notifications to Oracle 11g (11.2.0.1.0). It seems like a bug but I'm really not sure. I've tried doing this using SQL Developer to build the code as well as examples from the internet but it's not working.
I can create and enable a job easily enough:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"SCHEMA"."test1"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
null;
end;
',
auto_drop => FALSE
);
DBMS_SCHEDULER.enable(
name => '"SCHEMA"."test1"');
END;
/
anonymous block completed
As a precaution, I remove the job email notification - this works.
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
job_name => '"SCHEMA"."test1"'
);
end;
/
anonymous block completed
But when I try to add an email notification it's as if it can't find the object, I'm working in my own schema and have the DBA role so I would have thought any potential permission issues should be overcome (though in my own schema I would have assumed I could make scheduled jobs easily enough)
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => '"SCHEMA"."test1"',
recipients => '[email protected]',
events => 'JOB_BROKEN, JOB_CHAIN_STALLED, JOB_FAILED, JOB_OVER_MAX_DUR, JOB_SCH_LIM_REACHED'
);
END;
/
ORA-27476: "SCHEMA.SCHEMA" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4856
ORA-06512: at "SYS.DBMS_ISCHED", line 7117
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4030
ORA-06512: at line 3
Note it says SCHEMA.SCHEMA as if it didn't read the line properly. When I change it from '"SCHEMA"."test1"' to 'test1' it still doesn't work but says ORA-27476: "SCHEMA.TEST1" does not exist.
All my jobs work correctly and behave and I have gotten a UTL_MAIL implementation going but I'd really like to get the oracle stuff working for simplicity if possible.
Answer
A possible issue is that by default object names in Oracle are case insensitive, unless you surround them with double quotes.
Here is a test case:
SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> create user STACKOVERFLOW identified by STACKOVERFLOW;
User created.
SQL> grant connect, create job to STACKOVERFLOW;
Grant succeeded.
SQL> conn STACKOVERFLOW/STACKOVERFLOW
SQL> l
1 BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => '"STACKOVERFLOW"."test1"',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'begin
6 null;
7 end;
8 ',
9 auto_drop => FALSE
10 );
11 DBMS_SCHEDULER.enable(
12 name => '"STACKOVERFLOW"."test1"');
13* END;
SQL> /
PL/SQL procedure successfully completed.
If you name your job "test1" Oracle will create it with a lowercase name. You can confirm this by checking the catalog view dba_scheduler_jobs:
SQL> select owner, job_name from dba_scheduler_jobs where job_name = 'TEST1';
no rows selected
SQL> select owner, job_name from dba_scheduler_jobs where job_name = 'test1';
OWNER JOB_NAME
------------------------------ ------------------------------
STACKOVERFLOW test1
Therefore, this will work:
SQL> l
1 BEGIN
2 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
3 job_name => '"STACKOVERFLOW"."test1"',
4 recipients => '[email protected]',
5 events => 'JOB_BROKEN, JOB_CHAIN_STALLED, JOB_FAILED, JOB_OVER_MAX_DUR, JOB_SCH_LIM_REACHED'
6 );
7* END;
SQL> /
PL/SQL procedure successfully completed.
But this won't:
SQL> l
1 BEGIN
2 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
3 job_name => '"STACKOVERFLOW".TEST1',
4 recipients => '[email protected]',
5 events => 'JOB_BROKEN, JOB_CHAIN_STALLED, JOB_FAILED, JOB_OVER_MAX_DUR, JOB_SCH_LIM_REACHED'
6 );
7* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-27476: "STACKOVERFLOW.STACKOVERFLOW" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 7613
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4063
ORA-06512: at line 2