4

I have just created a job using SQL Developer build-in wizard and i would like to change the parameters of the created job from my application. The job is launching a stored procedure every day at 7 o'clock.

In the application I have two fields:

  1. Disable: true/false
  2. Hour: 10:00

And this is my job and fields I would like to update depending on what was selected in the app:

enter image description here

I see two ways of doing it:

  1. Make the fields dependent on the values in the table
  2. Make a pl/sql block that updates the values

Of course I don't know how to do it (in the first option the select statement doesn't work and in the second I don't know how to acces the jobs fields)

Please help

Aleksander Lipka
  • 354
  • 1
  • 9
  • 20

2 Answers2

6
Begin 
dbms_scheduler.disable( 'ATOS."job_email_notifications"' );

DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ATOS."job_email_notifications"', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');--change value--as per need

dbms_scheduler.enable( 'ATOS."job_email_notifications"' );
End;
/
Prashant Mishra
  • 619
  • 9
  • 25
  • Hey, thanks for the answer, however this doesn't work for me, the error says: Error report - ORA-27476: "ATOS.JOB_EMAIL_NOTIFICATIONS" does not exist ORA-06512: at line 2 *Cause: A database object was specified that does not exist. – Aleksander Lipka Aug 24 '16 at 07:43
  • post the output of below query : select * from dba_scheduler_jobs where job_name ='JOB_EMAIL_NOTIFICATIONS'; – Prashant Mishra Aug 24 '16 at 07:45
  • ORA-00942: table or view does not exist. Maybe I have a different scheduler name or sth? – Aleksander Lipka Aug 24 '16 at 07:47
  • 1
    try this select * from all_scheduler_jobs where job_name ='JOB_EMAIL_NOTIFICATIONS'; It seems you have rights to view dba views. – Prashant Mishra Aug 24 '16 at 07:48
  • select * from all_scheduler_jobs where job_name ='job_email_notifications'; with smaller letters it worked. I can see my job – Aleksander Lipka Aug 24 '16 at 07:52
  • use the code provided by me again.I have updated my answer. Begin dbms_scheduler.disable( 'job_email_notifications' ); DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'job_email_notifications', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');--change value--as per need dbms_scheduler.enable( 'job_email_notifications' ); End; – Prashant Mishra Aug 24 '16 at 07:54
  • I tried changing job names before and it didn't work. It still doesn't work – Aleksander Lipka Aug 24 '16 at 07:56
  • 1
    Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/121695/discussion-between-prashant-mishra-and-aleksander-lipka). – Prashant Mishra Aug 24 '16 at 07:57
0

Use below procedure for dynamic updates based on values 

Create or replace procedure change_attributes(a_job_name varchar2, a_param varchar2, a_new_val varchar2)
As
Begin 
dbms_scheduler.disable( a_job_name);

DBMS_SCHEDULER.SET_ATTRIBUTE ( name => a_job_name,attribute => a_param, value => a_new_val);

dbms_scheduler.enable( a_job_name);
End;
/
Prashant Mishra
  • 619
  • 9
  • 25