Does anyone know if there is a way to schedule a database task using PL/SQL or Package method calls?
I found the information regarding the scheduler syntax but our dates I would like to use are not set in stone and are changed yearly based on accounting periods...
Best answer by pwlm
View original
+5
If you using already declared package method, you can basically schedule a task from below window.
You can choose the package and method according to the module wise. After adding the package and method you can schedule the task in same window by clicking on create new schedule.
Hope this serves your requirement..
Cheers!
Or if you need to do this only from plsql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'job_name',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN your_method_name; END;',
start_date => systimestamp, -- or any start time
repeat_interval => 'FREQ=SECONDLY; INTERVAL=300', -- or any frequency
end_date => NULL, -- or any end date
enabled => TRUE,
comments => 'meaningful description');
END;
To add to Ruchira’s comment [hi Ruchira :P], you can use SQL expressions for start_date and repeat_interval - making this path very effective for picking up dynamic schedules. For instance, if the next execution date falls on a public holiday, you can use an expression to ensure the system schedules the next execution to the next working day.
Thank you for the responses! I guess my question was worded poorly. I know about the ways of scheduling the task, I would like the frequency to be based on a dynamic date in the database. Essentially I need this task to run at the end of every accounting period quarter, however, these periods are not static dates and change year to year.
+11
Thank you for the responses! I guess my question was worded poorly. I know about the ways of scheduling the task, I would like the frequency to be based on a dynamic date in the database. Essentially I need this task to run at the end of every accounting period quarter, however, these periods are not static dates and change year to year.
Hi
To do this you could create a Function in the Database (within a custom Package) that returns the next date you want the Scheduled Task to execute, then when scheduling the task enter the Function into the ‘Custom’ option shown below:
Cheers,
Pete
+9
Hi,
If you want to create the scheduled job only from PLSQL you may find the below Oracle documentation useful,
https://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12384
The number of parameters you need/want to specify is indicated when creating a job/schedule (DBMS_SCHEDULER.CREATE_JOB).
Regards,
Nadeesh
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
