How to alter oracle job to run every 12hrs

Question

I need below job to be run at every 12 hrs (example : job need to be run daily at 12:30am & 12:30pm) please someone help

Code :

BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 123
,what => 'SP_ABC;'
,next_date => TO_DATE('08/09/2020 00:30:00','dd/mm/yyyy hh24:mi:ss')
,INTERVAL => 'TRUNC(sysdate+1)+30/1440'
);
END;
/
COMMIT; 

Answer 1

That would be something like this:

SQL> set serveroutput on
SQL> declare
  2    l_job number;
  3  begin
  4    dbms_job.submit
  5      (job       => l_job,
  6       what      => 'p_test;',
  7       next_date =>  trunc(sysdate + 1) +  0/24 + 30/(24*60),
  8       interval  => 'trunc(sysdate + 1) + 12/24 + 30/(24*60)'
  9      );
 10    commit;
 11    dbms_output.put_line('job number = ' || l_job);
 12  end;
 13  /
job number = 151

PL/SQL procedure successfully completed.

SQL> select job, last_date, next_date from user_jobs;

       JOB LAST_DATE           NEXT_DATE
---------- ------------------- -------------------
       151                     08.09.2020 00:30:00

SQL>

---

Or, if you switch to DBMS_SCHEDULER:

SQL> begin
  2    sys.dbms_scheduler.create_job
  3      (job_name            => 'test',
  4       job_type            => 'plsql_block',
  5       job_action          => 'begin p_test; end;',
  6       start_date          =>  sysdate,
  7       repeat_interval     => 'freq=daily; byhour=0,12; byminute=30',
  8       enabled             => true
  9      );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select repeat_interval, next_run_date from user_scheduler_jobs;

REPEAT_INTERVAL                          NEXT_RUN_DATE
---------------------------------------- ----------------------------------------
freq=daily; byhour=0,12; byminute=30     08.09.20 00:30:05,000000 +02:00

SQL>