Solved

How can I schedule a Oracle Procedure in Unix

Posted on 2002-06-18
6
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
JYoungman earned 50 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virtualizing TAPE on dual VIOS 3 122
aix tls version 6 630
Skill Development 15 201
aix unix tar error 3 87
Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
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…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

738 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