0

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;
akaya
  • 110
  • 4
  • 9
  • 2
    Use [`DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING`](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-73BF0E7F-2626-414B-8094-A8FE8A2C4396) – astentx May 12 '23 at 08:06
  • This could be a good alternative. Problems: 1) This does not support simple string formats like 'sysdate+1', ORA-27415 exception is thrown 2) This is way more permissive than dbms_job interval, which may not be a good thing. 3) This uses timestamp with timezone, I would prefer a simple date. – akaya May 12 '23 at 08:43
  • 1
    Instead of "permissive" I would rather say "flexible" or "powerful" – Wernfried Domscheit May 12 '23 at 09:00
  • There's no magic behind `interval` parameter of (deprecated 13 years ago) `DBMS_JOB` package: as of [documentation](https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_job.htm#i1000807), it's just *Date function that calculates the next time to run the job*. That effectively turns into: "How to evaluate dynamic expression" with data type enforcement. – astentx May 12 '23 at 09:18
  • Don't use DBMS_JOB. Use scheduler (DBMS_SCHEDULER) instead. That will greatly simplify your life. – Paul W May 12 '23 at 11:41

1 Answers1

1

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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Please check my reply to the comment of the question. What is the advantage of your function compared to the function I provided in the question? – akaya May 12 '23 at 08:47
  • Apart from [SQL PL/SQL Context Switching](https://forums.oracle.com/ords/apexds/post/context-switching-performance-issue-0323) it is more a matter of taste. I don't like the `select ... from dual` so much. – Wernfried Domscheit May 12 '23 at 08:57
  • In first look it seems context switch would make it slower. However, I profiled both functions, my function performs faster. Don't know why. – akaya May 12 '23 at 09:33
  • 1
    Unless you have to run it thousands of time in a loop there should be no noticeable difference in performance. 20 years ago, this was different, you had significant less performance when you did may context switches. – Wernfried Domscheit May 12 '23 at 09:37