How can I get rid of a DBMS_SCHEDULER.CREATE_JOB

I made a dbms scheduler.create job

BEGIN
              DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'SCHEDULE'
                                       ,JOB_TYPE => 'STORED_PROCEDURE'
                                       ,JOB_ACTION => 'myschema....mystuff'
                                       ,START_DATE => SYSTIMESTAMP
                                       ,REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=1'
                                       ,COMMENTS => 'ORIGINALLY ONCE PER DAY BUT NOW I WANT IT EVERY HOUR'
                                       ,END_DATE => NULL);

END;

Open in new window


 but I entered the wrong repeat_interval so when I went to reset my interval I got an error telling me that schedule already exists.

Do I drop the schedule and then re-create it(if so, how)?

Or do I need some sort of update statement?

Thanks a bunch
Mark_CoAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Probably the easiest is drop it with DBMS_SCHEDULER.DROP_JOB

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72273

You can change values but I would just drop it and recreate it.
0
 
Gerwin Jansen, EE MVEConnect With a Mentor Topic Advisor Commented:
Hi, you should be able to remove it like this:

EXEC DBMS_SCHEDULER.DROP_JOB('SCHEDULE');

Display it like this:

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'SCHEDULE';

Disable it like this:
(if you didn't already delete it)

BEGIN
  DBMS_SCHEDULER.DISABLE('SCHEDULE');
END;
0
 
Mark_CoAuthor Commented:
k so here's a dumb question: Why are neither of these working? I get 'invalid SQL statement' for each. Forgive me if I'm doing silly things, i've just tried to read the documentation and do a similar thing but neither is working.

DBMS_SCHEDULER.DROP_JOB(
                                      JOB_NAME IN Varchar2
                                     ,JOB_TYPE IN VARCHAR2
                                     ,JOB_ACTION IN VARCHAR2
                                     ,START_DATE in TIMESTAMP
                                     ,REPEAT_INTERVAL IN INTERVAL
                                     ,COMMENTS  IN VARCHAR2
                                     ,END_DATE in NULL
                                     );

Open in new window



DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'SCHEDULE'
                                     ,JOB_TYPE => 'STORED_PROCEDURE'
                                     ,JOB_ACTION => 'blank'
                                     ,START_DATE => SYSTIMESTAMP
                                     ,REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1'
                                     ,COMMENTS => 'ONCE PER DAY'
                                     ,END_DATE => NULL);

Open in new window

0
 
Mark_CoAuthor Commented:
Got it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.