How to schedule function in Oracle?

Question

I want to schedule function in Oracle.

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'aud_clear_fun',
                            start_date      => sysdate,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            end_date        => null,
                            enabled         => true,
                            comments        => 'Created By: MK; Truncates aud$ table');
end;

I have tried to indicate several other values for job_action, for example:

declare
my_var number;
begin
select aud_clear_fun into my_var from dual;
end;

But doesn't work. Can you provide me with the right syntax of scheduling function?

Thank you.

--aud_clear_fun

create or replace function aud_clear_fun
return number is
begin
   delete from a;
   return 0;
end;

--Job

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'begin
                                                 mari_dba.aud_clear_fun();
                                                end;',
                            start_date      => sysdate,
                            end_date        => NULL,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            enabled         => true);
end;

--Execution

begin
  dbms_scheduler.run_job('aud_clear');
end;

--Error

ORA-06550: line 2, column 50:
PLS-00221: 'AUD_CLEAR_FUN' is not a procedure or is undefined
ORA-06550: line 2, column 50:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

View program sources of error stack?

Answer 1

The error message is very clear, you need to specify a procedure or a valid pl/sql block. If you really want to truncate the aud$ table from a piece of pl/sql, you will need to use dynamic SQL.

begin
execute immediate 'truncate table sys.aud$';
end;
/

put the code in the action or in the procedure definition of aud_clear_fun, that really needs to be a procedure to work with Oracle Scheduler. The Scheduler is not going to read the returned value ...

Answer 2

Put the job_action inline, as follows:

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'begin
                                                truncate table aud$;
                                                end;',
                            start_date      => sysdate,
                            end_date        => NULL,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            enabled         => true,
                            comments        => 'Created By: MK; Truncates aud$ table');
end;