Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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 ...

share|improve this answer
    
that was what I wanted to read thank you... –  kupa May 7 '12 at 15:28
add comment

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;
share|improve this answer
    
it means that I cannot schedule function, right? I have opportunity to call procedure but not function? –  kupa May 7 '12 at 14:06
    
job_action=>'begin myfunction(); end;' (On multiple lines) –  Phil May 7 '12 at 14:08
    
It says: ORA-06550: line 1, column 729: PLS-00221: 'AUD_CLEAR_FUN' is not a procedure or is undefined ORA-06550: line 1, column 729: 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? –  kupa May 7 '12 at 14:11
1  
Which user owns the function? –  Phil May 7 '12 at 14:21
    
Current user........ –  kupa May 7 '12 at 14:30
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.