How to present job_name to DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

Question

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_address@test.com',
             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.

Are you sure that the job was created in the correct schema? What's the output of `select owner, job_name from dba_scheduler_jobs where job_name = 'TEST1'`? Also be aware that `"test1"` will be case sensitive since it is surrounded by double quotes. — Marco Baldelli, Sep 17 '14 at 06:15
@MarcoBaldelli I think this is at the core of the problem. I think that the add_scheduler function doesn't appreciate lower case even though scheduler can handle it. Technically I suppose this is a bug but lowercase object names are terrible so I don't fault it. I'd just rather SQL Developer did an upcase for me... Once I confirm I'll ask you to put that as an answer. — Ewanw, Sep 18 '14 at 04:27
Definitely seems the right analysis. Please do an answer for me to mark. My process is running but I'm not getting emails. Time for me to dig deeper. — Ewanw, Sep 29 '14 at 00:06

score 2 · Accepted Answer · answered Sep 29 '14 at 07:29

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_address@test.com',
  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_address@test.com',
  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

How to present job_name to DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

1 Answers1