I can't schedule a job to run.
I made a procedure that writes CSV files and it works when I execute it.
Then I tried to make a job to schedule it:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'AUTOMATE_CSV_EXPORT',
job_type => 'STORED_PROCEDURE',
job_action => 'EXPORT_ALL_LYS_FOR_TABLEAU',
number_of_arguments => 0,
start_date => '20-AUG-20 2.05.00PM',
repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23;',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'hello i am a comment');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'AUTOMATE_CSV_EXPORT',
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_RUNS);
DBMS_SCHEDULER.enable(
name => 'AUTOMATE_CSV_EXPORT');
END;
/
It didn't run at the scheduled time, but it did when I ran it like this:
I tried to edit the start time in the wizard and it gives me this error:
I scheduled it to run every 2 minutes starting a while ago, and it hasn't run yet. It's definitely enabled.
Troubleshooting
TLDR: scheduler works!!! The only thing that doesn't work is me putting in my email to get notifications.
- Am i running it in the same schema I created it in?
I changed the job to run every 2 minutes.
^^^ so it is working? But I don't see any CSVs in the destination folder. I changed the start date to NULL like the code below and it worked! I have CSVs!
- Testing a scheduled job: test:
create or replace procedure test_print
is
begin
dbms_output.put_line('Current date and time is : ' ||
to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
end;
set serveroutput on;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_TEST',
job_type => 'STORED_PROCEDURE',
job_action => 'TEST_PRINT',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Testing job runs');
DBMS_SCHEDULER.enable(name => 'JOB_TEST');
END;
/
select job_name,output,status,owner
from user_scheduler_job_run_details where job_name='JOB_TEST';
results:
The thing that still doesn't work is notifications via email. When that's the only thing i edit, i get this error:
