CODEWITHSUNDEEP
oracle-databaseplsqldbms-job
akaya
akaya

Reputation: 110

Is there a built-in function to parse oracle dbms_job interval string and get next_date?

I am trying to implement the same interval string format oracle uses for dbms_job for some custom processing logic. I wrote a function using execute immediate, I wonder if there is a built-in function that I could call directly.

function get_next_date(p_interval in varchar2) return date is
  v_date      date;
  v_query_str varchar2(256);
begin
  v_query_str := 'select ' || p_interval || ' from dual';
  
  execute immediate v_query_str
    into v_date;
  
  return v_date;
exception
  when others then
    return null;
end;

Usage:

begin
  dbms_output.put_line(get_next_date('sysdate+1/1440'));
end;

Upvotes: 0

Views: 142

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

DBMS_JOBS is legacy. For DBMS_SCHEDULER you can use DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING as mentioned in the comment.

For others you can use function like this:

function get_next_date(p_interval in varchar2) return date is
  v_date      date;
begin
  EXECUTE IMMEDIATE 'BEGIN :ret := '||p_interval||'; END;' USING OUT v_date;
  return v_date;
exception
  when others then
    return null;
end;

Note, next time at DBMS_JOBS is evaluated when the current job has been finished, whereas DBMS_SCHEDULER evaluates the next time when current job starts. For long running jobs or short intervals, the result can be different.

Upvotes: 1

Related Questions