1

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:

enter image description here

I tried to edit the start time in the wizard and it gives me this error:

enter image description here

I scheduled it to run every 2 minutes starting a while ago, and it hasn't run yet. It's definitely enabled. enter image description here


Troubleshooting

TLDR: scheduler works!!! The only thing that doesn't work is me putting in my email to get notifications.

  1. Am i running it in the same schema I created it in? enter image description here

I changed the job to run every 2 minutes. enter image description here ^^^ 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!

  1. 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: enter image description here

The thing that still doesn't work is notifications via email. When that's the only thing i edit, i get this error:

enter image description here

6
  • It won't run automatically until it is enabled. What version of SQLDeveloper are you using? – Michael Kutz Aug 20 '20 at 19:08
  • It was scheduled to run every Friday.To test it change frequency to every x minutes everyday if the job runs successfully change back to every Friday.(In SQL Developer check sql tab for line 6) – user168186 Aug 20 '20 at 19:39
  • @MichaelKutz i have version 19.2 and I enabled it after creating it. – ellie-lumen Aug 20 '20 at 21:52
  • @Kumar I enabled it to run today every 2 minutes, and it still doesn't work. I don't know what's wrong with line 6. – ellie-lumen Aug 20 '20 at 21:55
  • Where are you creating jobs ? as sysdba or C##elle? – user168186 Aug 21 '20 at 2:01
2

Most probably you are creating objects in different schemas,verify created jobs and job details in current schema with these views user_scheduler_jobs and user_scheduler_job_run_details

Simple procedure scheduled to run every 2 minutes

  SQL>create or replace procedure test_print
      2  is
      3  begin
      4  dbms_output.put_line('Current date and time is : ' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
      5* end;
    SQL> /
    
    Procedure created.
    
    SQL> exec test_print();
    Current date and time is : 20-08-2020 09:22:59 PM
    
    PL/SQL procedure successfully completed.
    
    
    SQL> BEGIN
      2             DBMS_SCHEDULER.CREATE_JOB (
      3              job_name => 'JOB_TEST',
      4              job_type => 'STORED_PROCEDURE',
      5              job_action => 'TEST_PRINT',
      6              number_of_arguments => 0,
      7              start_date => NULL,
      8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
      9              end_date => NULL,
     10              enabled => FALSE,
     11              auto_drop => FALSE,
     12              comments => 'Testing job runs');
     13
     14
     15
     16
     17
     18              DBMS_SCHEDULER.enable(
     19               name => 'JOB_TEST');
     20  END;
     21  /
     
  SQL> select job_name,output,status from user_scheduler_job_run_details
       2  where  job_name='JOB_TEST';

JOB_NAME   OUTPUT                                             STATUS
---------- -------------------------------------------------- ---------------
JOB_TEST   Current date and time is :20-08-2020 09:30:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:32:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:34:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:36:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:38:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:40:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:42:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:44:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:46:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:48:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:50:05 PM  SUCCEEDED -- Modified date format in procedure
JOB_TEST   Current date and time is :20-AUG-2020 09:52:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:54:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:58:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:02:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:04:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:56:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:00:05 PM  SUCCEEDED

18 rows selected.


SQL> select job_name,last_start_date,last_run_duration from user_scheduler_jobs
  2  where job_name='JOB_TEST';

JOB_NAME   LAST_START_DATE                                    LAST_RUN_DURATION
---------- -------------------------------------------------- ------------------------------
JOB_TEST   20-AUG-20 10.08.05.070000 PM US/CENTRAL            +000000000 00:00:00.009000

Edit:- Just observation you were able to run job manually but not running from scheduler implies job and procedure are not in the same schema.

2
  • Thank you for doing so much work to help me! I did your troubleshooting (updated in the question), and my scheduler works now! I was in the same schema. What made it work was changing the start date to NULL. The only thing that's wrong now is that if I try to put in my email to get notifications, it throws the same error as before (updated in the last paragraph of my question). – ellie-lumen Aug 21 '20 at 15:01
  • Email notification working fine from SQL Developer as well as SQLPLUS. Next time when you run into error in SQL Developer for notification go to sql tab copy complete code to SQL sheet and see if Sql Developer points to any missing commas,quotes or braces or run the code other option check this link – user168186 Aug 22 '20 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.