The program I'm working on has multiple different environments out of which some don't have a certain JOB scheduled in them. What is the best way to sync them all up using the database's migration scripts?
I have tried just creating the job with dbms_scheduler.create_job()
but on the environments it already exists an error was thrown saying that "An attempt was made to create an object with a name that has already been used by another object in the same schema.".
After that, I tried dropping the existing job using dbms_scheduler.drop_job()
but on the environments that the job doesn't exist an error is thrown saying that "The specified object did not exist, privileges were not granted, or the object was of the wrong type.".
Is there a way to CREATE OR REPLACE a job?
There is no "CREATE OR REPLACE job" syntax. But it's not that hard to work around that. Check if the job exists, drop it if it does and then create it.
DECLARE
l_job VARCHAR2(100) := 'MY_JOB';
FUNCTION job_exists (job_name_i VARCHAR2) RETURN BOOLEAN
IS
l_dummy INTEGER;
BEGIN
SELECT 1 INTO l_dummy FROM user_scheduler_jobs WHERE job_name = job_name_i;
RETURN true;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN false;
END;
BEGIN
IF job_exists(job_name_i => l_job) THEN
dbms_scheduler.drop_job ('MY_JOB');
END IF;
dbms_scheduler.create_job(
job_name => 'MY_JOB'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN NULL; END;'
);
END;
/