0
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'delete_partition',
   job_type           =>  'STORED_PROCEDURE',
   start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney',
   repeat_interval    =>  'FREQ=MONTHLY;INTERVAL=1', /* every month */
   end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney',
   comments           =>  'Running on the 17th of every month');
END;
/

Idea is to create a scheduled job which will run a package that drops partitions that are older than 6 months.

Question is how can I create another package in PL/SQL Developer which will include the dbms_scheduler job?

Any help is highly appreciated.

p_eazy
  • 27
  • 1
  • 6
  • 2
    Why would you do that? You'd have a scheduled job that calls a procedure that creates a job that calls a procedure that ... etc. Here's the job, procedure is scheduled, it'll run every month. That's all, isn't it? – Littlefoot Dec 19 '18 at 11:56
  • `'28-APR-08 07.00.00 PM Australia/Sydney'` is a string, not a `DATE` or `TIMESTAMP`. Use `TIMESTAMP '2018-04-28 07:00:00 Australia/Sydney'` or `TO_TIMESTAMP_TZ` or simply `SYSTIMESTAMP`. And `end_date` should be `NULL` I assume as you don't what the jobs to stop at certain time. – Wernfried Domscheit Dec 19 '18 at 13:28
  • Ok so let me just give you bit more context around what im trying to do here. So objective is to purge log data older than 6 months of 20 partitioned tables. I'm starting this work by tackling one table for now so I've created a PurgePackage that includes a Procedure which handles a for loop that delete 6 months old partitions. Therefore, id now like to create a scheduled job which will run PurgePackage daily. This is why I said I want this shceduler to be kept seperately rather than adding it in the PurgePackage itself. Hope this helps you understand a little bit more now. – p_eazy Dec 19 '18 at 13:45

1 Answers1

1

Why you want to add a job in the package ? anyway you can add this in your package

 -- Run a job immediately. If use_current_session is TRUE the job is run in the
-- user's current session. If use_current_session is FALSE the job is run in the
-- background by a dedicated job slave.
BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'delete_partition',
    USE_CURRENT_SESSION => FALSE);
END;
/
Moudiz
  • 7,211
  • 22
  • 78
  • 156