How to create and new job and how to sumit

[raghud]

This is the first time I am trying to use the dbms_job package .. Need your help ( syntax) to create a job and submit a job . I do have procedure delete_on _friday it has to be automatically executed on every FRIDAY evening at 6:00 PM

[kevwidd]

If your running Unix you could setup a job in the cron which will kick off a script.

[raghud]

Thanks .. My manager wants this functionality insite the database . This org won't give any access on OS to the dba's . It is very difficult to manage the jobs outside of database

[kevwidd]

Hi,

To run DBMS_JOB's you need to have SNP processes running in the background. You do this by adding the following lines in the init.ora:

JOB_QUEUE_PROCESSES (a number between 0 and 10 depending how many processes you need to be running)
JOB_QUEUE_INTERVAL (Interval between wake-ups, default 60)

Running a job:

PROCEDURE SUBMIT(job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT NULL,
no_parse IN BOOLEAN DEFAULT FALSE);

job: job Number
what: PL/SQL code to run
next_date: When job will run next
Interval: Time which it should run

* Your stored procedure that will be run is called kev for e.g.

eg of job:

SQL> Variable v_jobnum number
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:v_jobnum, 'kev;', sysdate, 3 sysdate + 1);
4 END;

Cheers

[raghud]

Thank you