exec dbms_scheduler.create_job(job_name=>null,job_type=>'PLSQL_BLOCK',job_action=>'BEGIN NULL; END;')
ORA-27451: JOB_NAME cannot be NULL
ORA-06512: at "SYS.DBMS_ISCHED", line 146
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 1
This sounds like a proper error message. A bit less obvious is the drop_job message
SQL> exec dbms_scheduler.drop_job(job_name=>null)
ORA-20001: comma-separated list invalid near
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at "SYS.DBMS_SCHEDULER", line 743
ORA-06512: at line 1
comma-separated list invalid near what?
Ok, why would you create an empty job? Obviously you wouldn’t. But remember job_name could be a very long expression that won’t fit in your VARCHAR2(30) variable.
SQL> begin
dbms_scheduler.create_job(job_name=>
' "SCOTT" '||
' . '||
' "JOB10000000000000000000001" ',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN NULL; END;');
end;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.drop_job('scott.job10000000000000000000001')
PL/SQL procedure successfully completed.
If you use drop job in the exception clause without catching the exception of the exception, it could lead to this ORA-20001 if job name is null
For exception handling, we could improve
BEGIN
CREATE JOB
RUN JOB
DROP JOB
EXCEPTION
WHEN OTHERS THEN
DROP JOB
output message
RAISE
END
into
BEGIN
CREATE JOB
RUN JOB
DROP JOB
EXCEPTION
WHEN OTHERS THEN
BEGIN
DROP JOB
EXCEPTION
WHEN IS_RUNNING
sleep
WHEN OTHERS
output message
END LOOP
output message
RAISE
END