CONNECT OWNER_USER/PASSWORD;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
set serveroutput on
DECLARE
v_jobno BINARY_INTEGER;
v_what VARCHAR2(2000);
v_interval VARCHAR2(200);
v_errnum NUMBER;
v_errmsg VARCHAR2(100);
CURSOR c_job IS
SELECT job, what, interval FROM user_jobs;
v_job c_job%ROWTYPE;
BEGIN
OPEN c_job;
LOOP
FETCH c_job INTO v_job;
EXIT WHEN c_job%NOTFOUND;
DBMS_JOB.REMOVE(v_job.job);
DBMS_OUTPUT.PUT_LINE('Job '||v_job.job||' removed.');
DBMS_JOB.SUBMIT(v_jobno, v_what, sysdate, v_interval, FALSE);
DBMS_JOB.RUN(v_jobno);
DBMS_OUTPUT.PUT_LINE('Job '||v_jobno||' submitted.');
END LOOP;
CLOSE c_job;
EXCEPTION
WHEN OTHERS THEN
v_errnum := SQLCODE;
v_errmsg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('Other error occurred with SQLCODE '||v_errnum);
DBMS_OUTPUT.PUT_LINE('-Error Message: '||v_errmsg);
CLOSE c_job;
END;
/
