• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

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
0
Mark_Co
Asked:
Mark_Co
  • 2
2 Solutions
 
slightwv (䄆 Netminder) 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 MVETopic 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now