rem Name:    submit.sql
rem Purpose: Schedule a job to be executed
rem Usage:   @submit <interval> "<job>"
rem Subject: job
rem Attrib:  pls ddl
rem Descr:   <interval> format: [!][[+][dd<-|,>]hh[:mm]]
rem      ! means execute immediately
rem          + specifies a relative time (from current time)
rem          dd, day of month or # days if relative (monthly)
rem          dd- day of week or # days if relative (weekly)
rem          hh  hours (daily)
rem          :mm minutes
rem Notes:   Use TWO single quotes around string parameters
rem SeeAlso: @daily @jc @jobs

@setup
set serveroutput on

define arg="'&&1'"
define job="'&&2'"

declare

  i binary_integer;
  s varchar2(256);

  imm boolean := false;
  anchor binary_integer := 1;
  -- 0=relative, 1=daily, 2=weekly, 3=monthly

  days number := 0;
  hrs number := 0;
  mins number := 0;

  nextd date;
  intv varchar2(80);
  jobnum binary_integer;
 

function eval(expr varchar2)
return date is
  stmt varchar2(2000);
  c number;
  retval number;
  result date;
begin
  stmt := 'select '||expr||' from sys.dual';
  c := sys.dbms_sql.open_cursor;
  sys.dbms_sql.parse(c, stmt, sys.dbms_sql.native);
  sys.dbms_sql.define_column(c, 1, result);
  retval := sys.dbms_sql.execute_and_fetch(c);
  if retval > 0 then
    sys.dbms_sql.column_value(c, 1, result);
  else
    result := null;
  end if;
  sys.dbms_sql.close_cursor(c);
  return result;
exception
  when others then
  if sys.dbms_sql.is_open(i) then
    sys.dbms_sql.close_cursor(c);
  end if;
  raise;
end eval;
 

procedure concat_if(s1 in out varchar2, s2 varchar2,
  ok boolean default true, sep varchar2 default null)
is
begin
  if ok then
    if s1 is null then
      s1 := s2;
    else
      s1 := s1||sep||s2;
    end if;
  end if;
end;
 

begin
  -- PARSE TIME STRING
  s := &&arg;

  -- GET IMMIEDIATE FLAG
  if substr(s, 1, 1) = '!' then
    imm := true;
    s := substr(s, 2);
  end if;

  -- GET RELATIVE FLAG
  if substr(s, 1, 1) = '+' then
    anchor := 0;
    s := substr(s, 2);
  end if;

  -- GET DAYS
  i := instr(s, '-');
  if i > 0 then
    days := to_number(substr(s, 1, i - 1));
    if anchor > 0 then
      anchor := 2;
    end if;
    s := substr(s, i + 1);
  else
    i := instr(s, ',');
    if i > 0 then
      days := to_number(substr(s, 1, i - 1));
      if anchor > 0 then
        anchor := 3;
      end if;
      s := substr(s, i + 1);
    end if;
  end if;

  -- GET HOURS + MINUTES
  i := instr(s, ':');
  if i > 0 then
    hrs := nvl(to_number(substr(s, 1, i - 1)), 0);
    mins := substr(s, i + 1);
    s := substr(s, i + 1);
  else
    hrs := nvl(to_number(s), 0);
  end if;

  -- GET INTERVAL
  if anchor = 0 then
    intv := 'sysdate';
  elsif anchor = 1 then -- DAILY
    intv := 'trunc(sysdate)';
  elsif anchor = 2 then -- WEEKLY
    intv := 'trunc(sysdate,''DY'')';
  elsif anchor = 3 then -- MONTHLY
    intv := 'trunc(sysdate,''MM'')';
  else
    raise_application_error(-20000,
      'Invalid anchor (interal @submit)');
  end if;

  concat_if(intv, to_number(days), days > 0, '+');
  concat_if(intv, to_number(hrs)||'/24', hrs > 0, '+');
  concat_if(intv, to_number(mins)||'/1440', mins > 0, '+');

  nextd := eval(intv);
  if nextd < sysdate-0.1/1440 then
    if anchor = 1 then
      intv := replace(intv, 'sysdate', 'sysdate+1');
    elsif anchor = 2 then
      intv := replace(intv, 'sysdate', 'sysdate+7');
    elsif anchor = 3 then
      intv := replace(intv, 'sysdate', 'add_months(sysdate, 1)');
    else
      intv := replace(intv, 'sysdate', 'sysdate+1/1440');
    end if;
    nextd := eval(intv);
  end if;

  if imm then
    nextd := sysdate + 0.1/1440;
  end if;

  --&&putl('today='||to_char(sysdate, 'Dy Mon DD HH24:MI:SS'));
  --&&putl('start='||to_char(nextd, 'Dy Mon DD HH24:MI:SS'));
  --&&putl('intvl='||intv);

  begin
    dbms_job.submit(job=>jobnum, what=>&&job
    , next_date=>nextd, interval=>intv, no_parse=>false);
  exception
    when others then
    &&putl('Job failed with errors:');
    &&putl(sqlerrm);
    return;
  end;

  commit;
  &&putl('Commit complete.');

  select
    'Job '||ltrim(to_char(jobnum))||' submitted for '||
    to_char(next_date,'Dy Mon DD HH24:MI:SS')||'.'
  into s
  from &&ora._jobs where job = jobnum;

  &&putl(s);
end;
/

undef arg job

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips