[SOLVED] DBMS Job with function

DBMS Job with function

I have this DBMS Job that run at the end of day to clean the data and I have this function available CLEAN_SNAPSHOT_DATA_F(7).

I try running this script below.

begin
  sys.dbms_job.submit(job => :job,
                      what => 'select OPTIEXEC_ADMIN.CLEAN_SNAPSHOT_DATA_F(7) from dual',
                      next_date => to_date('11-06-2016', 'dd-mm-yyyy'),
                      interval => 'SYSDATE + 24/24');
  commit;
end;

But the error return ORA-01008: not all variables bound

Can you advice what the issue about the script?

Solution

Try this . Hope it helps.

DECLARE
jb_av NUMBER;
BEGIN
  sys.dbms_job.submit(job => jb_av, 
                      what => 'DECLARE lv_var VARCHAR2(32676); BEGIN select OPTIEXEC_ADMIN.CLEAN_SNAPSHOT_DATA_F(7) INTO lv_var from dual;END;', 
                      next_date => to_date('11-06-2016', 'dd-mm-yyyy'), 
                      interval => 'SYSDATE + 24/24');
  COMMIT;
END;