CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Rifqi Fitriady
Rifqi Fitriady

Reputation: 93

running stored procedures automatic in every month

hi i've stored procedure with will try to delete data from specified date (sysdate - 30) and i wanna trying to run Oracle Stored Procedure in every beginning of month..

how can i do that ?

thanks in advance, sorry for my english

Upvotes: 0

Views: 3712

Answers (3)

Dhanya Raj
Dhanya Raj

Reputation: 107

you can schedule a job for it,..you have to call the stored procedure in the job.So that you can run the stored procedure in the beginning of every month..

Upvotes: 0

Art
Art

Reputation: 5792

Minor note:

Oracle recommends that you switch from DBMS_JOB to Oracle Scheduler.

BEGIN                      
  DBMS_SCHEDULER.CREATE_JOB ( 
   job_name        => 'your_job_name',
   job_type        => 'STORED_PROCEDURE',       -- see oracle documentation on types --
   job_action      => 'name of the procedure',
   start_date      => trunc(sysdate)+2/24,      -- every night @ 2 a.m.
   repeat_interval => 'FREQ=DAILY;BYHOUR=2',    -- daily @ 2 a.m.
   end_date        => NULL,
   enabled         => TRUE,
   comments        => 'your general comment');
END;
/

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231851

The simplest thing that could possibly work would be to use the DBMS_JOB package. This, for example, will run your_procedure at midnight on the fist of every month starting at midnight on the first of next month and will pass in a parameter of sysdate - 30

DECLARE 
  l_jobno PLS_INTEGER;
BEGIN
  dbms_job.submit( l_jobno,
                   'BEGIN your_procedure( sysdate - 30 ); END;',
                   add_months( trunc(sysdate,'MM'), 1 ),
                   q'{add_months( trunc(sysdate, 'MM'), 1 )}' );
  commit;
END;

You can also use the DBMS_SCHEDULER package which is a newer and more sophisticated scheduler assuming you are using a more recent version of Oracle. That can get much more sophisticated and much more involved than DBMS_JOB-- if your requirements are really simply, though, the lighter weight DBMS_JOB may be easier to learn and understand.

Upvotes: 1

Related Questions