?
Solved

How can I schedule a Oracle Procedure in Unix

Posted on 2002-06-18
6
Medium Priority
?
312 Views
Last Modified: 2010-08-05
I would like to run a procedure in Oracle once in a month.
How can I do this
0
Comment
Question by:syathm
  • 4
6 Comments
 
LVL 21

Expert Comment

by:tfewster
ID: 7089163
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
 
LVL 21

Expert Comment

by:tfewster
ID: 7089240
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
 
LVL 21

Expert Comment

by:tfewster
ID: 7103627
syathm, did that answer your question? If you need more explanation of cron, please let me know.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Accepted Solution

by:
JYoungman earned 200 total points
ID: 7116311
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
 
LVL 21

Expert Comment

by:tfewster
ID: 7832966
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
 

Expert Comment

by:modulo
ID: 7908941
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In tuning file systems on the Solaris Operating System, changing some parameters of a file system usually destroys the data on it. For instance, changing the cache segment block size in the volume of a T3 requires that you delete the existing volu…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question