How can I schedule a Oracle Procedure in Unix

I would like to run a procedure in Oracle once in a month.
How can I do this
syathmAsked:
Who is Participating?
 
JYoungmanConnect With a Mentor Commented:
A more effective way of doing this (in Oracle 8 and above) is to use the built-in DBMS_JOB feature.  This is a package which manages the periodic calling of a PL/SQl procedure.   you might use it like this :-

create procedure my_monthly_job as
BEGIN
   name varchar2(100);
   -- ... lots of good stuff here ...
END
/



-- Now set the job up (you only need to do this once)
DECLARE
  jobno NUMBER;
BEGIN
  dbms_job.submit(jobno, 'my_monthly_job',
      SYSDATE, ADD_MONTHS(SYSDATE, 1) );
END;
/

This mechanism is better because there is no need to set up ORACLE_HOME, TWO_TASK, and so on, and you don't have to worry about putting passwords into files, setting up pexternally-dentified accounts, and so forth.  The DBMS_JOB package also provides management features.

For more information about DBMS_JOB, please see

Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
A76936-01

0
 
tfewsterCommented:
Use cron:

As Oracle, do `crontab -e`
Insert a line:
minute hour monthday * * /path/to/script

Remember that cron doesn't use your .profile etc, so ensure the script explicitly sets all paths & variables that it will need.

(man cron & man crontab for the full info :)

0
 
tfewsterCommented:
If the oracle user doesn't have permission to use cron, you can add them to cron.allow OR have root's crontab kick off the command with
minute hour monthday * * su - oracle -c /path/to/script

If your script doesn't send stdout & stderr to a log file itself, you can redirect the output within cron; Otherwise, any output will be sent to the owner of the cron job as a mail message. (I hate it when that happens, as noone ever reads their unix mail file, and they just grow forever ;-)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tfewsterCommented:
syathm, did that answer your question? If you need more explanation of cron, please let me know.
0
 
tfewsterCommented:
No comment has been added lately, so it's time to clean up this Topic Area.
I will leave a recommendation for this question in the Cleanup topic area as follows:

- Answered by JYoungman

Please leave any comments here within the next 7 days

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

tfewster (I don't work here, I'm just an Expert :-)
0
 
moduloCommented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.